SQL Pivot by Date
Sometimes in CF I'm faced with the challenge of constructing something resembling a schedule of events over a range of arbitrary dates. The goal is really to display all slots whether available or not. One way is to query the database for all matching events during the date and have the code to loop over each slot and if there is something to fill it, put it in there, otherwise do something different like make a link available.
An alternative approach is to have the SQL return a row for each date and each resource you are considering, regardless of availability.
This is a little more challenging than you would think since you have no master date table to LEFT JOIN on. Here is a timesaving snippet to help. It's a UDF for SQL Server 2000 which, when called with a start date and a number of days to return will give you the master date table. Since it is a TABLE FUNCTION, any stored procedures calling it will not be forced to recompile (and slow down your app) because it avoids the creation of temporary tables.
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fn_getDays (
/*
WRITTEN BY Adam Howitt adamhowitt@gmail.com
FUNCTION getDays
WRITTEN FOR SQL Server 2000
DATE 05.27.2005
Usage:
select dt
from fn_getDays('05/27/2005',5)
*/
@st_dt datetime,
@num_days int )
RETURNS @dateTable TABLE ( dt datetime not null)
AS
BEGIN
WHILE (@num_days > 0)
BEGIN
SET @st_dt = dateadd(day,1,@st_dt)
INSERT INTO @dateTable (dt)
VALUES (@st_dt)
SET @num_days = @num_days - 1
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
from fn_getDays('05/27/2005',5) d
CROSS JOIN tb_resources r
LEFT JOIN tb_events e
ON d.dt = e.event_dt