July 29, 2017 at 5:36 am
Member can be enrolled multiple times in a year and can change plan anytime, I need to get C_level data for latest near to get date that is '12/31/2017' for ID 222, You might ask question 222 latest is 12/31/2099 why u want to show 12/31/2017, the reason is member is enrolled for future date as well, but we have to current one
Create table #Temp
(
INS_ID INT,
Start_Date Date,
End_Date Date,
C_Level SmallInt
)
Insert into #Temp Values (222,'01/01/2011','12/31/2011',1)
Insert into #Temp Values (222,'01/01/2012','12/31/2012',2)
Insert into #Temp Values (222,'01/01/2013','12/31/2013',3)
Insert into #Temp Values (222,'01/01/2014','12/31/2014',1)
Insert into #Temp Values (222,'01/01/2015','12/31/2015',1)
Insert into #Temp Values (222,'01/01/2016','12/31/2016',1)
Insert into #Temp Values (222,'01/01/2017','12/31/2017',3)
Insert into #Temp Values (222,'01/01/2018','12/31/2018',3)
Insert into #Temp Values (222,'01/01/2019','12/31/2099',0)
Insert into #Temp Values (223,'01/01/2017','07/28/2017',4)
Insert into #Temp Values (223,'07/29/2017','12/31/2017',2)
Insert into #Temp Values (223,'01/01/2018','12/31/2099',1)
Insert into #Temp Values (224,'01/01/2015','07/28/2015',4)
Insert into #Temp Values (224,'07/29/2016','12/31/2016',5)
----OUTPUT
(222,'01/01/2017','12/31/2017',3)
(223,'07/29/2017','12/31/2017',2)
(224,'07/29/2016','12/31/2016',5)
Thanks for help
July 29, 2017 at 9:53 am
maybe....
WITH ndate
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY ins_id ORDER BY ABS(DATEDIFF(day, end_date, @thedate))) rn
FROM #temp)
SELECT *
FROM ndate
WHERE rn = 1;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 31, 2017 at 7:26 am
I notice the proposed solution took absolute value of date difference, so it could include ones that end after the 12-31-2017 date.
ROW_NUMBER() evaluation can be good, but depending on how many enrollments each member has, this could get expensive as it has to enumerate ALL enrollments for every member. Since you only want one per INS_ID, maybe something like this, assuming you have some member table that this table is a child table of:DECLARE @thedate date = '2017-12-31';
SELECT m.ins_id, c.Start_Date, c.End_Date, c.C_Level
FROM #member m
CROSS APPLY
(SELECT TOP 1 ins_id, Start_Date, End_Date, C_Level
FROM #temp t
WHERE t.ins_id = m.ins_id
AND end_date <= @thedate
ORDER BY End_Date DESC) c;
If you always have a value in End_Date, this query could be supported by an index:CREATE UNIQUE NONCLUSTERED INDEX ix_enroll_recent ON #temp (INS_ID, End_Date DESC) INCLUDE (Start_Date, C_Level)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply