1

Suppose I have a table called “products”:

Productive product-name   product-quantity  created-date
111            ABC          10               1/10/2018
222            XYZ          20               1/10/2018
333            PQR          30               1/11/2018
444            MNC          40               1/15/2018
555            DEF          50               1/11/2018

If I run this query:

Select convert(date,createddate,101), count(product-quantity) from products
Where created-date > 1/9/2018 and created-date < 1/16/1018
group by convert(date,createddate,101)

Which will give me output such as

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/15/2018            40

I want to achieve:

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/12/2018            00
1/13/2018            00
1/14/2018            00
1/15/2018            40

How can I do it?

  • 4
    You need a calendar table or generate a series of dates. – McNets Feb 2 '18 at 14:17
  • 1
    An internet search of "calendar table" will return many scripts that show how to create one you can leverage for this and other purposes. – Dan Guzman Feb 2 '18 at 14:21
  • 2
    This one is real handy, and removes the chore of using a tally table or something similar every time you need to use it. Just create it once and be done. It also allows you to skip a lot of date functions which could help performance over a lot of rows. Aaron Bertrand date dimension / calendar table – scsimon Feb 2 '18 at 14:31
0

A basic implementation of a Calendar table.

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, DAY(@date), DATEPART(weekday, @date), DATENAME(weekday, @date));
    SET @date = DATEADD(day, 1, @date);
END
GO
31 rows affected
CREATE TABLE products(Productive int, Name char(3), Qty int, Created date);
INSERT INTO products VALUES
(111, 'ABC', 10, '1/10/2018'),
(222, 'XYZ', 20, '1/10/2018'),
(333, 'PQR', 30, '1/11/2018'),
(444, 'MNC', 40, '1/15/2018'),
(555, 'DEF', 50, '1/11/2018');
GO
5 rows affected

Notice I've used a LEFT JOIN to get all records from calendar table and only those records that match on products table.

SELECT     c.cDate AS Created, COALESCE(Qty, 0) AS Qty
FROM       Calendar c
LEFT JOIN  (SELECT   CONVERT(date, Created, 101) AS [Created],
                     COUNT(Qty) AS [Qty]
            FROM     products
            GROUP BY CONVERT(date, Created, 101)) p
ON         p.Created = c.cDate
WHERE      c.cDate >= (SELECT MIN(Created) FROM products)
AND        c.cDate <= (SELECT MAX(Created) FROM products);
GO
Created             | Qty
:------------------ | --:
10/01/2018 00:00:00 |   2
11/01/2018 00:00:00 |   2
12/01/2018 00:00:00 |   0
13/01/2018 00:00:00 |   0
14/01/2018 00:00:00 |   0
15/01/2018 00:00:00 |   1

dbfiddle here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.