I am looking for help generating a table with dynamic columns and calculated values in SQL Server from the following (abbreviated) source tables:
Assignments Source Table
Name StartDate EndDate Value Factor Jim 2013-08-01 2013-09-06 200.0 0.5 Bob 2013-07-27 2013-11-01 140.0 1.0 Alice 2013-08-29 2014-03-22 200.0 0.8 Jim 2013-08-20 2013-09-01 250.0 0.5
Reporting Weeks Source Table
WeekEndDate 2013-08-18 2013-08-25 2013-09-01 2013-09-08
Desired Results Table
Name StartDate EndDate Value Factor 2013-08-18 2013-08-25 2013-09-01 2013-09-08 Jim 2013-08-01 2013-09-06 200.0 0.5 100.0 100.0 100.0 0.0 Bob 2013-07-27 2013-11-01 140.0 1.0 140.0 140.0 140.0 140.0 Alice 2013-08-29 2014-03-22 200.0 0.8 0.0 0.0 160.0 160.0 Jim 2013-08-20 2013-09-01 250.0 0.5 0.0 125.0 125.0 0.0
Basically, I need to turn the reporting rows table into columns and then calculate a value where the pivoted column date (Reporting Weeks/WeekEndDate) is between the StartDate and EndDate. If the date is outside that range the value should be set to zero. The "Reporting Weeks" table can vary over time, and may be generated from another query. The StartDate and EndDate will usually not match the WeekEndDate. I have been going over a lot of questions/answers around dynamic sql pivots, but haven't found anything that I can adapt to fit the need given my limited background in this area.
Before tackling the dynamic SQL version of this, I would first write the query as a static version using your limited dates. In order to get the result, I would calculate the value that you need in a subquery and then pivot on the weekenddates.
The basic syntax if you have a limited number of values would be:
select name, startdate, enddate,
value, factor,
coalesce([2013-08-18], 0) [2013-08-18],
coalesce([2013-08-25], 0) [2013-08-25],
coalesce([2013-09-01], 0) [2013-09-01],
coalesce([2013-09-08], 0) [2013-09-08]
from
(
select a.name,
a.startdate,
a.enddate,
a.value,
a.factor,
convert(varchar(10), r.weekenddate, 120) weekenddate,
amt = a.value * a.factor
from assignments a
inner join reportingweeks r
on r.weekenddate >= a.startdate
and r.weekenddate <= a.enddate
) d
pivot
(
sum(amt)
for weekenddate in ([2013-08-18], [2013-08-25], [2013-09-01],
[2013-09-08])
) piv;
See SQL Fiddle with Demo. Once you have the correct logic, then you can convert the query into dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@colsNull AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(dt)
from
(
select convert(varchar(10), weekenddate, 120) dt
from reportingweeks
) d
group by dt
order by dt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsNull = STUFF((SELECT ', coalesce(' + QUOTENAME(dt)+', 0) as '+QUOTENAME(dt)
from
(
select convert(varchar(10), weekenddate, 120) dt
from reportingweeks
) d
group by dt
order by dt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name, startdate, enddate,
value, factor, ' + @colsNull + '
from
(
select a.name,
a.startdate,
a.enddate,
a.value,
a.factor,
convert(varchar(10), r.weekenddate, 120) weekenddate,
amt = a.value * a.factor
from assignments a
inner join reportingweeks r
on r.weekenddate >= a.startdate
and r.weekenddate <= a.enddate
) x
pivot
(
sum(amt)
for weekenddate in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. Both will give a result:
| NAME | STARTDATE | ENDDATE | VALUE | FACTOR | 2013-08-18 | 2013-08-25 | 2013-09-01 | 2013-09-08 |
|-------|------------|------------|-------|--------|------------|------------|------------|------------|
| Alice | 2013-08-29 | 2014-03-22 | 200 | 0.8 | 0 | 0 | 160 | 160 |
| Bob | 2013-07-27 | 2013-11-01 | 140 | 1 | 140 | 140 | 140 | 140 |
| Jim | 2013-08-01 | 2013-09-06 | 200 | 0.5 | 100 | 100 | 100 | 0 |
| Jim | 2013-08-20 | 2013-09-01 | 250 | 0.5 | 0 | 125 | 125 | 0 |