时间戳

首页 > 解决方案 > 填写日期查询中缺少的月份

sql - 填写日期查询中缺少的月份

问题描述

我正在使用 Microsoft SQL Server 2017。我正在尝试在数据查询中填充缺失的月份。

查询是：

SELECT Concat(Datename(mm, temp1.InvoiceDate), '-', Datepart(yy, Getdate())) AS 'Date' , temp1.[Partner], Count(temp1.issues) AS 'Total' 
FROM   temp1 
GROUP  BY temp1.InvoiceDate, temp1.[partner] 
ORDER  BY Datepart(mm, temp1.InvoiceDate), temp1.[partner];

不要介意getdate，因为我会一直处理实际的年份数据。

它向我展示了结果：

Date        Partner             Total
April-2020  Enterprise1, S.A.   1
May-2020    Enterprise2, S.A.   1
July-2020   Enterprise2, S.A.   2

这是正确的，但是在没有数据的实际月份（getdate 实际月份）之前，我也需要缺少月份。

就像是：

Date           Partner           Total
January-2020   N/A                0
February-2020  N/A                0
March-2020     N/A                0
April-2020     Enterprise1, S.A.  1
May-2020       Enterprise2, S.A.  1
June-2020      N/A                0
July-2020      Enterprise2, S.A.  2
August-2020    N/A                0
September-2020 N/A                0

另外，我不希望从实际显示的那几个月开始。

PS：我的开发环境中不能使用T-SQL代码（declare等）。

提前致谢。

标签： sqlsql-server

解决方案

我会使用递归 CTE 来生成年份的日期：

with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, t1.partner, count(t1.issues)
from dates d left join
     temp1 t1 
     ON t1.InvoiceDate >= d.yyyymm AND
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, t1.partner
order by d.yyyymm, t1.partner;

注意一些变化：

  • 这将返回日期。它不会像你一样格式化它。你可以随意格式化它。
  • 它返回0而不是“N/A”。 0似乎更合适。

以上内容实际上只适用于一个合作伙伴（因此它适用于问题中的示例）。我怀疑你想要所有合作伙伴的所有月份。如果是这样，请使用 across join生成行，然后使用left joinand group by：

with dates as (
      select datefromparts(year(getdate()), 1, 1) as yyyymm
      union all
      select dateadd(month, 1, yyyymm)
      from dates
      where yyyymm < getdate()
     )
select d.yyyymm, p.partner, count(t1.issues)
from dates d cross join
     (select distinct partner from temp1) p left join
     temp1 t1 
     on t1.partner = p.partner and
        t1.InvoiceDate >= d.yyyymm and
        t1.InvoiceDate < dateadd(month, 1, d.yyyymm)
group by d.yyyymm, p.partner
order by d.yyyymm, p.partner;

推荐阅读