SQL Server 2008 Express calendar table

Question

I have the following table

event_ID      Int,
Start_Date    Date,
End_Date      Date,

I want to create a permanent calender table that include the following columns

calender_ID,
Event_ID,
Event_Date,

and I want the Event_Date to list all dates between Start_date and End_Date

I'm using SQL Server Express 2008 in Visual Studio

thanks for any kind of help

Hammam

score 0 · Answer 1 · edited May 23 '17 at 11:56

First, create a function like "ExplodeDates" mentioned in this SO Thread: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788

Next, in the stored procedure / trigger you plan on keeping the event and calender tables in sync with, you'd essentially just do something like the following:

DELETE FROM CalenderTable WHERE Event_ID=@EventID

INSERT INTO CalenderTable (Event_ID, Event_Date)
SELECT Event.Event_ID, Dates.TheDate
FROM Event
CROSS APPLY ExplodeDates(Event.Start_Date, Event.End_Date) Dates
WHERE Event.Event_ID=@EventID

I don't have SQL on this machine, so I haven't had a chance to test this. It might need some tweaking, but this should at least get you going down a workable path...

SQL Server 2008 Express calendar table

1 Answers1