question

Michele01 avatar image
Michele01 asked

Query that puts "1" in year column for date range; can overlap

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

sql-server-2008-r2querycase-statementdate-format
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered

This seems to be one of those situations where it's worth turning the logic upside-down. I think you want to flag all those records where the range covers at least part of 2017. It might be an idea to look at identifying all those records where the date range ends before 2017, or the date range starts after 2017, and then picking those that don't match.

I'm sure I saw an article about this sort of thing, but I can't find it right now.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.