Showing month value when no data exists SQL Server

Question

I have a SQL Server 2012 table that holds session per user and I need to find out monthly total of minutes for activity and if there are no results for the month then to show "0". Table below:

User   SessionStart              SessionEnd
1      2014-03-01 08:00:00.000   2014-03-01 08:10:00.000
1      2014-03-15 09:00:00.000   2014-03-15 09:30:00.000
1      2014-05-01 04:00:00.000   2014-05-01 04:50:00.000
1      2014-06-01 02:00:00.000   2014-06-01 02:05:00.000
1      2014-07-01 09:00:00.000   2014-07-01 10:30:00.000
1      2014-09-01 01:00:00.000   2014-09-01 01:07:00.000
1      2014-12-05 08:00:00.000   2014-12-05 08:10:00.000
2      2014-01-01 01:01:00.000   2014-01-01 01:11:00.000
1      2015-03-01 08:00:00.000   2015-03-01 08:10:00.000
1      2015-05-01 04:00:00.000   2015-05-01 04:50:00.000
1      2015-06-01 02:00:00.000   2015-06-01 02:05:00.000
...    ...                       ...

What I'm ending up with is:

User   Month   Year   Minutes 
1      3       2014   40
1      5       2014   50  
1      6       2014   5
1      7       2014   90
1      9       2014   7
1      12      2014   10
1      3       2015   10
1      5       2015   50
1      6       2015   5

What I'd like to get:

User   Month   Year   Minutes 
1      1       2014   0
1      2       2014   0
1      3       2014   40
1      4       2014   0
1      5       2014   50  
1      6       2014   5
1      7       2014   90
1      8       2014   0
1      9       2014   7
1      10      2014   0
1      11      2014   0
1      12      2014   10
1      1       2015   0
1      1       2015   0
1      3       2015   10
1      4       2015   0
1      5       2015   50
1      6       2015   5

I've tried using:

SELECT User
      ,MONTH(SessionStart)
      ,YEAR(SessionStart)
      ,SUM(DATEDIFF(minute, SessionStart, SessionEnd)) AS Minutes
FROM SessionTable
WHERE User = 1 AND YEAR(SessionStart) >= 2014
GROUP BY SessionStart, User

I have a table called "Months" that holds the number of the month (Month) and name of the month (MonthName) and have tried performing a left outer join on this table and have also tried using case selects. I've searched on this issue on Stackoverflow and see somewhat similar issues, but not one that involves using the date. Struggling to think today...

Answer 1

You need to have a calendar table which is generally a list of predefined dates with in some range..

You need to have months table for your need..which can be generated using numbers table ..

This is a Pseudo code only:

create table months
(
monthdate datetime
)

insert into monthdate
select dateadd(day,n,getdate()-10000)
from numbers
where n<=10000

Now you have months table ,all you need to do is a left join

select *
from monthstable mt
join yourtable yt
on yt.date=mt.date

This way you will preserve all dates which you dont have as well

** some good links :**
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/

http://www.sqlservercentral.com/articles/T-SQL/62867/

Answer 2

You're on the right track with your months table. Could you post your example that's not working using the left outer join? If you start with months and left outer join to your SessionTable you should be fine.

Something like...

SELECT User
      ,MONTH(m.month)
      ,YEAR(m.year)
      ,SUM(DATEDIFF(minute, s.SessionStart, s.SessionEnd)) AS Minutes
FROM Months m
  LEFT OUTER JOIN SessionTable s
    ON m.year = s.year
    AND m.month = s.month
    AND s.User = 1 
WHERE YEAR(m.year) >= 2014
GROUP BY s.SessionStart, s.User

NOTE:

• Don't filter your sessionTable data in the where clause when you're using month as the primary data source - filter it in the LEFT OUTER JOIN. If you put it in the WHERE, you're turning things into an INNER JOIN.
• You want to use your months table when returning your data for the month/year info

That work?

Answer 3

You can generate all the rows with a cross join and then use left join to bring in your summaries. Assuming that some row exists for each month, you can get this information from the session table itself:

SELECT u.User, yyyymm.mm, yyyymm.yy
       COALESCE(SUM(DATEDIFF(minute, s.SessionStart, s.SessionEnd)), 0) AS Minutes
FROM (SELECT 1 as user) u CROSS JOIN
     (SELECT DISTINCT YEAR(SessionStart) as yyyy, MONTH(SessionStart) as mm
      FROM SessionTable
      WHERE SessionStart >= '2014-01-01'
     ) yyyymm LEFT JOIN
     SessionStart ss
     ON u.user = ss.user AND
        YEAR(SessionStart) = yyyymm.yy AND MONTH(SessionStart) = yyyymm.mm
GROUP BY u.User, yyyymm.yyyy, yyyymm.mm;