主页 / dba / 问题 / 329497
Accepted
RaGe
Asked: 2023-07-20 22:04:58 +0800 CST

TSQL使用数字表计算2个不同的列

  • 772

我尝试使用数字表按月间隔计算 2 个不同的日期列：

数字表：

编号
0
1
2
3

原始表（示例）：

性别 订阅_日期 取消日期
男性 2023-01-01 2023-05-01
男性 2023-01-05 2023-01-08
男性 2023-01-05 2023-03-09
男性 2023-02-01 2023-04-08
女性 2023-01-05 2023-04-08
女性 2023-01-07 无效的

最终输出应该是这样的：

报告月 性别 订阅 取消
2023-01-01 男性 3 1
2023-01-01 女性 2 0
2023-02-01 男性 1 0
2023-02-01 女性 0 0
2023-03-01 男性 0 1
2023-03-01 女性 0 0
2023-04-01 男性 0 1
2023-04-01 女性 0 1
2023-05-01 男性 0 0
2023-05-01 女性 1 0

第一次尝试：

-- first day of the current month, one year ago
DECLARE @StartDate date = DATEADD(year,-1,DATEADD(month,datediff(month,0,getdate()),0)) 

-- Start from StartDate, showing results for Subscriptions for each month
SELECT
   @StartDate AS StartDate, Numbers.Num, DATEADD(month, Numbers.Num, @StartDate) AS ReportMonth
   ,S.Gender
   ,COUNT(S.SubscriptionDate) AS Subscriptions
FROM Numbers
JOIN
OriginatingTable AS S ON DATEADD(month, Numbers.Num, @StartDate) = DATEADD(MONTH, DATEDIFF(MONTH, 0, S.SubscriptionDate), 0)
WHERE (Numbers.Num <= DATEDIFF(month, @StartDate, GETDATE())) 
GROUP BY Numbers.Num, DATEADD(month, Numbers.Num, @StartDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, S.Subscription_Date), 0), S.Gender
ORDER BY Numbers.Num

这可行，但我无法整合第二列“取消”。

感谢您的帮助，

拉尔夫.

t-sql

2 个回答

  1. Best Answer

    定义您的驱动表：Numbers Cartesian join Genders 并为每一行计算计数作为标量子查询类似

    select n.StartDate, n.EndDate,g.gender,
 DATEADD(month, Numbers.Num, @StartDate) AS ReportMonth,
 (select count(*) from OriginatingTable o 
   where o.gender=g.gender
     and o.Subscription_Date between n.StartDate and n.EndDate) as SubscriptionsCount,
 (select count(*) from OriginatingTable o 
   where o.gender=g.gender
     and o.Cancel_Date between n.StartDate and n.EndDate) as CancellationsCount
from numbers n
cross join Genders g
    • 0

  2. 谢尔盖，

    谢谢你！

    稍微修改一下就可以了：

    DECLARE @StartDate date = DATEADD(year,-1,DATEADD(month,datediff(month,0,getdate()),0)) --first day of current month, one year ago
DECLARE @Genders table (Gender varchar(10));
INSERT INTO @Genders values('male'),('female');

SELECT DATEADD(month, N.Num, @StartDate) AS ReportMonth -- monthly Counter
        , (select count (Subscription_Date) from Originating_Table o
            where DATEADD(month,N.Num,@StartDate)= DATEADD(MONTH, DATEDIFF(MONTH,0,o.Subscription_Date),0)
            and o.Gender = g.Gender
          ) as Subscriptions
        , g.Gender
        ,
        (select count (Cancel_Date) from Originating_Table o
            where DATEADD(month,N.Num,@StartDate)= DATEADD(MONTH, DATEDIFF(MONTH,0,o.Cancel_Date),0)
            and o.Gender = g.Gender
          ) as Cancellations
    FROM Numbers N
    CROSS JOIN @Genders g
    WHERE N.Num < datediff(MONTH,@Startdate, GETDATE())+1 -- Report up to current month
    ORDER BY ReportMonth
    • 0

相关问题