I am trying to get an SQL Server Dynamic Pivot table to work that allows me to count and then sum the number of columns. The purpose of the pivot table is to create a report of all the days individuals were staying in a city and the total number of days(in a month). So, for example, Person A was staying everyday in June - the total will be 30.Person B only started staying on the 3rd of June - the total will be 27 etc. The data table only consists of Name, ArriveDate, DepartDate...the days of the month are created through an SQL query.
+------+------------+------------+-------+-------+-------+-----+-------+-------+-------+
| Name | ArriveDate | DepartDate | 06-01 | 06-02 | 06-03 | ... | 06-29 | 06-30 | Total |
+------+------------+------------+-------+-------+-------+-----+-------+-------+-------+
| A | 2014-06-01 | 2014-06-23 | 1 | 1 | 1 | ... | 1 | 1 | 30 |
| B | 2014-06-02 | 2014-06-23 | 0 | 1 | 1 | ... | 1 | 1 | 27 |
| C | 2014-06-02 | 2014-06-23 | 0 | 0 | 0 | ... | 1 | 1 | 16 |
+------+------------+------------+-------+-------+-------+-----+-------+-------+-------+Here is the query I have so far:
DROP TABLE #tempDates
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
;WITH cte (datelist, maxdate) AS
(
SELECT MIN(ArriveDate) datelist,
EOMONTH(GETDATE()) AS maxdate
FROM Reservation
UNION ALL
SELECT dateadd(dd, 1, datelist), maxdate
FROM cte
WHERE datelist < maxdate
)
SELECT c.datelist
INTO #tempDates
FROM cte c
SELECT @cols = STUFF(( SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120))
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT ID,
ArriveDate,
DepartDate,
' + @cols + '
FROM
(
SELECT r.ID,
r.ArriveDate,
r.DepartDate,
d.datelist,
convert(CHAR(10), datelist, 120) PivotDate
FROM
Reservation r
LEFT JOIN
#tempDates d
ON d.datelist BETWEEN rg.ArriveDate AND GETDATE()
) x
pivot
(
COUNT(datelist)
FOR PivotDate IN (' + @cols + ')
) p '
EXECUTE (@query)
Here is my attempt using a dynamic crosstab:
Sample Data:
Name ArriveDate DepartDate
---- ---------- ----------
A 2015-07-01 2015-07-23
B 2015-07-02 2015-07-04
C 2015-07-03 2015-07-31
Dynamic Crosstab Solution:
DECLARE @minDate AS DATE,
@maxDate AS DATE
SELECT @minDate = DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)),
@maxDate = EOMONTH(GETDATE())
CREATE TABLE #dates(dt DATE)
DECLARE @sql1 VARCHAR(MAX) = '',
@sql2 VARCHAR(MAX) = '',
@sql3 VARCHAR(MAX) = '';
WITH E1(N) AS(
SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b),
Tally(N) AS(
SELECT TOP(DATEDIFF(DAY, @minDate, @maxDate) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E8
)
INSERT INTO #dates
SELECT DATEADD(DAY, N - 1, @minDate)
FROM Tally
SELECT @sql1 =
'SELECT
r.Name
, r.ArriveDate
, r.DepartDate' + CHAR(10)
SELECT @sql2 = @sql2 +
' , SUM(CASE WHEN d.dt = CAST(''' + CONVERT(VARCHAR(8), dt, 112) + ''' AS DATE) THEN 1 ELSE 0 END) AS '
+ QUOTENAME(CONVERT(VARCHAR(10), dt, 120)) + CHAR(10)
FROM #dates
ORDER BY dt
SELECT @sql2 = @sql2 +
' , COUNT(d.dt) AS [total]' + CHAR(10)
SELECT @sql3 =
'FROM Reservation r
LEFT JOIN #dates d
ON d.dt BETWEEN r.ArriveDate AND r.DepartDate
GROUP BY
r.Name, r.ArriveDate, r.DepartDate'
PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)
DROP TABLE #dates
Result:
Name ArriveDate DepartDate 2015-07-01 2015-07-02 2015-07-03 ..... 2015-07-29 2015-07-30 2015-07-31 total
---- ---------- ---------- ----------- ----------- ----------- ..... ----------- ----------- ----------- -----------
A 2015-07-01 2015-07-23 1 1 1 ..... 0 0 0 23
B 2015-07-02 2015-07-04 0 1 1 ..... 0 0 0 3
C 2015-07-03 2015-07-31 0 0 1 ..... 1 1 1 29