sql - 计算特定日期\日期在两个日期之间出现的次数
问题描述
我有一份每年 4 月 1 日更改的合同价格。合同的开始日期和结束日期可能会跨越多个价格变化,最初我想计算合同开始日期和合同结束日期之间的变化。
这是我想要得到的结果集:
2017 年 1 月 4 日至 2018 年 3 月 31 日 = 0
2018 年 3 月 31 日至 2018 年 1 月 4 日 = 1
2015 年 1 月 1 日至 2018 年 12 月 31 日 = 4
目前我的查询看起来像这样(手动可怕):
SET DATEFORMAT DMY;
SELECT
Contract_ID,
(Period_1 + Period_2 + Period_3 + Period_4 + Period_5 + Period_6 + Period_7
+ Period_8 + Period_9
) AS Split_Count
FROM
(
SELECT
C.Contract_ID,
CASE
WHEN ('01/04/2011'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_1,
CASE
WHEN ('01/04/2012'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_2,
CASE
WHEN ('01/04/2013'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_3,
CASE
WHEN ('01/04/2014'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_4,
CASE
WHEN ('01/04/2015'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_5,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_6,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_7,
CASE
WHEN ('01/04/2017'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_8,
CASE
WHEN ('01/04/2018'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_9
FROM Contract C
这样做的最佳方法是什么?
解决方案
您可以使用外部应用执行类似的操作来加入您的周期表
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
您可以旋转此数据以查看单行的期间计数。
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
或者只是获得合同的总金额。
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
推荐阅读
- r - 在系统中没有 Outlook 应用程序的 R 中发送电子邮件
- java - 我的 Java 程序无法识别 /usr/local 和子文件夹
- sqlite - 托管在 heroku 上的后端 Flask 服务器重置 sqlite 数据库。?
- ios - 显示的 react-native 页面元素在 unity3d 中反转
- node.js - req.users undefined react express
- laravel - 即使我已经将其移除,仍然可以检测到 livewire 组件和刀片
- javascript - ReferenceError:从window.open()刷新应用程序后未定义cordova
- android - LiveData 数据更改后 UI 不会更新
- python - 无法在 MacOS 上使用 pip
- java - 将 ImageButtons 附加到 OnClickListener 的位图