FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

DECLARE @s date = '20140518', @e date = '20140521';

DECLARE @MyTable TABLE
(
  ID int NOT NULL, 
  Site varchar(10) NOT NULL,
  dt date NOT NULL, 
  Frequency int
);

INSERT @MyTable VALUES
(1,'A','2014-05-18',5),
(1,'A','2014-05-19',4),
(1,'A','2014-05-20',25),

(1,'B','2014-05-18',54),
(1,'B','2014-05-19',41),
(1,'B','2014-05-20',25),
(1,'B','2014-05-21',15),

(2,'A','2014-05-20',7),
(2,'A','2014-05-21',722),

(2,'B','2014-05-18',9),
(2,'B','2014-05-19',5),
(2,'B','2014-05-21',32),

(3,'A','2014-05-18',4),
(3,'A','2014-05-20',1),

(3,'B','2014-05-18',2),
(3,'B','2014-05-19',11),
(3,'B','2014-05-20',27),

(4,'A','2014-05-18',6),
(4,'B','2014-05-20',33);

-- cte number range
;WITH cte_n AS
(
  SELECT n = 0 UNION ALL SELECT n + 1 FROM cte_n
  WHERE n < DATEDIFF(DAY, @s, @e)
),
-- cte date range
cte_range AS
(
  SELECT rdt = DATEADD(DAY, n, @s) FROM cte_n
),
-- source data in range
cte_src AS
(
  SELECT t.ID, t.Site, t.dt, t.Frequency 
  FROM @MyTable AS t
  WHERE EXISTS (SELECT 1 FROM cte_range WHERE rdt = t.dt)
),
-- fill the holes
cte_pop AS
(
  SELECT 
    IDs.ID,
    IDs.Site,
    dt = rdt,
    Frequency = COALESCE(t.Frequency, 0)
  FROM cte_range
  CROSS JOIN (SELECT ID,Site FROM cte_src GROUP BY ID,Site) IDs
  LEFT OUTER JOIN @MyTable AS t
    ON t.dt = rdt AND IDs.ID = t.ID AND IDs.Site = t.Site
)

-- select * from cte_pop;

-- with outer select. this works *
/*
SELECT * FROM
(
    SELECT 
        ID,
        Site,
        dt,
        Frequency
    FROM cte_pop
) t 
PIVOT(
    SUM(Frequency)
    FOR dt IN ([2014-05-18],[2014-05-19],[2014-05-20],[2014-05-21])
) AS p
ORDER BY ID, Site
*/

SELECT 
    ID,
    Site,
    dt,
    Frequency
FROM cte_pop

-- if I uncomment this, errors: with invalid column dt and Frequency
PIVOT(
    SUM(Frequency)
    FOR dt IN ([2014-05-18],[2014-05-19],[2014-05-20],[2014-05-21])
) AS p
ORDER BY ID, Site

Msg 207, Level 16, State 1, Server e8f807d68ffb, Line 95
Invalid column name 'dt'.
Msg 207, Level 16, State 1, Server e8f807d68ffb, Line 96
Invalid column name 'Frequency'.