Zero count for non existing group item category

Question

I need to find a list of three products and their quantities grouped by date, for the last five days. For this i used the TSQL below.

SELECT CONVERT(VARCHAR(10),WM_data,103) as Date, 
       Product = CASE 
                   WHEN WM_Product = 1 THEN 'Product A' 
                   WHEN WM_Product = 2 THEN 'Product B' 
                   ELSE 'Product C' 
                 END, 
       WM_qtd as Quantity 
  FROM Tb_InfoProduct 
GROUP BY CONVERT(VARCHAR(10),WM_data,103), WM_Product
  HAVING CONVERT(VARCHAR(10),WM_data,103) > convert(VARCHAR(10),dateadd(dd, -5, getdate()),103) 
ORDER BY CONVERT(VARCHAR(10),WM_data,103), WM_Product

as a result i receive:

Date          Product     Quantity
-------------------------------------------------
16/02/2012    Product A    132
16/02/2012    Product C     10
17/02/2012    Product A    163
17/02/2012    Product B     61
17/02/2012    Product C     58
18/02/2012    Product A      7
18/02/2012    Product B      2

But I need:

Date     Product     Quantity
-------------------------------------------------
16/02/2012   Product A   132
16/02/2012   Product B     0  -- This
16/02/2012   Product C    10
17/02/2012   Product A   163
17/02/2012   Product B    61
17/02/2012   Product C    58
18/02/2012   Product A     7
18/02/2012   Product B     2
18/02/2012   Product C     0   -- This

How can I achieve this?

You need to have (or generate) a list of dates, that you then OUTER join to on the date values. — OMG Ponies, Feb 26 '12 at 02:58
As an aside, you should do the filtering in the `WHERE` clause (using date objects not strings) — Leigh, Feb 26 '12 at 04:42

score 1 · Answer 1 · edited May 23 '17 at 10:34

First of all, there are some issues with your query:

Your SELECT clause contains a column that is neither in GROUP BY nor aggregated – WM_qtd. You meant SUM(WM_qtd), didn't you?
In general, dates formatted with CONVERT(103) cannot be meaningfully compared nor sorted. I would advise you either to use a different format (like 112) or to use a different method of removing the time part at all and apply the formatting later on (just in the SELECT clause or maybe not in SQL at all).
You are filtering the source dataset with the HAVING clause, which is inefficient, because all the data would have to be pulled, grouped, and only then filtered. In this particular case it would be better to filter the data with the WHERE clause.

Now as for your question... The result set you are after is basically the cross-product of all the relevant dates and all the relevant products (oops, sorry for the pun). It just includes some addition information, like Quantity.

So you need to obtain the two lists, dates and products, cross-join them, then you can outer-join the resulting set to Tb_InfoProduct and group by date and product to obtain the numbers – same as you are already doing in your query, only the date and product columns your GROUP BY and SELECT clauses will be referencing must be those in the respective derived lists, not the ones in the Tb_InfoProduct table.

For the purpose of my answer I'm assuming that the subset of Tb_InfoProduct you are requesting contains all the dates and all the products that must be present in the output, so, to obtain the two lists, I'm merely selecting distinct dates and distinct products from the Tb_InfoProduct subset. Here's an approximate solution in its entirety:

SELECT
  Date = CONVERT(VARCHAR(10), d.Date, 103),
  Product = CASE p.WM_Product
    WHEN 1 THEN 'Product A'
    WHEN 2 THEN 'Product B'
    ELSE        'Product C'
  END,
  Quantity = SUM(WM_qtd)
FROM (
  SELECT DISTINCT Date = DATEADD(DAY, DATEDIFF(DAY, 0, WM_data), 0)
  FROM Tb_InfoProduct
  WHERE WM_data >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 4, 0)
) d
CROSS JOIN (
  SELECT DISTINCT WM_Product
  FROM Tb_InfoProduct
  WHERE WM_data >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 4, 0)
) p
LEFT JOIN (
  SELECT
    Date = DATEADD(DAY, DATEDIFF(DAY, 0, WM_data), 0),
    WM_Product
  FROM Tb_InfoProduct
  WHERE WM_data >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 4, 0)
) i ON d.Date = i.Date AND p.WM_Product = i.WM_Product
GROUP BY
  d.Date,
  p.WM_Product
ORDER BY
  d.Date,
  p.WM_Product

If the requested subset may omit some dates and/or products which you would prefer to include in the output, you should build the list(s) differently. For instance, you could use a Products referencing table for the list of products, if you have one, of course. As for the dates, you might need to generate the list of dates for the given range, as suggested by @OMG Ponies (and this question might help you with that: Get a list of dates between two dates using a function).

score 0 · Answer 2 · edited Mar 27 '12 at 15:06

if using sql 2008:

SELECT table1.date, table2.productid, isnull(table3.total,0) total FROM
--get the last 5 days
(
select distinct date from mytable1 where date > getdate() - 5
) table1
--get the 3 products
CROSS JOIN
(
select productid from product where productid in (1,2,3)
) table2
OUTER APPLY (SELECT SUM(1) Total from YourTable 
    where yourtable.date = table1.date 
    and yourtable.productid = table2.productid) Table3

Zero count for non existing group item category

2 Answers2