SQL to return 1 for each row with column range in 2017 or 2018 or 2019

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 2018 column for now):

Person_id  Last_Name  Middle  First_Name  Status_from_date Status_thru_date    2018
8000       Skywalker  Dude    Luke        Null             2010-01-28 07:38      1
9000       Yoda               Phinnius    2017-06-01 00:00 2019-05-31 00:00      1
1000       Lamb       Little  Mary        2018-07-01 00:00 2020-06-30 00:00      1
2000       Spider     Bitsy   Itsy        2016-11-01 00:00 2017-06-30 00:00      1

How do I add a column, say [2018], and put a 1 for if status_from_date to status_thru_date is in 2018, or a 0 if not?

I wanted to add the following at the –(a) in the query:

,(SELECT case 
             when exists
             (
                select *  --
                FROM   dbo.RV_Person_status_hist
                where 
                status_from_date is not null
                and
                ('1-1-2018' between status_from_date and status_thru_date)
                and status_from_date is not null
                ) 
                then 1 else 0 end  ) 

                  AS [2018]

This doesn’t seem to be working, though. See the 2018 column in the above table. It’s showing 1 for all returned, and it’s not excluding nulls. It’s pretty complicated. status_from and status_thru could fall with 2018 in it, or 2018 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 2018?

I’ve looked at range within range, and return 0 or 1. I don’t think I have all cases since the ranges overlap as well.

**Update: I tried adding this at –(a) above instead, per the potential answer below:

,(SELECT status_from_date, status_thru_date,
             case 
             when datepart(year, status_from_date)='2018'
               or datepart(year, status_thru_date)='2018'
               or (
                status_from_date <= '01/01/2018'
                and status_thru_date >= '12/12/2018'
               )
            then 1
              else 0
              end)  AS [2018]

but I’m getting Ambiguous column name ‘status_from_date’. Ambiguous column name ‘status_thru_date’. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Any ideas? Figured it out.

**Update 2: How about this?

,(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]--------

That one is putting a 1 in the 2017 column for both male and female for the data of: status_from: 2014-10-01 and status_to: 2016-09-30

Answer

You could do something like this:

while would be to check if start or end contains 2018, or if the date is between start and thru

CREATE TABLE #testTable (
    Status_from_date DATETIME,
    Status_thru_date DATETIME
    )

INSERT INTO #testTable (
    Status_from_date,
    Status_thru_date
    )
VALUES (
    '2017-06-01 00:00',
    '2019-05-31 00:00'
    ),
    (
    NULL,
    '2010-01-28 07:38'
    ),
    (
    '2018-07-01 00:00',
    '2020-06-30 00:00'
    )

SELECT Status_from_date,
    Status_thru_date,
    CASE 
        WHEN datepart(year, Status_from_date) = '2018'
            OR datepart(year, Status_thru_date) = '2018'
            OR (
                Status_from_date <= '01/01/2018'
                AND Status_thru_date >= '12/12/2018'
                )
            THEN 1
        ELSE 0
        END AS '2018'
FROM #testTable

DROP TABLE #testTable

which produces:

Status_from_date            Status_thru_date            2018
2017-06-01 00:00:00.000     2019-05-31 00:00:00.000     1
NULL                        2010-01-28 07:38:00.000     0
2018-07-01 00:00:00.000     2020-06-30 00:00:00.000     1

Advertisement

Answer