February 11, 2010 at 4:49 am
I have an existing calendar table (see below) and am trying to update a new column with the text 'YES' where Monday is the first Monday following the First Saturday of the month. Is it possible to use this existing table to express that logic?
TheDate,IsWeekday,IsHoliday,theYear,FinancialYear,TheQuarter,monthNumber,DayNumber,DayOfWeek,MothNumber,DayName,WeekNumber,DataSource,WorkingDayOfMonth,EventOwner,EventDesc,HolidayDescription,CAL_YEAR,CAL_MONTH
01/07/2009 00:00,1,0,2009,2009,2,7,1,4,July,Wednesday,26,NULL,1,NULL,NULL,NULL,2009,7
02/07/2009 00:00,1,0,2009,2009,2,7,2,5,July,Thursday,26,NULL,2,NULL,NULL,NULL,2009,7
03/07/2009 00:00,1,0,2009,2009,2,7,3,6,July,Friday,26,NULL,3,NULL,NULL,NULL,2009,7
04/07/2009 00:00,0,0,2009,2009,2,7,4,7,July,Saturday,26,NULL,NULL,NULL,NULL,NULL,2009,7
05/07/2009 00:00,0,0,2009,2009,2,7,5,1,July,Sunday,27,NULL,NULL,NULL,NULL,NULL,2009,7
06/07/2009 00:00,1,0,2009,2009,2,7,6,2,July,Monday,27,NULL,4,NULL,NULL,NULL,2009,7
07/07/2009 00:00,1,0,2009,2009,2,7,7,3,July,Tuesday,27,NULL,5,NULL,NULL,NULL,2009,7
08/07/2009 00:00,1,0,2009,2009,2,7,8,4,July,Wednesday,27,NULL,6,NULL,NULL,NULL,2009,7
09/07/2009 00:00,1,0,2009,2009,2,7,9,5,July,Thursday,27,NULL,7,NULL,NULL,NULL,2009,7
10/07/2009 00:00,1,0,2009,2009,2,7,10,6,July,Friday,27,NULL,8,NULL,NULL,NULL,2009,7
11/07/2009 00:00,0,0,2009,2009,2,7,11,7,July,Saturday,27,NULL,NULL,NULL,NULL,NULL,2009,7
12/07/2009 00:00,0,0,2009,2009,2,7,12,1,July,Sunday,28,NULL,NULL,NULL,NULL,NULL,2009,7
13/07/2009 00:00,1,0,2009,2009,2,7,13,2,July,Monday,28,NULL,9,NULL,NULL,NULL,2009,7
14/07/2009 00:00,1,0,2009,2009,2,7,14,3,July,Tuesday,28,NULL,10,NULL,NULL,NULL,2009,7
15/07/2009 00:00,1,0,2009,2009,2,7,15,4,July,Wednesday,28,NULL,11,NULL,NULL,NULL,2009,7
16/07/2009 00:00,1,0,2009,2009,2,7,16,5,July,Thursday,28,NULL,12,NULL,NULL,NULL,2009,7
17/07/2009 00:00,1,0,2009,2009,2,7,17,6,July,Friday,28,NULL,13,NULL,NULL,NULL,2009,7
18/07/2009 00:00,0,0,2009,2009,2,7,18,7,July,Saturday,28,NULL,NULL,NULL,NULL,NULL,2009,7
19/07/2009 00:00,0,0,2009,2009,2,7,19,1,July,Sunday,29,NULL,NULL,NULL,NULL,NULL,2009,7
20/07/2009 00:00,1,0,2009,2009,2,7,20,2,July,Monday,29,NULL,14,NULL,NULL,NULL,2009,7
21/07/2009 00:00,1,0,2009,2009,2,7,21,3,July,Tuesday,29,NULL,15,NULL,NULL,NULL,2009,7
22/07/2009 00:00,1,0,2009,2009,2,7,22,4,July,Wednesday,29,NULL,16,NULL,NULL,NULL,2009,7
23/07/2009 00:00,1,0,2009,2009,2,7,23,5,July,Thursday,29,NULL,17,NULL,NULL,NULL,2009,7
24/07/2009 00:00,1,0,2009,2009,2,7,24,6,July,Friday,29,NULL,18,NULL,NULL,NULL,2009,7
25/07/2009 00:00,0,0,2009,2009,2,7,25,7,July,Saturday,29,NULL,NULL,NULL,NULL,NULL,2009,7
26/07/2009 00:00,0,0,2009,2009,2,7,26,1,July,Sunday,30,NULL,NULL,NULL,NULL,NULL,2009,7
27/07/2009 00:00,1,0,2009,2009,2,7,27,2,July,Monday,30,NULL,19,NULL,NULL,NULL,2009,7
28/07/2009 00:00,1,0,2009,2009,2,7,28,3,July,Tuesday,30,NULL,20,NULL,NULL,NULL,2009,7
29/07/2009 00:00,1,0,2009,2009,2,7,29,4,July,Wednesday,30,NULL,21,NULL,NULL,NULL,2009,7
30/07/2009 00:00,1,0,2009,2009,2,7,30,5,July,Thursday,30,NULL,22,NULL,NULL,NULL,2009,7
31/07/2009 00:00,1,0,2009,2009,2,7,31,6,July,Friday,30,NULL,23,NULL,NULL,NULL,2009,7
01/08/2009 00:00,0,0,2009,2009,2,8,1,7,August,Saturday,30,NULL,NULL,NULL,NULL,NULL,2009,8
02/08/2009 00:00,0,0,2009,2009,2,8,2,1,August,Sunday,31,NULL,NULL,NULL,NULL,NULL,2009,8
03/08/2009 00:00,1,0,2009,2009,2,8,3,2,August,Monday,31,NULL,1,NULL,NULL,NULL,2009,8
04/08/2009 00:00,1,0,2009,2009,2,8,4,3,August,Tuesday,31,NULL,2,NULL,NULL,NULL,2009,8
05/08/2009 00:00,1,0,2009,2009,2,8,5,4,August,Wednesday,31,NULL,3,NULL,NULL,NULL,2009,8
06/08/2009 00:00,1,0,2009,2009,2,8,6,5,August,Thursday,31,NULL,4,NULL,NULL,NULL,2009,8
07/08/2009 00:00,1,0,2009,2009,2,8,7,6,August,Friday,31,NULL,5,NULL,NULL,NULL,2009,8
08/08/2009 00:00,0,0,2009,2009,2,8,8,7,August,Saturday,31,NULL,NULL,NULL,NULL,NULL,2009,8
09/08/2009 00:00,0,0,2009,2009,2,8,9,1,August,Sunday,32,NULL,NULL,NULL,NULL,NULL,2009,8
10/08/2009 00:00,1,0,2009,2009,2,8,10,2,August,Monday,32,NULL,6,NULL,NULL,NULL,2009,8
11/08/2009 00:00,1,0,2009,2009,2,8,11,3,August,Tuesday,32,NULL,7,NULL,NULL,NULL,2009,8
12/08/2009 00:00,1,0,2009,2009,2,8,12,4,August,Wednesday,32,NULL,8,NULL,NULL,NULL,2009,8
13/08/2009 00:00,1,0,2009,2009,2,8,13,5,August,Thursday,32,NULL,9,NULL,NULL,NULL,2009,8
14/08/2009 00:00,1,0,2009,2009,2,8,14,6,August,Friday,32,NULL,10,NULL,NULL,NULL,2009,8
15/08/2009 00:00,0,0,2009,2009,2,8,15,7,August,Saturday,32,NULL,NULL,NULL,NULL,NULL,2009,8
16/08/2009 00:00,0,0,2009,2009,2,8,16,1,August,Sunday,33,NULL,NULL,NULL,NULL,NULL,2009,8
17/08/2009 00:00,1,0,2009,2009,2,8,17,2,August,Monday,33,NULL,11,NULL,NULL,NULL,2009,8
18/08/2009 00:00,1,0,2009,2009,2,8,18,3,August,Tuesday,33,NULL,12,NULL,NULL,NULL,2009,8
19/08/2009 00:00,1,0,2009,2009,2,8,19,4,August,Wednesday,33,NULL,13,NULL,NULL,NULL,2009,8
20/08/2009 00:00,1,0,2009,2009,2,8,20,5,August,Thursday,33,NULL,14,NULL,NULL,NULL,2009,8
February 11, 2010 at 6:36 am
I believe this might help you
declare @date datetime
set @date = '01/08/2010'
SELECT DATEPART(day,@date),DATEPART(weekday,@date)
SELECT CASE
WHEN DATEPART(weekday,@date) = 2
and DATEPART(day,@date) between 2 and 8
THEN 'YES'
ELSE 'No'
END
Jim
February 11, 2010 at 7:10 am
Cheers Jim. That's done the trick.
Andy
February 11, 2010 at 8:04 am
whitlander
It appears that you will be handling a great deal of date data.
If you have not already found Lynn's posting may I suggest reading this post by Lynn Pettis - and add the routines he
describes to your "bag of tricks", I have found them to be very handy.
https://qa.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
February 12, 2010 at 5:19 am
Thanks for link. What skt5000 was fine. I popualted the calendar with his logic up until 01-01-2020, which is fine for my purposes.
February 12, 2010 at 5:55 am
skt5000 (2/11/2010)
I believe this might help youdeclare @date datetime
set @date = '01/08/2010'
SELECT DATEPART(day,@date),DATEPART(weekday,@date)
SELECT CASE
WHEN DATEPART(weekday,@date) = 2
and DATEPART(day,@date) between 2 and 8
THEN 'YES'
ELSE 'No'
END
Jim
I believe it should be between 3 and 9 instead of between 2 and 8.
Test case is August 2010. The first Monday is Aug, 2 but since it is not following the first Saturday of the month it should return NO. The Monday that should return YES is Aug, 9.
Beware that the above query will only work for @@DATEFIRST = 7. To make it more universal, you could do
WHEN (DATEPART(weekday,@date) + @@DATEFIRST) %7 = 2.
February 12, 2010 at 7:04 am
ahh yes, you're right. very much appreciated.
thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply