Working days calendar in T-SQL?
pedestrian via SQLMonster.com
I would like to generate a result set which show all
consecutive working days (Monday to Friday excluding the
weekends) that is, for June 2007, it show me 1st, 4th, 5th,
6th, 8th, 11th etc... until 29th (last working day of June). Is
it possible to do this with T-SQL?
Thank you very much.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200706/1
Alex Kuznetsov
wrote:
Yes, google up "calendar table".
Uri Dimant
http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html
"pedestrian via SQLMonster.com" <u16758@uwe> wrote in message
news:73eef10337f0a@uwe...
David Portas
> My company working days is from Monday to Friday.
Is that always the case though? What about public holidays?
> I would like to generate a result set which show all
> consecutive working days (Monday to Friday excluding the
> weekends) that is, for June 2007, it show me 1st, 4th, 5th,
> 6th, 8th, 11th etc... until 29th (last working day of June). Is
> it possible to do this with T-SQL?
Use a calendar table:
SELECT dt
FROM Calendar
WHERE dt > '20070601'
AND dt < '20070701'
AND WorkingDay = 'Y';
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
ML
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
ML
pedestrian via SQLMonster.com
Uri Dimant wrote:
>Hi
>http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html
>
>> My company working days is from Monday to Friday.
>> I would like to generate a result set which show all
>[quoted text clipped - 4 lines]
>>
>> Thank you very much.
--
Regards,
Pedestrian, Penang.
Message posted via http://www.sqlmonster.com
pedestrian via SQLMonster.com
Uri Dimant wrote:
>Hi
>http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html
>
>> My company working days is from Monday to Friday.
>> I would like to generate a result set which show all
>[quoted text clipped - 4 lines]
>>
>> Thank you very much.
--
Regards,
Pedestrian, Penang.
pedestrian via SQLMonster.com
Thank you Alex.
--
Regards,
Pedestrian, Penang.
Message posted via http://www.sqlmonster.com
pedestrian via SQLMonster.com
FYI, I have decided to create an Windows app that will be scheduled daily...
--
Regards,
Pedestrian, Penang.
--CELKO--
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
week_in_year INTEGER NOT NULL, -- SQL server is not ISO standard
holiday_type INTEGER NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year INTEGER NOT NULL,
julian_business_day INTEGER NOT NULL,
...);
The Julian business day is a good trick. Number the days from
whenever your calendar starts and repeat a number for a weekend or
company holiday.
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day weekends:
http://www.smart.net/~mmontes/ushols.html
Time zones with fractional hour displacements
http://www.timeanddate.com/worldclock/city.html?n=246
http://www.timeanddate.com/worldclock/city.html?n=176
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=54
But the STRANGE ones are:
http://www.timeanddate.com/worldclock/city.html?n=63
http://www.timeanddate.com/worldclock/city.html?n=5
raibeart
CREATE TABLE [dbo].[TIME_DIMENSION](
[DATE_ID] [datetime] NOT NULL,
[DAY_OF_WEEK] [varchar](50) NULL,
[DAY_MONTH_NO] [int] NULL,
[DAY_MONTH_TXT] [varchar](12) NULL,
[DAY_YEAR_NO] [smallint] NULL,
[DAY_OVERALL_NO] [int] NULL,
[WEEK_YEAR_NO] [int] NULL,
[WEEK_OVERALL_NO] [int] NULL,
[MONTH_NO] [int] NULL,
[MONTH_TXT] [varchar](15) NULL,
[MONTH_OVERALL_NO] [int] NULL,
[QUARTER_NO] [int] NULL,
[FISCAL_PERIOD] [varchar](50) NULL,
[HOLIDAY_FLG] [varchar](50) NULL,
[WEEKDAY_FLG] [varchar](50) NULL,
[LAST_DAY_IN_MONTH_FLG] [varchar](50) NULL,
[YEAR_NO] [smallint] NULL,
[CREATION_DATE] [datetime] NULL,
[APP_LOGIN_CREATION] [varchar](50) NULL,
[MODIFICATION_DATE] [datetime] NULL,
[APP_LOGIN_MODIFICATION] [varchar](50) NULL,
CONSTRAINT [TIME_DIMENSION_PK] PRIMARY KEY CLUSTERED
(
[DATE_ID] ASC
)ON [PRIMARY]
) ON [PRIMARY]
Using this, there is essentially no math that needs to be done on
dates. You
simply join this tablewith any date column and you get all the
information about it.
I use an Access program to load the table initially and usually carry
100 to
150 years of data in it.
Robert
On Jun 19, 8:22 am, "pedestrian via SQLMonster.com" <u16758@uwe>
wrote:
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007...
pedestrian via SQLMonster.com
Thank you guy for the suggested solution...
>[quoted text clipped - 7 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007...
--
Regards,
Pedestrian, Penang.