Difficulty in creating new variable in SQL-CodePudding

I need like to create some new variables of years (2015, 2016, ---, 2022) based on start date and end date. If someone's year of start date is 2017 and year of end date is 2020 then for that person value in 2017, 2018, 2019, and 2020 columns will be 'Yes'

I used the following code and got the attached table.

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

Using the code mentioned above, I got the attached table. Someone's start date is in 2018 and end date is in 2020. They are not showing as 'Yes' in those years.

CodePudding user response：

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

There's probably also a way to do this using a CROSS APPLY to a table-value constructor VALUES() clause, but I'm not even sure what database you're using so I won't try guess at the right syntax just yet.

CodePudding user response：

I was able to figure out the solution. See below 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