Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do you create a calendar table like this?

Author  Topic 

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-21 : 14:44:06
execution check
8/13/2008 8/13/2008
8/13/2008 8/16/2008
8/13/2008 8/20/2008
8/13/2008 8/23/2008
8/13/2008 9/10/2008
8/13/2008 9/13/2008
8/20/2008 8/20/2008
8/20/2008 8/23/2008
8/20/2008 8/27/2008
8/20/2008 8/30/2008
8/20/2008 9/17/2008
8/20/2008 9/20/2008

and on and on... until the execution column reaches jan 1st, 2020.

the execution column will always be wednesdays, and the check column will always have 6 dates that correspond to each specific wednesday in the execution column.

which are: That same day, the saturday of that week, 1 wk from wednesday, 1 week from that saturday, and 4 weeks from wednesday and 4 weeks from that saturday.

above is a sample of the what i need, but it needs to continue on until 2020

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-21 : 22:42:09
make use of F_TABLE_DATE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-21 : 22:54:34
here it is the query using F_TABLE_DATE

SELECT	execution	= d.[DATE],
[CHECK] = DATEADD(DAY, n.num, d.[DATE])
FROM F_TABLE_DATE('2008-01-01', '2020-12-31') d
CROSS JOIN
(
SELECT num = 0 UNION ALL -- same DAY (Wed)
SELECT num = 3 UNION ALL -- the sat
SELECT num = 7 UNION ALL -- 1 week FROM Wed
SELECT num = 10 UNION ALL -- 1 week FROM Sat
SELECT num = 28 UNION ALL -- 4 week FROM Wed
SELECT num = 31 -- 4 week FROM Sat
) n
WHERE d.WEEKDAY_NAME = 'Wed'
ORDER BY execution, [CHECK]



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -