Query last 6 month of data , display by month name

Question

I want to get the last 6 month count records, and show it per month. I need to show 0 in case that month doesn't have any records.

My query is

DECLARE @Date1 DATETIME, @Date2 DATETIME
SET @Date1 = GETDATE()
SET @Date2 = DateAdd(month, -6, Convert(CHAR(10), @Date1, 121))

SELECT 
    DATENAME(MONTH,IssueDate) [Month Name], COUNT(1) [Count]
FROM 
    CompetitiveProcess
WHERE 
    IssueDate BETWEEN @Date2 AND @Date1
GROUP BY 
    YEAR(IssueDate), MONTH(IssueDate), DATENAME(MONTH, IssueDate)

But is not showing the month that doesn't have any rows. Can anyone help me with this?

Create a table (temp works) that has 1 row for every month you want (you could do a quick loop to populate this dynamically). Start at that table in your query and left join it to the count query. Should be tons of examples on Stack Overflow if you search for it — Twelfth, Jul 29 '14 at 00:21
http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 I think that answer will work for you — Twelfth, Jul 29 '14 at 00:22
Thanks for you quick reply , i got the answer from this thread http://stackoverflow.com/questions/16968809/sql-query-get-all-data-from-jan-to-current-month-even-if-there-are-no-records?rq=1 — Andy Pham, Jul 29 '14 at 00:42

score 1 · Answer 1 · answered Jul 29 '14 at 02:34

Just use a recursive CTE to generate the dates and then left outer join to get the data:

with dates as (
      select cast(getdate() - day(getdate()) + 1 as date) as monthstart,
             cast(dateadd(month, 1, getdate()) - day(getdate()) as date) as monthend,

             0 as lev
      union all
      select dateadd(month, -1, monthstart), 
             dateadd(day, -1, monthstart),
             lev + 1
      from dates
      where lev < 6
     )
SELECT DATENAME(dates.monthstart, IssueDate) as [Month Name],
       COUNT(cp.issuedate) as [Count]
FROM dates left outer join
     CompetitiveProcess cp
     on IssueDate BETWEEN dates.monthstart and dates.monthend
GROUP BY YEAR(dates.monthstart), MONTH(dates.monthstart), DATENAME(MONTH, dates.monthstart);

Query last 6 month of data , display by month name

1 Answers1