我的订阅可以持续 1 到 4 个财政季度(也可以看作是四个日期范围(的任何地方(就我的输出而言(
我正在使用亚利桑那州财政季度:
- Q1:7月1日至9月30日
- 第二季度:10月1日至12月31 日
- 第 3 季度:1 月 1 日至 3 月 31 日
- 第四季度:4月1日至6月30日
我需要弄清楚的是,根据订阅开始和结束日期,每个季度有多少订阅处于活动状态。
例如,使用 (YYYY-MM-DD(,我有:
- 从 2016-07-06 开始到 2017-02-22 结束的订阅 我应该能够看到我在第 1 季度、第 2 季度和第 3 季度有一个有效的订阅。
- 另一个从 2016-10-18 开始到 2016-10-24 结束的订阅只会被视为在第二季度处于活动状态 最后,从
- 2016-09-28 开始但没有结束日期的订阅将被视为在 Q1、Q2、Q3 和 Q4 处于活动状态(因此,无论从哪个开始季度一直到 Q4(
下面是我当前的SQL Server脚本,这里是SQL Fiddle上的:
WITH SubscriptionInfo AS
(
SELECT
[Subscriptions].[Customer_Id]
,[DistributorTypes].[Name] AS [Distributor Type]
,[Customers].Zip_Id
,[Subscriptions].[UnsubscribeReason_Id]
,[Subscriptions].[Id] AS [Subscription ID]
,CONVERT(DATE, [Subscriptions].[StartDate]) AS [Subscription Start Date]
,CONVERT(DATE, [Subscriptions].[EndDate]) AS [Subscription End Date]
,[PriorityLevels].PriorityLevel AS [Priority Level]
,CONVERT(DATE, [SubscriptionPriorityLevels].StartDate) AS [Priority Level Start Date]
,CONVERT(DATE, [SubscriptionPriorityLevels].EndDate) AS [Priority Level End Date]
,[FundingSources].[Name] AS [Funding Source]
,CONVERT(DATE, [SubscriptionFundingSources].StartDate) AS [SubscriptionFundingSources Start Date]
,CONVERT(DATE, [SubscriptionFundingSources].EndDate) AS [SubscriptionFundingSources End Date]
FROM [Subscriptions]
LEFT JOIN [SubscriptionPriorityLevels]
ON [SubscriptionPriorityLevels].Subscription_Id = Subscriptions.Id
LEFT JOIN [PriorityLevels]
ON [PriorityLevels].Id = SubscriptionPriorityLevels.PriorityLevel_Id
LEFT JOIN [SubscriptionFundingSources]
ON [SubscriptionFundingSources].Subscription_Id = Subscriptions.Id
LEFT JOIN [FundingSources]
ON [FundingSources].Id = [SubscriptionFundingSources].FundingSource_Id
LEFT JOIN [Customers]
ON [Customers].Id = [Subscriptions].Customer_Id
LEFT JOIN [DistributorTypes]
ON [DistributorTypes].Id = Customers.DistributorType_Id
WHERE
([Subscriptions].StartDate >= '2016-07-01') -- Dummy dates, would later be parameters
AND ([Subscriptions].EndDate <= '2017-06-30'
OR [Subscriptions].EndDate IS NULL)
AND ([PriorityLevels].PriorityLevel IN (2, 3)) -- Only care about these two levels
AND ([Customers].DistributorType_Id = 1) -- Distributor Type: Number One Distrubition
AND ([SubscriptionFundingSources].FundingSource_Id = 2) -- Funding Source: First Bank
)
SELECT
[SubscriptionInfo].Customer_Id
,[SubscriptionInfo].[Subscription ID]
,MAX([SubscriptionInfo].[Priority Level]) AS [Highest Priority Level]
,CASE -- Determine which fiscal quarter each Subscription Start Date belongs to
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (7, 8, 9) THEN 1 -- July, August, September
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (10, 11, 12) THEN 2 -- October, November, December
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (1, 2, 3) THEN 3 -- January, Feburary, March
ELSE 4 -- April, May, June
END AS [Fiscal Quarter Start Date]
,CASE -- Determine which fiscal quarter each Subscription Start Date belongs to
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (7, 8, 9) THEN 1 -- July, August, September
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (10, 11, 12) THEN 2 -- October, November, December
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (1, 2, 3) THEN 3 -- January, Feburary, March
ELSE 4 -- April, May, June
END AS [Fiscal Quarter End Date]
FROM [SubscriptionInfo]
GROUP BY
[SubscriptionInfo].Customer_Id
,[SubscriptionInfo].[Subscription ID]
,CASE -- Group Subscription Start Date's into Fiscal Quarters
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (7, 8, 9) THEN 1 -- July, August, September
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (10, 11, 12) THEN 2 -- October, November, December
WHEN MONTH([SubscriptionInfo].[Subscription Start Date]) IN (1, 2, 3) THEN 3 -- January, Feburary, March
ELSE 4 -- April, May, June
END
,CASE -- Group Subscription End Date's into Fiscal Quarters
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (7, 8, 9) THEN 1 -- July, August, September
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (10, 11, 12) THEN 2 -- October, November, December
WHEN MONTH([SubscriptionInfo].[Subscription End Date]) IN (1, 2, 3) THEN 3 -- January, Feburary, March
ELSE 4 -- April, May, June
END
ORDER BY
[SubscriptionInfo].Customer_Id
到目前为止,我能够确定订阅在哪个财政季度开始以及哪个财政季度结束。
我希望能够计算每个季度有多少订阅处于活动状态。
期望输出:
| FirstQuarter | SecondQuarter | ThirdQuarter | FourthQuarter |
|--------------|---------------|--------------|---------------|
| 2 | 1 | 3 | 3 |
一旦您按照朗格的建议有了描述季度开始和结束日期@Sean表格,您只需要将订阅的开始和结束日期与季度的开始和结束日期进行比较,然后将它们计算在重叠的地方。 基本上,如果 A.Start相关内容
最新更新
- react hook是如何工作的?为什么useState跟踪的值在重新呈现时没有重置?
- 如何在颤振中自动点击按钮
- 如何将具有公共文件夹的react vite应用程序部署到我的域上的子文件夹?
- S3FS连接器Pyspark 3.2, Hadoop 3.3.1挂在spark.read.load()
- Flutter在不更改上下文区域设置的情况下在小部件内获取语言字符串
- fluentd: ignore_repeated_log_interval and ignore_same_log_in
- Psycopg2 无法适应间歇性发生的类型"元组"问题
- 从terraform创建发布模式定义时出错
- Pine脚本代码为策略给出错误.入口函数
- 如何从javascript中的API调用返回值给React组件
- Oracle检查巨大的插入进度
- 输入2位数时程序不能正常工作
- Python解析嵌套的JSON文件并取出所需的属性
- MongooseError:Model.create()不再接受回调如何使用异步等待
- 如何将bot/命令限制到某个通道?
- SwiftUi调用中的额外参数当调用主视图中@EnvironmentObject的两个视图时
- 无法从反应-选择下拉Robotframework中选择选项
- Intel Intrinsic: Load interleaved float32
- 使用base64.解码或base64.RawStdEncoding解码base64字符串在Go &g
- 如何使用Angular加载HTML附加div中的动态数据
- RTL文本在react-native项目中没有正确对齐
- 如何在类型语法中使用值
- 在离子反应项目中加入手电筒
- 模板中的Angular条件
- Unity3D在比较两个字符串时出现stackoverflow错误
- c-为什么使用多个线程会导致执行速度减慢
- 从r中的数据名列表中合并多个数据名的循环
- Rails新命令给出错误
- WordPress表单提交问题
- 如何使用pack创建左、右和中心框架?
热门标签:
javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium