CODEWITHSUNDEEP
sqlsql-servert-sql
teni
teni

Reputation: 455

SQL: Find date match outside or within a range

In a Microsoft SQL Server, we have this table which logs property changes of users:

ID | userID | prop | changeDate
-------------------------------
 4   23      B       2017-02-18
 1   14      D       2013-05-19
 2   15      C       2014-04-10
 3   23      A       2016-01-11

What I would like to do is to query this table and find the active property within a range of dates.

For instance the period of 2016-08-01 to 2017-01-01 I would like to get userID 23 with prop A because that was the active property for that user until it was changed on 2017-02-18

I started out with the changeDate BETWEEN '2016-08-01' and '2017-01-01' but that only covers those cases where I have the exact start date.

I'm turning slightly insane. Is this quest even possible with this data? Or maybe I am asking the wrong question?

Upvotes: 0

Views: 70

Answers (2)

Steve
Steve

Reputation: 710

A self join can help here. 

CREATE TABLE x (ID INT, userID INT, prop CHAR(1), changeDate DATETIME)

INSERT INTO x
  VALUES ( 4, 23, 'B', '20170218' ),
         ( 1, 14, 'D', '20130519' ),
         ( 2, 15, 'C', '20140410' ),
         ( 3, 23, 'A', '20160111' )

; WITH cte AS (
  SELECT  x1.userID, x1.prop, x1.changeDate AS StartDate, MIN(x2.changeDate) AS ExitDate
  FROM    x x1
          LEFT OUTER JOIN x  x2
                  ON x1.changeDate < x2.changeDate
  GROUP BY x1.userID, x1.prop, x1.changeDate
)

SELECT  *
FROM    cte
WHERE   Startdate > = '20160801' 
  AND   ( ExitDate IS NULL OR ExitDate < '20170101' )

This gives you what you are looking for

userID | prop | StartDate               | ExitDate
--------------------------------------------------
 23       B      2017-02-18 00:00:00.000   NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

For a table of this type, the typical question is "What was a given users property as-of a particular date?"

This answers that question:

select pc.*
from (select pc.*,
             row_number() over (partition by userId order by changeDate desc) as seqnum
      from propertychanges pc
      where changeDate <= @AsOfDate
     ) pc
where seqnum = 1;

Upvotes: 2

Related Questions