SQL Query to print month list of no records

Question

I have written a query to get all the records from ORDERS Table of Northwind DB,

select count(*) as COUN,month(OrderDate) as Mon
from orders 
where  Year(OrderDate) = '1996' 
group by month(OrderDate)

Here I want to print all the months which do not have any record. any suggestion will be helpful.

The best solution is to have a dates table. – Kermit May 16 '14 at 15:13 — Kermit, May 16 '14 at 15:13

score 0 · Answer 1 · answered May 16 '14 at 15:17

0

This should work:

select count(*) as COUN,month(OrderDate) as Mon
from orders 
where  Year(OrderDate) = '1996' 
AND NOT month(OrderDate) IN(SELECT month(OrderDate) from Orders WHERE Year(OrderDate) = '1996')
group by month(OrderDate)

answered May 16 '14 at 15:17

MDiesel

2,647
12
14

score 0 · Answer 2 · answered May 16 '14 at 15:18

You could to use a WITH-series to generate those months you are interested in:

WITH mons AS
  (
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    mons
    WHERE   num < 12
  )
SELECT COUNT(*) AS COUNT, num
  FROM mons
  LEFT JOIN orders ON MONTH(OrderDate) = num
  WHERE YEAR(OrderDate) = '1996'
  GROUP BY num;

score 0 · Answer 3 · answered May 16 '14 at 15:19

0

You can use a having clause to avoid querying the Orders table twice:

select count(*) as COUN,month(OrderDate) as Mon
from orders 
where  Year(OrderDate) = '1996' 
group by month(OrderDate)
having count(*) = 0

answered May 16 '14 at 15:19

HasaniH

8,232
6
41
59

score 0 · Answer 4 · edited May 23 '17 at 10:25

Based on this answer, you can try the below:

--Example Setup
CREATE TABLE FORDERS (OrderDate datetime)

INSERT INTO FORDERS (OrderDate)
    VALUES (GETDATE());

--Actual Query.
DECLARE @Year varchar(4) = 1996;

DECLARE @StartDate  datetime = @Year + '0101',
        @EndDate    datetime = @Year + '1231';


SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName, COUNT(OrderDate)
FROM    master.dbo.spt_values x
    LEFT JOIN FOrders ON x.number = MONTH(OrderDate)
        AND YEAR(OrderDate) = @Year
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)
GROUP BY x.number, MONTH(OrderDate)
HAVING COUNT(OrderDate) = 0;

SQL Query to print month list of no records

4 Answers4