从日期字段上的组动态创建缺少的月份 [英] Create missing months dynamically from group on date field

问题描述

在下面的 SQL 中，我尝试插入行以填充结果中缺失的月份.由于发布了SQL select, pad with chronological missing months

In the following SQL i'm trying to insert rows to fill in the missing months in the results. The solution is very close thanks to post SQL select, pad with chronological missing months

但是此代码运行 gr8 但仍然缺少几个月，问题是如何加入/联合临时表

But yet this code runs gr8 but still have missing months, issue is how to join/union the temp table

DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()), @EndDate DATETIME = getdate(), @DATE DATETIME

DECLARE @TEMP AS TABLE (MeterReadDate datetime)

SET @DATE = @StartDate

WHILE @DATE <= @EndDate
BEGIN
     INSERT INTO @TEMP VALUES ( @DATE)
    SET @DATE = DATEADD(MONTH,1,@DATE)
END



SELECT convert(char(7), t.MeterReadDate, 121),count(*)

  FROM @TEMP m left join
     [PremiseMeterReadProviders] t
     on convert(char(7), t.MeterReadDate, 121) = convert(char(7), m.MeterReadDate, 121)

  where (t.MeterReadDate > dateadd(m,-12,getdate()))
  group by  convert(char(7), t.MeterReadDate, 121)
  order by  convert(char(7), t.MeterReadDate, 121)

推荐答案

我认为您需要重新构建查询如下

I think you need to reconstruct the query as below

DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()), @EndDate DATETIME = getdate(), @DATE DATETIME

DECLARE @TEMP AS TABLE (MeterReadDate datetime)

SET @DATE = @StartDate

WHILE @DATE <= @EndDate
BEGIN
     INSERT INTO @TEMP VALUES ( @DATE)
    SET @DATE = DATEADD(MONTH,1,@DATE)
END



SELECT convert(char(7), m.MeterReadDate, 121),count(*)

  FROM @TEMP m left join
     [Announcement] t
     on convert(char(7), t.ExpiryDate, 121) = convert(char(7), m.MeterReadDate, 121)

  WHERE (t.ExpiryDate IS NULL OR t.ExpiryDate > dateadd(m,-12,getdate()))
  group by  convert(char(7), m.MeterReadDate, 121)
  order by  convert(char(7), m.MeterReadDate, 121)

在 where 条件中，我添加了 t.ExpiryDate IS NULL，因为对于缺少的月份，这将为 null.

In the where condition, I added t.ExpiryDate IS NULL, because this will be null for the missing month.

这篇关于从日期字段上的组动态创建缺少的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！