时间戳

首页 > 解决方案 > How can I get the count to display zero for months that have no records

sql - 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.

标签： sqlsql-servertsqlgroup-byrecursive-query

解决方案

一种选择使用递归查询来生成日期，然后将原始查询与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)

我很不清楚hsi.rmObjectInstance1235查询中表的意图，因为它的列都没有在selectandgroup by子句中使用；它是用来过滤hsi.rmobjecthistory的objectID，那么你可以把它重写为exists条件，如上面的解决方案所示。可能，您也可以只删除查询的那一部分。

另外，请注意

  • top没有order by真的没有意义

  • top (100) percent是无操作

因此，我删除了该行限制子句。

推荐阅读