How to add missing dates when calculating count on a table

Question

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

Do you want to query the sales per day for each item distinctly? First I recommend avoiding the column ìd at all as technical keys often are more confusing than informational in the result. Let item_id be your first column and sort your query by that first, then by date. Be extra careful on days where no samples of a certain item got sold as there can be null values which can let you miss some rows of your result! — procra
– procra, Commented Dec 8, 2020 at 10:35
Maybe a left join in combination with the coalesce()-function could do a simple job for you, but i'm currently looking up for that myself — procra
– procra, Commented Dec 8, 2020 at 10:43
Do you want the same range for every item? A fixed range? A range based on only the first and last date for each item? — Gordon Linoff
– Gordon Linoff, Commented Dec 8, 2020 at 11:55

GMB · Accepted Answer · 2020-12-08 10:31:37Z

1

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.

answered Dec 8, 2020 at 10:31

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Useme Alehosaini · Accepted Answer · 2020-12-08 11:38:02Z

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          |
+----+--------+-------------------------+------------+

Tim Biegeleisen · Accepted Answer · 2020-12-08 10:33:00Z

0

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;

answered Dec 8, 2020 at 10:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

5 Comments

procra Over a year ago

Wouldn't that give null values on days without sales?

Tim Biegeleisen Over a year ago

No, it wouldn't. COUNTing a NULL yields a count of zero.

procra Over a year ago

count(*) does appearently but does count(field_name) too?

Tim Biegeleisen Over a year ago

Please research the documentation for the COUNT function, in whatever database you are using.

procra Over a year ago

Huh it seems you're right. So I learned something here too

Gordon Linoff · Accepted Answer · 2020-12-08 12:02:41Z

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.

Collectives™ on Stack Overflow

How to add missing dates when calculating count on a table

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related