Joined: 22 Mar 2013 Posts: 21 Topics: 7 Location: Fort Worth, TX
Posted: Wed Apr 09, 2014 11:03 am Post subject: Can SQL combine rows to get contiguous date ranges?
right now I'm defining a cursor:
Code:
EXEC SQL DECLARE CUR_1 CURSOR FOR
SELECT E.PERSON_ID,
E.COVERAGE_TYPE,
TO_CHAR(E.START_DATE, 'YYYYMMDD'),
TO_CHAR(E.STOP_DATE, 'YYYYMMDD')
FROM DHS_ELIG.INT_ELIGIBILITY E, DHS_ELIG.INT_CASE C,
DHS_ELIG.INT_PERSON P
WHERE E.STATUS = 'Medicaid Eligible'
AND E.PERSON_ID = P.PERSON_ID
AND P.CASE_ID = C.CASE_ID
AND C.CASE_STATUS = 'Completed'
AND E.COVERAGE_TYPE IS NOT NULL
AND E.COVERAGE_TYPE NOT IN ('076','062')
AND E.STOP_DATE IS NOT NULL
AND E.STOP_DATE >
E.START_DATE
ORDER BY E.PERSON_ID,
E.COVERAGE_TYPE,
E.START_DATE,
E.STOP_DATE
END-EXEC.
and I'm getting a result set similar to the following:
but what I really want is to have rows combined such that I show a single row of coverage, when the coverage is logically contiguous. The result set I would like to get is :
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Apr 09, 2014 2:58 pm Post subject:
ironponygrl wrote:
do you mean what datatype? they are datatype 'DATE'. is that the question?
ironponygrl,
Yes that is indeed the question. Further clarification, when you say contiguous data records do you mean policies that begin on the first of the month and end on the last day of the month? or when the policy start_date begins after the last policy Stop_date + 1 day ?
Code:
FRED 2013-Jan-01 to 2014-Feb-02
FRED 2014-Jan-01 to 2014-Jan-31
FRED 2014-Feb-01 to 2014-Feb-28
FRED 2014-Feb-01 to 2015-Jan-31
So if I were to pick the contiguous records based on the months then I would pick
Code:
FRED 2014-Jan-01 to 2014-Jan-31
FRED 2014-Feb-01 to 2014-Feb-28
or if I am looking at continuous policy dates then I would pick
Code:
FRED 2013-Jan-01 to 2014-Feb-02
FRED 2014-Feb-01 to 2015-Jan-31
Picking by months is quite easy, but continuous would get tricky
Joined: 22 Mar 2013 Posts: 21 Topics: 7 Location: Fort Worth, TX
Posted: Wed Apr 09, 2014 3:11 pm Post subject:
Whew! maybe that's why it's not readily apparent to me. I need the 'tricky' answer.
Its not always a full month of coverage. the begin and end dates can be any calendar date, just happens that in my examples I picked 1st and last day of the month.
based on the following input:
Code:
FRED 2013-Jan-01 to 2014-Feb-02
FRED 2014-Jan-01 to 2014-Jan-31
FRED 2014-Feb-01 to 2014-Feb-28
FRED 2014-Feb-01 to 2015-Jan-31
I would like to get a result set that only has one row:
Code:
FRED 2013-JAN-01 to 2015-JAN-31.
but if there is a break in the coverage, (input below:)
Code:
FRED 2013-Jan-01 to 2013-Jun-02
FRED 2014-Jan-01 to 2014-Jan-31
FRED 2014-Feb-01 to 2014-Feb-28
FRED 2014-Feb-01 to 2015-Jan-31
I would want a single row for each continuous period of coverage:
Code:
FRED 2013-Jan-01 to 2013-Jun-02
FRED 2014-JAN-01 to 2015-Jan-31
Joined: 22 Mar 2013 Posts: 21 Topics: 7 Location: Fort Worth, TX
Posted: Wed Apr 09, 2014 5:19 pm Post subject:
i dont think i answered the question right. if there are two policies and the end date of one and the start date of the other are one day apart, then they are considered 'contiguous', i.e. there never was a lapse in coverage, because coverage is allocated for an entire calendar day. logically, its the same if it was one policy with a longer period between start and end.
what i'm trying to get too, is results from my cursor that represents all of the unbroken periods with a single row for each unbroken period.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Apr 09, 2014 5:59 pm Post subject:
ironponygrl,
I can't think of an easier way to compare each record and determine if the policy has continuous records. One nasty way is to use recursive SQL and generate all the dates between MIN(Start_date) and Max(Stop_Date) but that would a huge overhead.
Since you are already having the cursor , isn't it easy to handle it in a working storage array? You basically fetch each record and compare it with prior record's stop-date+1 day as the start_date and if it is the same, you simply overlay the prior stop_date with current record stop_date. If that check failed then you add this new record to the array and repeat the process until you are done with key combo. Once done you initialize the array table once again and repeat the process until you are all done with the cursor.
Btw if you just want the Character form of the dates then you can simply use
Code:
REPLACE(CHAR(START_DATE),'-','')
If the date is 2014-03-01 then the output is 20140301.
Doesn't the TO_CHAR function return a variable string?
Joined: 22 Mar 2013 Posts: 21 Topics: 7 Location: Fort Worth, TX
Posted: Thu Apr 10, 2014 8:30 am Post subject:
Thanks Kolusu. I've long been aware of the manipulation in a working storage array, but the problem has been around so long, (everybody in the insurance or public assistance arena has to wrestle with this problem) I was just wondering if anybody had come up with a simple SQL solution yet.
seems like years ago I did the recursive SQL thing too, I remember sucky performance. I was curious if some new function had been developed that I was unaware of. wishful thinking ...
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
