CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-byrecursive-query
Cassandra Durell
Cassandra Durell

Reputation: 101

How can I get the count to display zero for months that have no records

I am pulling transactions that happen on an attribute (attribute ID 4205 in table 1235) by the date that a change happened to the attribute (found in the History table) and counting up the number of changes that occurred by month. So far I have 

SELECT TOP(100) PERCENT MONTH(H.transactiondate) AS Month, COUNT(*) AS Count 
FROM hsi.rmObjectInstance1235 AS O LEFT OUTER JOIN 
     hsi.rmObjectHistory AS H ON H.objectID = O.objectID 
WHERE H.attributeid = 4205) AND Year(H.transaction date) = '2020' 
GROUP BY MONTH(H.transactiondate)

And I get 

Month    Count 
---------------
1        9 
2        4 
3        11
4        14
5        1

I need to display a zero for months June - December instead of excluding those months.

Upvotes: 0

Views: 477

Answers (1)

GMB
GMB

Reputation: 222412

One option uses a recursive query to generate the dates, and then brings the original query with a left join:

with all_dates as (
    select cast('2020-01-01' as date) dt
    union all
    select dateadd(month, 1, dt) from all_dates where dt < '2020-12-01'
)
select
    month(d.dt) as month, 
    count(h.objectid) as cnt
from all_dates d
left join hsi.rmobjecthistory as h 
    on  h.attributeid = 4205
    and h.transaction_date >= d.dt
    and h.transaction_date < dateadd(month, 1, d.dt)
    and exists (select 1 from hsi.rmObjectInstance1235 o where o.objectID = h.objectID)
group by month(d.dt)

I am quite unclear about the intent of the table hsi.rmObjectInstance1235 in the query, as none of its column are used in the select and group by clauses; it it is meant to filter hsi.rmobjecthistory by objectID, then you can rewrite this as an exists condition, as shown in the above solution. Possibly, you might as well be able to just remove that part of the query.

Also, note that

  • top without order by does not really make sense

  • top (100) percent is a no op

As a consequence, I removed that row-limiting clause.

Upvotes: 2

Related Questions