CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Amita Shrivastava
Amita Shrivastava

Reputation: 1

How to include month names with count as 0 for monthly data count?

I am running the below query to fetch monthly data count for last year till current month.

SELECT dateName(month,tn.processstarttime) as reqMonth, count(ID) as requestCount, year(tn.processstarttime) as reqYear
FROM table A tn WHERE  year(tn.processstarttime) in (year(DATEADD(MONTH, 0, GETDATE())),year(DATEADD(MONTH, -12, GETDATE()))) 
AND tn.processstarttime>DATEADD(MONTH, -12, GETDATE()) 
GROUP BY dateName(month,tn.processstarttime),year(tn.processstarttime)
order by dateName(month,tn.processstarttime),year(tn.processstarttime)

But this query is not giving month names for which the data count is 0.

Please support to include months for which the data count is 0 with value as 0.

Thanks

Upvotes: 0

Views: 57

Answers (3)

Daniel Brughera
Daniel Brughera

Reputation: 1651

This answer can be similar to Michal Turczyn's, but there are a couple of substantial differences:

Do not pay much attention on the differences creating the first two CTEs, as different they look as irrelevant, simply matter of styles.

The important difference is in the third CTE and the way of filter your query, the name of your column (processstarttime) is giving a clue that it can be a very large table, so if you use where clauses using functions for the selected table columns, it will work, but your query won't be indexed and the performance can be a further issue

Not top relevant but also important is that It cover the "monthly data count for last year till current month" PO's requirement without hardcoding dates, it can be within a view or function that doesn't need to be modifiied year by year... 

WITH months AS (
        SELECT 1 AS MonthNum, DATENAME(Month,DATEFROMPARTS(1,1,1)) AS MonthName
        UNION ALL
        SELECT MonthNum + 1, DATENAME(Month,DATEFROMPARTS(1, MonthNum + 1, 1)) AS MonthName
        FROM months
        WHERE MonthNum <= 11
    ),
    years as (
        SELECT YEAR(GETDATE())-1 AS Year
        UNION ALL
        SELECT Year + 1
        FROM years 
        WHERE Year + 1 <= YEAR(GETDATE())
    ),
    dates as (
        SELECT Year, MonthNum, MonthName, DATEFROMPARTS(Year, MonthNum, 1) AS DateStart, DATEADD(MONTH, 1, DATEFROMPARTS(Year, MonthNum, 1)) AS DateEnd
        FROM years
        CROSS JOIN months
    )
    SELECT D.Year, D.MonthNum, D.MonthName, COUNT(ID) AS RequesCount
    FROM dates D
    LEFT JOIN  YourTable A ON A.ProcessStartTime >= DateStart AND A.ProcessStartTime < DateEnd
    WHERE DateStart < GETDATE()
    GROUP BY D.Year, D.MonthNum, D.MonthName
    ORDER BY Year, MonthNum

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

The standard way is to use calendar table with all years nad month required and then LEFT JOIN to it your result. When there will be no corresponding record in your table to some meonth, you will use COALESCE to obtain 0 for those months. See below query (I used CTEs to get calendar table, IMO the easiest way):

;with MonthNames as (
    select 1 MonthNo, 'January' MonthName
    union all
    select 2, 'February'
    union all
    select 3, 'March'
    union all
    select 4, 'April'
    union all
    select 5, 'May'
    union all
    select 6, 'June'
    union all
    select 7, 'July'
    union all
    select 8, 'August'
    union all
    select 9, 'September'
    union all
    select 10, 'October'
    union all
    select 11, 'November'
    union all
    select 12, 'December'
), Years as (
    select 2017 Year union all select 2018 union all select 2019
), CalendarTable as (
    select * from MonthNames cross join Years
)

select ct.MonthName,
       ct.Year,
       COALESCE(t.requestCount, 0) requestCount
from CalendarTable ct
left join (YOUR WHOLE SELECT) t
on t.Year = ct.Year and t.month = ct.MonthNo

Upvotes: 2

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Try this

SELECT months.month_name AS reqMonth, COUNT(ID) AS requestCount, YEAR(tn.processstarttime) AS reqYear
FROM A tn
    RIGHT JOIN (VALUES ('january'),('february'),('march'),('april'),
                       ('may'),('june'),('july'),('august'),('september'),
                       ('october'),('november'),('december')) as months(month_name) 
                ON DATENAME(month,tn.processstarttime) = months.month_name
                AND YEAR(tn.processstarttime) in (YEAR(DATEADD(MONTH, 0, GETDATE())),year(DATEADD(MONTH, -12, GETDATE()))) 
                AND tn.processstarttime>DATEADD(MONTH, -12, GETDATE()) 
GROUP BY months.month_name,YEAR(tn.processstarttime)
order by months.month_name,YEAR(tn.processstarttime)

In action here

Upvotes: 0

Related Questions