November 21, 2008 at 4:23 pm
I have a table of events - holidays and vacations, actually. Each event has a date and label. I want to output the data in a calendar grid so I can visualize upcoming vacations. For example,
Mon Tue Wed Thu Fri
----- -------------- -------------- -------------- -------------- --------------
12/08
----- -------------- -------------- -------------- -------------- --------------
12/14 Ellen Tim
----- -------------- -------------- -------------- -------------- --------------
12/21 Ellen Christmas Christmas
John
Martha
----- -------------- -------------- -------------- -------------- --------------
12/28 Bob Bob Bob New Year's Bob
John
----- -------------- -------------- -------------- -------------- --------------
If multiple events occur on the same day, each event is output in the appropriate day column in separate rows. The events of a day can be ordered ascending on label. A row is output for a week even if there are no events in that week. Each week of rows is separated by a line. The table will have years of data; but I only want to output a specified time period, given by start and end date, or start date and number of weeks.
I just can't seem to visualize the solution. The hard part is sometimes events in a week go on one row, sometimes multiple rows. How do I get started?
Here's the data for the above example grid.
IF OBJECT_ID('TempDB..#event','U') IS NOT NULL
DROP TABLE #event
CREATE TABLE #event (
event_date datetime not null,
label varchar(15) not null,
PRIMARY KEY (event_date, label))
INSERT #event (event_date, label)
SELECT '20081127','Thanksgiving' UNION ALL
SELECT '20081225','Christmas' UNION ALL
SELECT '20081226','Christmas' UNION ALL
SELECT '20090101','New Year''s' UNION ALL
SELECT '20081215','Ellen' UNION ALL
SELECT '20081219','Tim' UNION ALL
SELECT '20081224','John' UNION ALL
SELECT '20081224','Ellen' UNION ALL
SELECT '20081224','Martha' UNION ALL
SELECT '20081229','Bob' UNION ALL
SELECT '20081230','Bob' UNION ALL
SELECT '20081231','Bob' UNION ALL
SELECT '20090102','John' UNION ALL
SELECT '20090102','Bob' UNION ALL
SELECT '20090123','Lynn'
DECLARE @start_date datetime SET @start_date = '20081208'
DECLARE @end_date datetime SET @end_date = '20090103'
November 21, 2008 at 5:11 pm
Start from this:
SELECT Date, dbo.ListOfEvents(date)
FROM dbo.Calendar
Calendar is a table with all available dates.
I use a modification of table Tally for this (if you don't know what it is search for the article from Jeff Moden).
I just added another column and populated it with CONVERT(datetime, N)
dbo.ListOfEvents is a scalar function returning concatenated list of events from your table Event.
Search this forum for "concatenation function" and chose one of the options posted here.
My personal favorite would be:
CREATE FUNCTION dbo.ListOfEvents (@Date datetime)
RETURN nvarchar(4000)
AS
DECLARE @List nvarchar (4000)
SELECT @List = ISNULL(@List + CHAR(13) + CHAR(10), '') + EventName
FROM dbo.Event
WHERE Date = @Date
ORDER BY EventName
RETURN @List
GO
And don't forget about clustered index (Date, EventName) on table Event.
_____________
Code for TallyGenerator
November 21, 2008 at 5:40 pm
I had a quick attempt by creating two additional tables, one holding a set of weeks, and another a set of numbers.
create table #weeks
( startdate datetime not null
)
insert into #weeks
select '2008-12-08' union all
select '2008-12-14' union all
select '2008-12-21' union all
select '2008-12-28'
create table #nums
( number int not null
)
insert into #nums
select 0 union all
select 1 union all
select 2 union all
select 3
select case n.number
when 1 then cast(month(startdate) as varchar(2)) + '/' + cast(day(startdate) as varchar(2))
else ''
end as ' ',
case n.number
when 0 then '--------------'
else IsNull(( select e.label
from #event e
where e.event_date = w.startdate + 1
and n.number = (select count(*) from #event e2 where e.event_date=e2.event_date and e2.label <= e.label) ),'')
end as Mon,
case n.number
when 0 then '--------------'
else IsNull(( select e.label
from #event e
where e.event_date = w.startdate + 2
and n.number = (select count(*) from #event e2 where e.event_date=e2.event_date and e2.label <= e.label) ),'')
end as Tue,
case n.number
when 0 then '--------------'
else IsNull(( select e.label
from #event e
where e.event_date = w.startdate + 3
and n.number = (select count(*) from #event e2 where e.event_date=e2.event_date and e2.label <= e.label) ),'')
end as Wed,
case n.number
when 0 then '--------------'
else IsNull(( select e.label
from #event e
where e.event_date = w.startdate + 4
and n.number = (select count(*) from #event e2 where e.event_date=e2.event_date and e2.label <= e.label) ),'')
end as Thur,
case n.number
when 0 then '--------------'
else IsNull(( select e.label
from #event e
where e.event_date = w.startdate + 5
and n.number = (select count(*) from #event e2 where e.event_date=e2.event_date and e2.label <= e.label) ),'')
end as Fri
from #weeks w
cross join #nums n
where n.number <= ( select case when max(c.count) = 0 then 1 else max(c.count) end
from (select count(*) 'count'
from #event e
where e.event_date between w.startdate and w.startdate + 7
group by day(e.event_date)) as c
)
order by w.startdate
Performance won't be great but I think it gives the right answer!
November 22, 2008 at 8:54 pm
I see what you're doing, David.
I'm sure I can figure out how to move the line between the weeks from before the week to after the week. And #weeks can be generated on the fly with #nums. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply