My company working days is from Monday to Friday. 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?
On Jun 19, 8:22 am, "pedestrian via SQLMonster.com" <u16758@uwe> wrote:
> My company working days is from Monday to Friday. > 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?
> My company working days is from Monday to Friday. > 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?
On 19 Jun, 14:22, "pedestrian via SQLMonster.com" <u16758@uwe> wrote:
> 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.
Ya.... I have to take care the holiday as well (which is stored in a table) FYI, I have decided to create an Windows app that will be scheduled daily...
David Portas wrote: >> 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.
Build a calendar table with one column for the calendar data and other 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:
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:
> My company working days is from Monday to Friday. > 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?
>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: >> My company working days is from Monday to Friday. >> I would like to generate a result set which show all >[quoted text clipped - 7 lines] >> -- >> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007...
|
