How to Show missing Date based on a given date range?

Question

I have below query which works fine, it displays all the values for that particular location how much was the refund amount. However, if I want to show monthly report, I need to show those days were no refund is there in my Table 1 and display zero values for the same:

SELECT businessDate AS 'Business Date'
    ,Sum(convert(DECIMAL, ISNULL(T1.Amount, 0)) * 1) AS 'Refund Amount'
FROM table1 T1
WHERE (
        (T1.id = '1')
        AND (T1.businessDate BETWEEN '20160201' AND '20160229')
        AND (T1.Amount IS NOT NULL)
        )
GROUP BY T1.businessDate

my Current output is:

Business Date   Refund Amount
  20160202            14
  20160203            19

It should be:

Business Date   Refund Amount
  20160201             0
  20160202            14
  20160203            19

so how to fix my query to cater the above??

few ways to build a temporary date table if you dont have one.. http://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par then you just need to left join to this — JamieD77, Mar 22 '16 at 20:43

Chris Mack · Accepted Answer · 2016-03-22T21:16:40.043

1

I would use a recursive CTE as follows:

WITH cte
AS
    (
        SELECT CAST('2016-02-01' AS date) [Date]
        UNION ALL
        SELECT DATEADD(DAY, 1, [Date])
        FROM cte
        WHERE DATEADD(DAY, 1, [Date]) <= '2016-02-29'
    )

SELECT
    cte.[Date] [Business Date]
    , SUM(CONVERT(decimal, ISNULL(T1.Amount, 0)) * 1) [Refund Amount]
FROM
    cte
    LEFT JOIN (SELECT * FROM table1 WHERE id = 1) T1 ON cte.[Date] = T1.businessDate
GROUP BY cte.[Date]

edited Mar 22 '16 at 21:16

answered Mar 22 '16 at 20:52

Chris Mack

5,148
2
12
29

For more about Common Table Expression (CTE), check http://stackoverflow.com/a/14275097/122139 – Smandoli Mar 22 '16 at 20:56

How to Show missing Date based on a given date range?

1 Answers1