| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2005-09-22 : 08:37:21
|
| Guys, I have unique scenario here where I have to populate a column with first businees day of the year from years 1800 to 2005, I have been trying to use date function with getdate() but it doesnt seem to help.My query returns only the first Monday of each year but as you first mon might be Jan 1st in which case it cannot be a business day. Any suggestions/input would helpThanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-22 : 08:55:13
|
| Business days in 1800??? "Another day of burning witches, another .12 cents>" Well I guess burning witches was some time before 1800, but seriously, do you need to take into account any day in that time frame where "businesses" were closed? ie: because of natural disasters, wars etc.? I guess Martin Luther King day wasn't too closely observed considering slavery was still very popular with the white crowd. So you would really need a table of holidays/days off that applies to the current year (and place of interest) then apply that to your function. Since there are only 205 rows you need, just use your function to populate a table and then update the exceptions.Be One with the OptimizerTG |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-22 : 09:38:22
|
Since I was wise-crackin' I thought I'd offer up a possible solution.If the links Madhivanan don't quite get you there...for just these 2 simple rules:1. Get the first Monday of the year.2. If first Monday is Jan 1 make Tuesday the first business day of that yearthen how about this?select fbd = --add a day for instances of Jan 1 (first business day) dateadd( day ,case when datepart(day, fm) = 1 then 1 else 0 end ,fm)from (--get first monday of each year select fm = dateadd(day ,case when datepart(weekday, fdy) = 1 then 1 when datepart(weekday, fdy) = 2 then 0 when datepart(weekday, fdy) = 3 then 6 when datepart(weekday, fdy) = 4 then 5 when datepart(weekday, fdy) = 5 then 4 when datepart(weekday, fdy) = 6 then 3 when datepart(weekday, fdy) = 7 then 2 end ,fdy) from ( --get first day of each year for 1800-2005 select dateadd(year, n, '1/1/1800') fdy from (--produce enough numbers for our problem --(you can also use an existing numbers table instead) select n1+n2+n3+n4+n5+n6+n7+n8 n from (select 0 n1 union select 1) n1 cross join (select 0 n2 union select 2) n2 cross join (select 0 n3 union select 4) n3 cross join (select 0 n4 union select 8) n4 cross join (select 0 n5 union select 16) n5 cross join (select 0 n6 union select 32) n6 cross join (select 0 n7 union select 64) n7 cross join (select 0 n8 union select 128) n8 ) numbers where n <= 205 ) a ) aorder by 1 Be One with the OptimizerTG |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-09-22 : 09:42:25
|
or this......ALTER FUNCTION dbo.NextBusMonday(@InpDate DATETIME) RETURNS DATETIMEASBEGIN DECLARE @Retval DATETIME SELECT @Retval = CASE WHEN DATEPART(dw, @InpDate) = 2 THEN @InpDate WHEN DATEPART(dw, @InpDate) < 2 THEN DATEADD(dd, 1, @InpDate) ELSE DATEADD(dd, 7 - DATEPART(dw, @InpDate) + 2, @InpDate) END RETURN @RetvalEND--*********************************************************************************************DECLARE @StartDate DATETIMESELECT @StartDate = '1800-01-02'WHILE @StartDate < GETDATE()BEGIN SELECT dbo.NextBusMonday(@StartDate) SET @StartDate = DATEADD(yy, 1, @StartDate)ENDDuane. |
 |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2005-09-22 : 11:08:45
|
| Guys, The above solutions indeed does work for First Mondays of the year, but my issue is finding first business day (i.e. which is not a weekend or 1st Jan). Because scenario involves populating the column for backfile data with first business day.Any suggestions/inputs would helpThanks |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-22 : 11:24:17
|
>>but my issue is finding first business day (i.e. which is not a weekend or 1st Jan).Isn't that what I provided?quote: 1. Get the first Monday of the year.2. If first Monday is Jan 1 make Tuesday the first business day of that year
Be One with the OptimizerTG |
 |
|
|
ronstone
Starting Member
32 Posts |
Posted - 2005-09-22 : 16:44:05
|
[quote]Originally posted by TG Business days in 1800??? "Another day of burning witches, another .12 cents>" This made my day! |
 |
|
|
|