December 13, 2010 at 10:49 am
Good morning,
Not a clue where to start with this one...
.
I am building a calendar view on a web page. I need to display one month at a time. On the page, I would show the events that are valid for each day. If an event spans 10 days, it needs to be displayed on each day within the view.
Test Data
CREATE TABLE [dbo].[EventData](
[EventID] [int] NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL
) ON [PRIMARY]
insert into test (eventID, startdate, enddate)
SELECT 1, '11/01/2010', '12/15/2010'
UNION
SELECT 2, '12/01/2010', '12/05/2010'
UNION
SELECT 3, '12/01/2010', '12/01/2010'
UNION
SELECT 4, '10/01/2010', '01/15/2011'
UNION
SELECT 5, '12/01/2010', '12/03/2010'
.
I'd like to be able to send in the beginning/end range for a month, and get back all the events with their associated days. For example, for 12/2010, the return set would look like
EventID Date
1 12/1/2010
1 12/2/2010
etc up to
1 12/15/2010
2 12/1/2010
etc up to
2 12/5/2010
3 12/1/2010
4 12/1/2010
etc up to
4 12/31/2010
etc etc etc
.
As mentioned, I haven't a clue how to attack this one. I could do it in the front-end by going day to day, but that seems nuts.
.
Thank you in advance for your guidance.
-John
December 13, 2010 at 11:28 am
Create a Calendar table, or use a Numbers/Tally table (physical or virtual).
It'll look sort of like the below.
DECLARE @StartDate DATETIME;
SELECT @StartDate = '12/1/10';
;WITH
Numbers (Number) AS
(SELECT TOP 32 ROW_NUMBER() OVER (ORDER BY OBJECT_ID)-1
FROM sys.all_objects),
Calendar (Date) AS
(SELECT DATEADD(DAY, Number, @StartDate)
FROM Numbers
WHERE Number < DATEDIFF(DAY, @StartDate, DATEADD(MONTH, 1, @StartDate)))
SELECT *
FROM dbo.MyEventsTable
INNER JOIN Calendar
ON Date BETWEEN MyEventsTable.StartDate AND MyEventsTable.EndDate;
You may want to use a different parameter, or make sure the parameter is the first day of the desired month.
You could pretty easily make it go from Sunday of the first week of the desired month to Saturday of the last week. E.g.: If the month starts on a Tuesday, you'd want the prior Monday and Sunday, and if it ends on a Thursday, you'd want the following Friday and Saturday. If you want that, you need to do some calculations first, but a real Calendar table (list of dates with characteristics of each) would make it trivial to get them done.
Does that at least get you started?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2010 at 11:57 am
That is perfect. Actually, it gives me exactly what I need. Thank you.
December 14, 2010 at 6:49 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply