主页 / dba / 问题 / 278830
Accepted
TheDemonLord
Asked: 2020-10-29 13:31:27 +0800 CST

使用 NULL 的开始日期和结束日期计算分组记录

  • 772

这是我的问题，源数据库是 Azure MSSQL - 我有一张表，列出了所有已售出的订阅。相关列是：

账号、订阅组、购买日期、取消日期

取消日期包含 NULL 值（对于尚未取消的订阅）。一个帐户可能有多个订阅，跨越多个订阅组。

样本数据为：

帐号 | 订阅群 | 购买日期 | 取消日期 |

1 | 一个 | 2013 年 2 月 4 日 | 2014 年 2 月 4 日 |
1 | 一个 | 2013 年 2 月 4 日 | 空 |
1 | 乙| 2013 年 2 月 4 日 | 空 |
2 | C | 2018 年 5 月 7 日 | 2020 年 5 月 8 日 |
3 | C | 20/3/2020 | 空 |
3 | C | 2020 年 4 月 8 日 | 空 |

等等。

我正在计算每个客户和每个月的订阅组数量 - 所以输出看起来像这样：

客户 | A 的计数 | B 的计数 | C 计数 | 月/年 |
1 | 1 | 1 | 0 | 2020 年 10 月 |
3 | 0 | 0 | 2 | 2020 年 10 月 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

我可以为给定月份编写查询：

select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END ) 作为 "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t .[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
按 t.[Account Number] 分组， [月/年]

但是，我希望有人可以帮助我修改上述内容，以便它将运行所有月份（基于购买日期的 MIN 和取消日期的 MAX）

sql-server azure-sql-database

1 个回答

  1. Best Answer

    我最终使用存储过程执行此操作，创建了一个临时表：

    DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')      
         ,@EndDate  = (select max([Cancellation date]) from Table);


;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

    然后通过该表执行循环并填充输出表：

    Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) > 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable
    • 0

相关问题