Replacing no rows with 0 value

Question

I have the below query

SELECT    DATEPART(YEAR, Date_Reported) AS 'Year',
          DATEPART(MONTH, Date_Reported) AS 'Month',
          DATEPART(DAY, Date_Reported) AS 'Day',
          COUNT(*) AS 'Transactions'
FROM TX
WHERE Date_Reported >= DATEADD(day,-7, GETDATE())
GROUP BY  DATEPART(DAY, Date_Reported),
          DATEPART(MONTH, Date_Reported),
          DATEPART(YEAR, Date_Reported)
ORDER BY  'Year',
          'Month',
          'Day'

What it gives me is the last 7 days of transactions, but when a days transaction is 0 it does not show a row. What I would like is for the no rows to be displayed as a 0 value under the transaction column like below:

YEAR | MONTH | DAY | TRANSACTIONS
2018 | 9     | 4   |     2
2018 | 9     | 5   |     2
2018 | 9     | 6   |     0
2018 | 9     | 7   |     5
2018 | 9     | 8   |     2
2018 | 9     | 9   |     0
2018 | 9     | 10  |     0

Any ideas?

Possible duplicate of [SQL Count to include zero values](https://stackoverflow.com/questions/12372708/sql-count-to-include-zero-values) — Tanner, Sep 11 '18 at 10:36
Possible duplicate of [How to have GROUP BY and COUNT include zero sums?](https://stackoverflow.com/questions/10586746/how-to-have-group-by-and-count-include-zero-sums) — Ruud Helderman, Sep 11 '18 at 10:38
Only thing I would add to Tanner's link is instead use a tally table, rather than an rCTE. — Thom A, Sep 11 '18 at 10:39
Maybe comparing to `DATEADD(day,-7, CAST(GETDATE() AS DATE))` would be more accurate if you want the count for the oldest date not to depend on the time you run the query. — LukStorms, Sep 11 '18 at 11:02

Paweł Dyl · Answer 1 · 2018-09-11T13:15:13.950

0

Here is one possible solution:

WITH LastDays AS --Base table
(
  SELECT CAST(DATEADD(DAY,-D,SYSDATETIME()) AS Date) D FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) T(D)
), TX AS --Demo data
(
  SELECT * FROM (VALUES
  (CAST(SYSDATETIME() AS Date)),
  (CAST(DATEADD(DAY,-2,SYSDATETIME()) AS Date))
  ) T(Date_Reported)
)
SELECT    DATEPART(YEAR, D) AS 'Year',
          DATEPART(MONTH, D) AS 'Month',
          DATEPART(DAY, D) AS 'Day',
          COUNT(Date_Reported) AS 'Transactions'
FROM LastDays
LEFT JOIN TX ON Date_Reported=D
GROUP BY  DATEPART(DAY, D),
          DATEPART(MONTH, D),
          DATEPART(YEAR, D)
ORDER BY  'Year',
          'Month',
          'Day'

edited Sep 11 '18 at 13:15

answered Sep 11 '18 at 10:39

Paweł Dyl

8,888
1
11
27

hi thanks, is this query dynamic as i cna see the demo data being used? Basically will this work without me having to manually edit demo datta – Taz Sep 11 '18 at 13:09
I don't have TX table so I have to simulate it. You can omit this section and use real table. Also make sure `Date`data type is used (not `DateTime`, `DateTime2`) to ignore hours, minutes and seconds in comparisons. – Paweł Dyl Sep 11 '18 at 13:14

score 0 · Answer 2 · answered Sep 11 '18 at 11:00

Here is Solution,

First You have to create Date Table as base Table Because of You don't have some date Records in TX as you have displayed as 0. As per below Query You can Create that Table and Store it into Temp Table.

;WITH cte AS (
SELECT cast(GETDATE() as date) AS myDate
UNION ALL
SELECT cast( DATEADD(DAY,-1,myDate) as date)
FROM cte
where myDate >= DATEADD(day,-7, GETDATE())
)
SELECT myDate Into #dateTable
FROM cte

Second, You can use Left Join #dateTable and Your TX as per Shown below.

select DATEPART(YEAR, myDate) AS 'Year',
       DATEPART(MONTH, myDate) AS 'Month',
       DATEPART(DAY, myDate) AS 'Day',
       Count(Date_Reported) as 'Transactions'
from #dateTable
LEFT JOIN #Tempdata on CAST(Date_Reported as DATE) = CASt(myDate as date)
GROUP BY  DATEPART(DAY, myDate),
          DATEPART(MONTH, myDate),
          DATEPART(YEAR, myDate)
ORDER BY  'Year',
          'Month',
          'Day'

Replacing no rows with 0 value

2 Answers2