How do I fetch data grouped by month and year including the period with no data?

Question

Relatively new to SQL and I am stumped on this little issue. This doesn't seem to be very difficult to do, but I just can't seem to figure it out.

I am trying to get a count of transactions from a table, but I can't seem to get sql to get me to show all of the months instead of only the months and the year that the transactions occured in.

Here is the query:

SELECT      YEAR(dbo.countproject.trans_date)   AS [TransYear]
        ,   MONTH (dbo.countproject.trans_date) AS [TransMonth]
        ,   COUNT(Id)                           AS TransNum 
FROM        dbo.countproject
WHERE       dbo.countproject.make_name  = 'Honda'
AND         dbo.countproject.model_name = 'Civic'
AND         dbo.countproject.type       = 'Sale'
AND         dbo.countproject.trans_type LIKE '%%EU'
AND         dbo.countproject.mfr        = '2000'
GROUP BY    YEAR(dbo.countproject.trans_date)
        ,   MONTH(dbo.countproject.trans_date)
ORDER BY    YEAR(dbo.countproject.trans_date)

The query returns the following result set:

| TransYear | TransMonth | TransNum |
|-----------|------------|----------|
|  2004     |     1      |     5    |
|  2004     |     3      |     1    |
|  2005     |     4      |     2    |

and so forth....

I am trying to get it to show all the months and years even if the value is NULL.

I tried creating a new table which will have the year and the month as columns to get it to join somehow, but I am lost.

Any help would be appreciated.

What field specifically is nullable? the `trans_date` field or something else? — Eric Petroelje, Apr 30 '12 at 20:11
http://stackoverflow.com/questions/1478951/tsql-generate-a-resultset-of-incrementing-dates — Ralf de Kleine, Apr 30 '12 at 20:12

score 3 · Accepted Answer · 2012-05-01T15:30:17.347

If you are using SQL Server 2005 or above, you could use Common Table Expressions (CTE) to get the desired result. Below example shows how you can fetch the results as you had described in the question.

Click here to view the demo in SQL Fiddle.

Description:

Create and insert statements create the table and populates with some sample data. I have created the table based on the query provided in the question.
The statement within the WITH clause is executing a recursive expression. In this case the SELECT above the UNION ALL fetches the minimum and maximum dates available in the table dbo.countproject
Once the minimum date is fetched, the second SELECT statement after the UNION ALL increments the date in 1 month intervals until the recursive expression reaches the maximum date available in the table.
The recursive CTE has produced all the available dates possible. This output is available in the table named alltransactions.
We have to join this CTE output alltransactions with the actual table countproject using LEFT OUTER JOIN since we want to show all years and months even if there are no transactions.
The tables alltransactions and countproject are joined on the year and month parts of the date. The query then applies the necessary filters in the WHERE clause and then groups the data by year and month before ordering it by year and month.
You can notice from the sample data that the earliest date in the table is 2004-07-01 and the latest date is 2005-12-01. Hence the output shows from year 2004 / month 07 till year 2005 / month 12.

Hope that helps.

Script:

CREATE TABLE dbo.countproject
(   
        id          INT         NOT NULL IDENTITY
    ,   trans_date  DATETIME    NOT NULL
    ,   make_name   VARCHAR(20) NOT NULL
    ,   model_name  VARCHAR(20) NOT NULL
    ,   type        VARCHAR(20) NOT NULL
    ,   trans_type  VARCHAR(20) NOT NULL
    ,   mfr         INT         NOT NULL
);

