Fill exhaustive data history from sparse date-vs-value data in SQL

Question

I have a table with the following information

ID, USER, DATE, AMOUNT
id1, user2, 01/02/2015, 100
id2, user1, 03/02/2015, 200
id3, user2, 06/02/2015, 400
id4, user3,  04/02/2015, 900

what I'd like to obtain is a table listing all dates in the [min_date, max_date] interval and the corresponding values for each user.

USER, user1, user2, user3
01/02/2015,  0, 100, 0
02/02/2015, 0, 0, 0
03/02/2015, 300, 0, 0
04/02/2015, 0, 0, 900
05/02/2015, 0, 0, 0
06/02/2015, 0, 400, 0

I am using the technique suggested in this post to generate a column of dates.

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATE,'01/01/2011', 103)
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < CONVERT(date, '30/11/2016', 103)
) SELECT
 [Date]
FROM
 Dates
 OPTION (MAXRECURSION 10000)
 --OPTION (MAXRECURSION 45)

score 1 · Accepted Answer · answered Jan 12 '17 at 11:33

Try this:

CREATE TABLE IDS (ID CHAR(100), USERS CHAR(100), DATES  CHAR(100), AMOUNT  INT)
INSERT INTO IDS VALUES ('id1', 'user2', '01/02/2015', 100)
INSERT INTO IDS VALUES ('id2', 'user1', '03/02/2015', 200)
INSERT INTO IDS VALUES ('id3', 'user2', '06/02/2015', 400)
INSERT INTO IDS VALUES ('id4', 'user3', '04/02/2015', 900)


WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATE,'02/01/2015', 103)
        UNION ALL SELECT
         [Date] = DATEADD(MONTH, 1, [Date])
        FROM
         Dates
        WHERE
         Date < CONVERT(date, '30/12/2015', 103)
)

SELECT [Date] AS [USER] INTO [DATE] FROM Dates OPTION (MAXRECURSION 1200)



select [DATE].[USER],COALESCE ([user1],0) AS 'user1',COALESCE ([user2],0) AS 'user2',COALESCE ([user3],0) AS 'user3'
from 
(
  select DATES, USERS, AMOUNT
  from IDS
) src
pivot
(
  sum(AMOUNT)
  for USERS in ([user1], [user2], [user3])
) piv
RIGHT JOIN [DATE] ON [DATE].[USER]=PIV.DATES

Fill exhaustive data history from sparse date-vs-value data in SQL

1 Answers1