Show LastDateOfMonth even where there is no value

Question

Here is what I´m doing:

  WITH cte AS (
  SELECT * FROM TimeDim 
  )
SELECT t.TimeDimPK, c.ID 
FROM CTE AS t
LEFT OUTER JOIN TABLE c ON c.TimeDimFK = t.TimeDimPK
ORDER BY t.TimeDimPK
WHERE c.ID = 1

Result, which is missing dates as shown below:

TimeDimPK   ID
20120930    1
20121231    1
20130131    1

What I´m trying to get

TimeDimPK   ID
20120930    1
20121031    NULL
20121130    NULL
20121231    1
20130131    1

score 2 · Answer 1 · answered Dec 04 '14 at 16:04

2

It looks like your WHERE clause is getting rid of the other dates. Try this instead:

WITH    cte
          AS ( SELECT   TimedimPK
               FROM     TimeDim
             )
    SELECT  t.TimeDimPK ,
            c.ID
    FROM    cte t
            LEFT OUTER JOIN TABLEname c ON c.TimeDimFK = t.TimeDimPK
    WHERE   c.ID = 1
            OR c.ID IS NULL
    ORDER BY t.TimeDimPK

answered Dec 04 '14 at 16:04

Dave.Gugg

6,561
3
24
43

This will not work for me :( I need the query to generate the dates where missing, while using a where on a ID. – AceAlfred Dec 04 '14 at 16:24
You'll need to provide more descriptive feedback then "This will not work for me". When you run the query, what is missing? – Dave.Gugg Dec 04 '14 at 16:32
I need the query to generate the dates where missing, while using a where condition on a specific ID. – AceAlfred Dec 05 '14 at 09:52

Jarle Bjørnbeth · Answer 2 · 2014-12-04T23:38:49.817

You can try to use a "number/date generator", this queries will fill in all missing days, then select the last day of the month. I am not sure about all your data details, so I am currently giving you two suggestions:

1 - Compact suggestion:

DECLARE @minDate DATETIME, @maxDate DATETIME;

SELECT @minDate = MIN(TimeDimPK), @maxDate = MAX(TimeDimPK) FROM TimeDim;

WITH DateGenerator AS
(
    --Create a list with a lot of dates from @minDate
    SELECT TimeDimPK = CONVERT(DATE, DATEADD(dd, ROW_NUMBER() OVER (ORDER BY OBJECT_ID), @minDate)) FROM sys.objects
)
--List all days, including missing days
SELECT TimeDimPK
, c.ID
FROM DateGenerator n
LEFT JOIN AnotherTable c ON c.TimeDimFK = n.TimeDimPK
WHERE 
--Stop the number generator at the last day of the last month from the cte table
n.TimeDimPK <=  CONVERT(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @maxDate )+1,0)))
--This will get the last day of every month
AND TimeDimPK = CONVERT(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,TimeDimPK)+1,0)))

2 - Suggestion with your cte clause, if you want to use it for some tweaking:

WITH NumberGenerator AS
(
    --Create a number list 1,2,3,4,5,6,7,8,++
    SELECT Number = ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.objects
), cte AS (
    --Your cte query with a date number based on the days between the firts and current days
    SELECT 
    TimeDimPK = CONVERT(DATETIME, TimeDimPK)
    --Get the number of days to add from the first day in your table
    , DateNumber = DATEDIFF(dd,  MIN(TimeDimPK) OVER (), TimeDimPK)
    FROM #TimeDim 
), TableWithMissingDates AS
(
    --Fill missing days
    SELECT TimeDimPK = CONVERT(DATE, DATEADD(dd, n.Number - 1, MIN(t.TimeDimPK) OVER ()))
    , c.ID
    FROM NumberGenerator n
    LEFT JOIN cte t ON t.DateNumber = n.Number 
    LEFT JOIN #Test2 c ON c.TimeDimFK = t.TimeDimPK
    --Stop the number generator at the last day of the last month from the cte table
    WHERE CONVERT(DATE, DATEADD(dd, n.Number - 1, (SELECT MIN(TimeDimPK) FROM cte))) <  CONVERT(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, (SELECT MAX(TimeDimPK) FROM cte) )+1,0)))
)
SELECT * FROM TableWithMissingDates
WHERE 
--This will get the last day of every month
TimeDimPK = CONVERT(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,TimeDimPK)+1,0)))

Both queries will return the same table:

enter image description here

Show LastDateOfMonth even where there is no value

2 Answers2