INSERT INTO dbo.countproject (trans_date, make_name, model_name, type, trans_type, mfr) VALUES
    ('1900-01-01', 'Honda',     'Civic',    'Sale', 'EU', 2000),
    ('1900-01-01', 'Toyota',    'Corolla',  'Sale', 'EU', 2000),
    ('2004-07-01', 'Nissan',    'Altima',   'Sale', 'EU', 2000),
    ('2005-12-01', 'Toyota',    'Camry',    'Sale', 'EU', 2000),
    ('2004-04-01', 'Ford',      'Focus',    'Sale', 'EU', 2000),
    ('2005-08-01', 'Honda',     'Civic',    'Sale', 'EU', 2000),
    ('2005-11-01', 'Toyota',    'Camry',    'Sale', 'EU', 2000),
    ('2004-08-01', 'Toyota',    'Corolla',  'Sale', 'EU', 2000),
    ('2005-12-01', 'Honda',     'Civic',    'Sale', 'EU', 2000),
    ('2004-07-01', 'Honda',     'Civic',    'Sale', 'EU', 2000),
    ('2004-11-01', 'Honda',     'Civic',    'Sale', 'EU', 2000),
    ('2005-08-01', 'Honda',     'Civic',    'Sale', 'EU', 2000);


;WITH alltransactions
AS
(
    SELECT      MIN(trans_date) AS continuousdate
            ,   MAX(trans_date) AS maximumdate
    FROM        dbo.countproject
    WHERE           trans_date <> '1900-01-01'
    UNION ALL 
    SELECT      DATEADD(MONTH, 1, continuousdate) AS continuousdate
            ,   maximumdate
    FROM        alltransactions
    WHERE       DATEADD(MONTH, 1, continuousdate) <= maximumdate
)
SELECT          YEAR(at.continuousdate)     AS [Year]
            ,   MONTH(at.continuousdate)    AS [Month]

            ,   COUNT(cp.trans_date)        AS [Count]
FROM            alltransactions at
LEFT OUTER JOIN countproject    cp
ON              YEAR(at.continuousdate)     = YEAR(cp.trans_date)
AND             MONTH(at.continuousdate)    = MONTH(cp.trans_date)
AND             cp.make_name                = 'Honda'
and             cp.model_name               = 'Civic'
and             cp.type                     = 'Sale'
and             cp.trans_type               LIKE '%EU'
and             cp.mfr                      = '2000'
GROUP BY        YEAR(at.continuousdate)
            ,   MONTH(at.continuousdate)
ORDER BY        [Year]
            ,   [Month];

Output:

Year   Month  Count
-----  ------ -----
2004      4     0
2004      5     0
2004      6     0
2004      7     1
2004      8     0
2004      9     0
2004     10     0
2004     11     1
2004     12     0
2005      1     0
2005      2     0
2005      3     0
2005      4     1
2005      5     0
2005      6     0
2005      7     0
2005      8     2
2005      9     0
2005     10     0
2005     11     0
2005     12     1

Thank you! This did the trick. To further complicate this...... Is there anyway I can cut out years 1900-1989 from the query as I have a list of 1356 rows. I think in the future I would also like to pivot the table somehow so that I can do all make names as rows and year of manufacture in columns. I am going to do some reading on that now. Once again thanks for your help! — user1366606, May 01 '12 at 15:23
yes my fault.. I realized I used the wrong field in the where close so it was not pulling any values. — user1366606, May 01 '12 at 15:28

score 0 · Answer 2 · answered Apr 30 '12 at 20:14

0

You have to use an LEFT or RIGHT OUTER JOIN!

Here is an easy sample: http://www.w3schools.com/sql/sql_join_left.asp

You should get it done by yourself.

Greetings

answered Apr 30 '12 at 20:14

MUG4N

19,377
11
56
83

score 0 · Answer 3 · edited Feb 16 '13 at 17:21

Alas, the SQL statement can only return the data in the table. If you want all months, you need either a table with the year/month combinations you are intererested in or, preferably, a calendar table with all days and information about them.

With a calendar table, your query have a from clause that looked like:

from 
(
   select distinct year(date) as yr, month(date) as mon
   from calendar c
   where date between <earliest> and <latest>
) c 
left outer join CountTable ct
  on c.yr = year(ct.trans_date) 
  and c.mon = month(ct.trans_date)

How do I fetch data grouped by month and year including the period with no data?

3 Answers3