Get Total on SQL Dynamic (Calendar) Pivot Tables

Question

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)

Try putting your code into a code formatting block, it's fun! — Frank Bryce, Jul 24 '15 at 00:54

score 0 · Accepted Answer · answered Jul 24 '15 at 03:25

Here is my attempt using a dynamic crosstab:

SQL Fiddle

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

No problem, glad I could help. – Felix Pamittan Jul 24 '15 at 15:30 — Felix Pamittan, Jul 24 '15 at 15:30

Get Total on SQL Dynamic (Calendar) Pivot Tables

1 Answers1