CODEWITHSUNDEEP
sql-server
NotACoder
NotACoder

Reputation: 43

SQL Server: Find records with closest Date to CurrentDate based on conditions

I'm using SQL Server 2012 and I'm attempting to create a VIEW that would return records based on these conditions:

  1. Query needs to retrieve most applicable record based on date
  2. For dates that are within an inner Date Range, the closest record to CurrentDate will be returned
  3. For dates that are outside an inner Date Range, the closest record to CurrentDate will be returned

Sample tables in the database:

Person table:

pId     | Name
----------------------
01      | Person 1
02      | Person 2
----------------------

PersonDate table:

dId     |  pId      | StartDate     | EndDate
---------------------------------------------------
A1      |   01      |   2014-01-08  |   2018-01-08  
A2      |   01      |   2016-11-23  |   2016-12-01  
A3      |   01      |   2016-12-03  |   2016-12-08
A4      |   02      |   2016-10-10  |   2016-12-31
A5      |   02      |   2016-12-01  |   2016-12-05

If I run this query and the CurrentDate is 2016-11-28:

select p.name, d.startdate, d.enddate
from Person p, PersonDate d
where p.pId = d.pId
and d.StartDate = (select max(sl.StartDate)
                   from PersonDate sl
                   where d.pId = s1.pId)

The records that are returned are:

name        | startdate     | enddate
-------------------------------------------
Person 1    |   2016-12-03  |   2016-12-08      --> PersonDate Table row A3
Person 2    |   2016-12-01  |   2016-12-05      --> PersonDate Table row A5
-------------------------------------------

Both returned records are incorrect based on the conditions I'm trying to get back. I understand why I'm getting the returned records, and it is due to using the Max() function within my subquery, but I don't know how to write the query/subquery.

The correct records that I want to be return are (CurrentDate being 2016-11-28):

name        | startdate     | enddate
-------------------------------------------
Person 1    |   2016-11-23  |   2016-12-01
Person 2    |   2016-10-10  |   2016-12-31
-------------------------------------------

When CurrentDate is 2016-12-02:

name        | startdate     | enddate
---------------------------------------------
Person 1    |   2014-01-08  |   2018-01-08  
Person 2    |   2016-12-01  |   2016-12-05
---------------------------------------------

How can I write a VIEW that would return records based on the conditions above?

Upvotes: 4

Views: 1155

Answers (1)

Mani
Mani

Reputation: 344

create table #temp(did varchar(10),pid int,startdate datetime,enddate datetime)

insert into #temp values('A1',01,'2014-01-08','2018-01-08')
insert into #temp values('A2',01,  '2016-11-23'  ,   '2016-12-01'   )
insert into #temp values('A3',01, '2016-12-03'  ,   '2016-12-08'  )
insert into #temp values('A4',02,  '2016-10-10'  ,   '2016-12-31'  )
insert into #temp values('A5',02, '2016-12-01'  ,   '2016-12-05'  )


select b.pid,b.startdate,b.enddate
from
(
select ROW_NUMBER()over(partition by pid order by id desc) as SID , a.*
from
(
select 
ROW_NUMBER()over(partition by pid order by startdate,enddate desc) as ID
, * from #temp 
--to identify whether it is inner or outer
--1 means outer
--2 means inner
)a
where '2016-12-02' between startdate and enddate
--to find date lies in outer or inner range and select the required
)b
where b.SID=1

Upvotes: 1

Related Questions