September 18, 2018 at 6:55 am
CREATE TABLE #TEMP(
ID INT,
S_DATE DATE NULL,
E_DATE DATE NULL)
INSERT INTO #TEMP
VALUES
(1,'01/01/2018','12/31/2018'),
(1,'01/01/2019',NULL),
(2,'01/01/2018','12/31/2018'),
(2,'01/01/2019',NULL),
(3,'01/01/2018','12/31/2018'),
(4,'01/01/2019',NULL)
Below is the steps I’m trying to do in SQL, rules are something i can do once data is filtered out. I just need to know best way to store and add flags- so that it will be helpful to achieve below ones..
1) ID 1 and 2 exists in both current year and New Year - if ID is exists in both current (2018) and New Year (2019) then currently year should follow rule 11 and New Year should follow rule 12
2) ID 3 exists only in current year then need to follow rule 13
3) ID 4 exists only in New Year then need to follow rule 14
September 18, 2018 at 7:34 am
This kind of thing is extremely data dependent, Could you ever have a single row that spans a year boundary (on or before 12/31/xxxx to on or after 1/1/xxxy, where xxxx is the year prior to xxxy) ? You have provided sample data, but as we don't have any idea what rules 11 through 14 are, there's no way to provide a coded solution. We would need the exact expected results as well as the details on exactly how to follow the rules, regardless of the ID value involved.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2018 at 11:21 am
sgmunson - Tuesday, September 18, 2018 7:34 AMThis kind of thing is extremely data dependent, Could you ever have a single row that spans a year boundary (on or before 12/31/xxxx to on or after 1/1/xxxy, where xxxx is the year prior to xxxy) ? You have provided sample data, but as we don't have any idea what rules 11 through 14 are, there's no way to provide a coded solution. We would need the exact expected results as well as the details on exactly how to follow the rules, regardless of the ID value involved.
Hi, let me explain better way, Member can enroll into healthcare for future dates as well, For ID-1 he is going to terminate on 31 of dec and enrolled for new year advance as well (new one always end of old one plus 1 day ). Let me come to the rules- If he is having current active and future active timeline then i need to additional checks-one example is need to check current year is having reason for termination or not - and new year termination reason should be blank or not and go on- But only thing is need to identity which member is
1) Active for current year and new year
2)Only current year
3))Only new year-- I just need to know better way to store data and filter -flags - let say i want only new year data for additional checks.
Hope this helps! thanks
September 18, 2018 at 12:22 pm
This will at least get you started.
SELECT *,
CASE
WHEN MAX(S_DATE) OVER(PARTITION BY [ID]) < GETDATE() THEN 'Only Current Year'
WHEN MIN(S_DATE) OVER(PARTITION BY [ID]) > GETDATE() THEN 'Only New Year'
ELSE 'Both'
END AS Grp
FROM #TEMP
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2018 at 3:17 am
drew.allen - Tuesday, September 18, 2018 12:22 PMThis will at least get you started.
SELECT *,
CASE
WHEN MAX(S_DATE) OVER(PARTITION BY [ID]) < GETDATE() THEN 'Only Current Year'
WHEN MIN(S_DATE) OVER(PARTITION BY [ID]) > GETDATE() THEN 'Only New Year'
ELSE 'Both'
END AS Grp
FROM #TEMPDrew
Yes, it looks great Drew, it will be helpful to start 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply