[updated 2013-12-18 11:22 UTC]
[updated 2013-12-18 14:19 UTC]
[updated 2013-12-19 11:11 UTC]
as i did not know the name of the table you are using, i created one named yeahyeah. you should replace that with the name of your own table obviously. i have inserted the values you mentioned (fromdate todate duration as shown in your question).
the procedure should look like this. i called it pivotit.
create procedure pivotit (@start_date date, @end_date date)
as
/*
step 1:
identify all the data you need to fill the pivoted table.
this is achieved by using your daterange generator and joining
it with your table. the result is kept in a dynamic table
*/
declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
SELECT @start_date DateValue
UNION ALL
SELECT dateadd(dd,1,DateValue)
FROM DateRange
WHERE dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on ( DateRange.DateValue >= yeahyeah.FromDate
and DateRange.DateValue <= yeahyeah.ToDate);
/*
step 2:
for pivot you need all the columns that will be adressed. so
we create a string with all the distinct dates from the dynamic
table. these will then be put into a format like [1], [2], [3], ...
to create a dynamic select.
*/
declare @p varchar(max) = '';
declare @s varchar(max);
select @p = @p + ', [' + CONVERT(varchar,d) + ']' from (select distinct d from @acols) a;
set @p = SUBSTRING(@p,3,len(@p)-2);
/*
step 3:
create the dynamic select.
alas neither the dynamic table nor the parameters are available from
inside the dynamic sql. i might try to use bind variables, but was
not 100% sure if that would work here. so put in the declares for start_
and end_date from the procedure parameters and build up the dynamic table
once more.
then i use @p for the pivoted select. this is done by selecting the column
for the rows (id) and all the values from the pivot as columns (@p).
details on the whole pivot thing are here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
basically you tell sql-server (explicitly) what you want as the columns (@p),
what rows you want (id) and how to aggregate the values in the intersections
(sum(l))
[update 2013-12-19]
i added a routine that makes a cartesian product that has all the combination
of dates and ids in @acols, finds the ones that are missing in @acols and inserts
them with duration 0. then the pivoted cells are complete and display the zero
instead of NULL. you cannot use isnull or coalesce here since not the value
of the cell is NULL, the intersection simply did not exist.
*/
set @s = '
declare @start_date date = convert(date,'''+CONVERT(varchar,@start_date,112)+''',112);
declare @end_date date = convert(date,'''+CONVERT(varchar,@end_date,112)+''',112);
declare @acols table (i int, d date, l int);
;WITH DateRange AS
(
SELECT @start_date DateValue
UNION ALL
SELECT dateadd(dd,1,DateValue)
FROM DateRange
WHERE dateadd(dd,1,DateValue) <= @end_date
)
insert into @acols (i, d, l)
select id, DateValue, Duration from DateRange
join yeahyeah on ( DateRange.DateValue >= yeahyeah.FromDate
and DateRange.DateValue <= yeahyeah.ToDate);
with cart as
(
select distinct
a.i
, b.d
from @acols a
join @acols b
on 1=1
)
insert into @acols (i, d, l)
select cart.i
, cart.d
, 0
from cart
left outer join
@acols a
on cart.i = a.i
and cart.d = a.d
where a.i is null;
select id, '+@p+'
from
( select convert(varchar,d) as d
, l
, i as id
from @acols ) as sourcetable
pivot (
sum(l)
for d in ('+@p+')
) as pivottable';
execute(@s);
after you created the procedure you can do this:
exec pivotit @start_date = '2013-01-01', @end_date = '2013-01-31'
which will then yield:
