在SQL中创建新变量困难

Question

我需要根据开始日期和结束日期创建一些新的年份变量(2015、2016、——、2022)。如果某人的起始日期年份为2017年，结束日期年份为2020年，那么该人值在2017、2018、2019和2020列中将为"Yes">

我使用了下面的代码，得到了附表。

select *
,case when startdate >= '2015-01-01 00:00:00.000' and enddate <= '2015-12-31 00:00:00.000' then 'yes' end as '2015'
,case when startdate >= '2016-01-01 00:00:00.000' and enddate <= '2016-12-31 00:00:00.000' then 'yes' end as '2016' 
,case when startdate >= '2017-01-01 00:00:00.000' and enddate <= '2017-12-31 00:00:00.000' then 'yes' end as '2017' 
,case when startdate >= '2018-01-01 00:00:00.000' and enddate <= '2018-12-31 00:00:00.000' then 'yes' end as '2018' 
,case when startdate >= '2019-01-01 00:00:00.000' and enddate <= '2019-12-31 00:00:00.000' then 'yes' end as '2019' 
,case when startdate >= '2020-01-01 00:00:00.000' and enddate <= '2020-12-31 00:00:00.000' then 'yes' end as '2020' 
,case when startdate >= '2021-01-01 00:00:00.000' and enddate <= '2021-12-31 00:00:00.000' then 'yes' end as '2021' 
,case when startdate >= '2022-01-01 00:00:00.000' and enddate <= '2022-12-31 00:00:00.000' then 'yes' end as '2022' 
from #have
order by ID

使用上面提到的代码，我得到了附带的表格。有些人的开始日期是2018年，结束日期是2020年。在那些年里，他们没有显示出"是"。

Answer 1

select *
,case when startdate < '20160101' and enddate > '20150101' then 'yes' end as "2015"
,case when startdate < '20170101' and enddate > '20160101' then 'yes' end as "2016"
,case when startdate < '20180101' and enddate > '20170101' then 'yes' end as "2017"
,case when startdate < '20190101' and enddate > '20180101' then 'yes' end as "2018"
,case when startdate < '20200101' and enddate > '20190101' then 'yes' end as "2019"
,case when startdate < '20210101' and enddate > '20200101' then 'yes' end as "2020"
,case when startdate < '20220101' and enddate > '20210101' then 'yes' end as "2021"
,case when startdate < '20230101' and enddate > '20220101' then 'yes' end as "2022"
from #have
order by ID

可能还有一种方法可以使用CROSS APPLY到表值构造器VALUES()子句，但我甚至不确定你使用的是什么数据库，所以我不会尝试猜测正确的语法。

Answer 2

我能想出解决办法。见下文

select *
,case when  2015 >= year(startdate) and 2015 <= year(enddate) then 'yes' end as '2015'
,case when  2016 >= year(startdate) and 2016 <= year(enddate) then 'yes' end as '2016'
,case when  2017 >= year(startdate) and 2017 <= year(enddate) then 'yes' end as '2017'
,case when  2018 >= year(startdate) and 2018 <= year(enddate) then 'yes' end as '2018'
,case when  2019 >= year(startdate) and 2019 <= year(enddate) then 'yes' end as '2019'
,case when  2020 >= year(startdate) and 2020 <= year(enddate) then 'yes' end as '2020'
,case when  2021 >= year(startdate) and 2021 <= year(enddate) then 'yes' end as '2021'
,case when  2022 >= year(startdate) and 2022 <= year(enddate) then 'yes' end as '2022'
from #Have
order by PID