How to add missing dates when calculating count on a table

user_14008258 0
Viewed 236

I have a sales table that records sale of an item. simplified version is below

ID ItemID SaleTime
1 1234 2020-12-01 12:44:22
2 1234 2020-12-01 17:12:22
3 1234 2020-12-02 12:44:22
4 1234 2020-12-04 17:12:22

i am writing a query to count items sold each day which is working fine and giving following results.

ID ItemID Date Sale count
1 1234 2020-12-01 2
2 1234 2020-12-02 1
3 1234 2020-12-04 1

how do I include days where no sale was made with zero count like following.

ID ItemID Date Sale count
1 1234 2020-12-01 2
2 1234 2020-12-02 1
3 1234 2020-12-03 0
4 1234 2020-12-04 1
date-arithmetic count datetime sql-server sql
4 Answers

One option uses a recursive query to generate the dates. you can then cross join that with the list of distinct items available in the table, and bring the table with a left join. The last step is aggregation:

with cte as (
    select min(convert(date, saletime)) as dt, max(convert(date, saletime)) as max_dt from mytable
    union all
    select dateadd(day, 1, dt), max_dt from cte where dt < max_dt
)
select c.dt, i.itemid, count(t.id) as sale_count
from cte c
cross join (select distinct itemid from mytable) i
left join mytable t 
    on  t.itemid = i.itemid
    and t.date >= c.dt
    and t.date <  dateadd(day, 1, c.dt)
group by c.dt, i.itemid

In a real life situation, you would probably have a separate referential table to store the items, that you would use instead of the select distinct subquery.

user_106767160 
DECLARE @start_date DATETIME = '2020-11-28 00:00:00.000';
DECLARE @end_date DATETIME = '2020-12-13 00:00:00.000';

;WITH AllDays AS (SELECT @start_date AS [DATE]
                   UNION ALL
                   SELECT DATEADD(DAY, 1, [Date])
                   FROM   AllDays
                   WHERE  [Date] < @end_date),
     Items AS (SELECT distinct itemid from Sales)
     SELECT ROW_NUMBER() OVER (ORDER BY i.itemid, a.[DATE]) AS [ID], i.itemid as [ITEMID], a.[DATE],  count(s.itemid) AS [SALE COUNT]
     FROM Items i
     CROSS JOIN AllDays a
     LEFT JOIN Sales s ON a.[DATE] = convert(date, s.salestime) and i.itemid = s.itemid
     GROUP BY i.itemid, a.[DATE]
     ORDER BY i.itemid, a.[DATE]
     OPTION (MAXRECURSION 0)

Results (for two item Ids and 16 days):

+----+--------+-------------------------+------------+
| ID | ITEMID | DATE                    | SALE COUNT |
+----+--------+-------------------------+------------+
| 1  | 1234   | 2020-11-28 00:00:00.000 | 0          |
| 2  | 1234   | 2020-11-29 00:00:00.000 | 0          |
| 3  | 1234   | 2020-11-30 00:00:00.000 | 0          |
| 4  | 1234   | 2020-12-01 00:00:00.000 | 2          |
| 5  | 1234   | 2020-12-02 00:00:00.000 | 1          |
| 6  | 1234   | 2020-12-03 00:00:00.000 | 0          |
| 7  | 1234   | 2020-12-04 00:00:00.000 | 1          |
| 8  | 1234   | 2020-12-05 00:00:00.000 | 0          |
| 9  | 1234   | 2020-12-06 00:00:00.000 | 0          |
| 10 | 1234   | 2020-12-07 00:00:00.000 | 0          |
| 11 | 1234   | 2020-12-08 00:00:00.000 | 0          |
| 12 | 1234   | 2020-12-09 00:00:00.000 | 0          |
| 13 | 1234   | 2020-12-10 00:00:00.000 | 0          |
| 14 | 1234   | 2020-12-11 00:00:00.000 | 0          |
| 15 | 1234   | 2020-12-12 00:00:00.000 | 0          |
| 16 | 1234   | 2020-12-13 00:00:00.000 | 0          |
| 17 | 1235   | 2020-11-28 00:00:00.000 | 0          |
| 18 | 1235   | 2020-11-29 00:00:00.000 | 0          |
| 19 | 1235   | 2020-11-30 00:00:00.000 | 0          |
| 20 | 1235   | 2020-12-01 00:00:00.000 | 0          |
| 21 | 1235   | 2020-12-02 00:00:00.000 | 0          |
| 22 | 1235   | 2020-12-03 00:00:00.000 | 0          |
| 23 | 1235   | 2020-12-04 00:00:00.000 | 1          |
| 24 | 1235   | 2020-12-05 00:00:00.000 | 0          |
| 25 | 1235   | 2020-12-06 00:00:00.000 | 0          |
| 26 | 1235   | 2020-12-07 00:00:00.000 | 0          |
| 27 | 1235   | 2020-12-08 00:00:00.000 | 0          |
| 28 | 1235   | 2020-12-09 00:00:00.000 | 0          |
| 29 | 1235   | 2020-12-10 00:00:00.000 | 0          |
| 30 | 1235   | 2020-12-11 00:00:00.000 | 0          |
| 31 | 1235   | 2020-12-12 00:00:00.000 | 0          |
| 32 | 1235   | 2020-12-13 00:00:00.000 | 0          |
+----+--------+-------------------------+------------+
user_131218790

One approach uses a calendar table which keeps track of all the dates you want to appear in your report:

WITH dates AS (
    SELECT CAST('20201201' AS date) AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
    FROM dates
    WHERE DATEADD(dd, 1, dt) <= '20201231'
)

SELECT
    t.ITEMID,
    d.dt,
    COUNT(t.ID) AS [SALE COUNT]
FROM dates d
LEFT JOIN yourTable t
    ON CAST(t.SALETIME AS date) = d.dt
GROUP BY
    t.ITEMID,
    d.dt
ORDER BY
    d.dt;
user_18632290

As other answers have noted, one solution is a recursive CTE. You specifically want to do that for one itemid, so I would suggest:

with dates as (
      select min(convert(date, saletime)) as dte, max(convert(date, saletime)) as max_dte
      from mytable
      union all
      select dateadd(day, 1, dt), max_dt
      from dates
      where dte < max_dte
    )
select c.dt, v.itemid, count(t.id) as sale_count
from dates d cross join
     (values (1234)) v(itemid) left join
     mytable t
     on t.itemid = v.itemid and
        t.date >= d.dte
        t.date <  dateadd(day, 1, c.dt)
group by d.dte, v.itemid;

Note that if you have more than 100 days, then you need to also add OPTION (MAXRECURSION 0) to avoid an error in the date generation.

If you want the solution for all item ids, then GMB's answer is better answer.

user_11440350
Related