I have a query that looks like this:
SELECT DISTINCT
p.person_ID
,p.Last_Name
,ISNULL(p.Middle_Initial, '') AS Middle
,p.First_Name
,sh.Status_from_date
,sh.Status_thru_date
--(a)
FROM
person p
INNER JOIN
Person_Facilities f ON p.Person_ID = f.Person_ID
LEFT OUTER JOIN
rv_person_status_hist sh ON p.person_ID = sh.person_ID
ORDER BY
Last_Name
The returned data looks like this sort of thing (ignore the 2017 columns for now):
Person_id Last_Name Middle First_Name Status_from_date Status_thru_date 2017 2017_Male 2017_Female 2018 2018_Male 2018_Female 2019 2019_Male 2019_Female
1000 Lamb Little Mary 2015-07-01 00:00 2017-06-30 00:00 1 0 1 0 0 0 1 0 1
1000 Lamb Little Mary 2015-11-01 00:00 2017-03-30 00:00 1 0 1 0 0 0 1 0 1
with the date format being yyyy-mm-dd.
How do I add a column, say [2017], and put a 1 for if status_from_date to status_thru_date includes 2017, or a 0 if not?
I wanted to add the following at the --(a) in the query:
,(case when ((sh.status_from_date is null or sh.status_from_date <= '2017-01-01') and --------
(sh.status_thru_date is null or sh.status_thru_date >= '2017-12-31'))
or
((f.status_from_date is null or f.status_from_date <= '2017-01-01') and --------
(f.status_thru_date is null or f.status_thru_date >= '2017-12-31')) or
(
(datepart(year, sh.status_from_date)='2017') or
(datepart(year, sh.status_thru_date)='2017') or
(datepart(year, f.status_from_date)='2017') or
(datepart(year, f.status_from_date)='2017')
)
then 1 else 0
end) as [2017]
,(case when ((
(
(sh.status_from_date is null or sh.status_from_date <= '2017-01-01') and
(sh.status_thru_date is null or sh.status_thru_date >= '2017-12-31')
)
or
(
(f.status_from_date is null or f.status_from_date <= '2017-01-01') and
(f.status_thru_date is null or f.status_thru_date >= '2017-12-31')
)
or
(
(datepart(year, sh.status_from_date)='2017') or
(datepart(year, sh.status_thru_date)='2017') or
(datepart(year, f.status_from_date)='2017') or
(datepart(year, f.status_from_date)='2017')
)
)and
p.Sex='M'
)
then 1 else 0
end) as [2017_Male]
,(case when ((
(
(sh.status_from_date is null or sh.status_from_date <= '2017-01-01') and
(sh.status_thru_date is null or sh.status_thru_date >= '2017-12-31')
)
or
(
(f.status_from_date is null or f.status_from_date <= '2017-01-01') and
(f.status_thru_date is null or f.status_thru_date >= '2017-12-31')
)
or
(
(datepart(year, sh.status_from_date)='2017') or
(datepart(year, sh.status_thru_date)='2017') or
(datepart(year, f.status_from_date)='2017') or
(datepart(year, f.status_from_date)='2017')
)
)and
p.Sex='F'
)
then 1 else 0
end) as [2017_Female]-------------------------------------------------------------------
...
there's ones for 2018 and 2019 as well that are almost identical except year.
This doesn't seem to be working, though. See the 2019 column in the above table. It's showing 1 in places it shouldn't. It's pretty complicated. status_from and status_thru could fall with 2017 in it, or 2017 could be inside status_from and status_thru, which should both be 1.
How do I exclude the nulls, and how do I show a 1 when the status date includes 2017? Is there an issue with date format that would make the one fall into both 2017 and 2019, when it's just 2017?
I've looked at [range within range][1], and [return 0 or 1][2]. I think I have all cases since the ranges overlap as well.
[1]: https://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates
[2]: https://stackoverflow.com/questions/14770092/returning-1-or-0-in-specific-sql-query
