OraFAQ Forum: SQL & PL/SQL » Help with oracle query to manipulate dates

Home » SQL & PL/SQL » SQL & PL/SQL » Help with oracle query to manipulate dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with oracle query to manipulate dates [message #646226]

Tue, 22 December 2015 08:21

nsk78
Messages: 6
Registered: May 2014

Junior Member

I posted my query yesterday and it seems like its deleted. I am trying to provide more explanation. I am trying to adjust dates so that the patients don't have common overlapping days .I find the svc_dt+days_supply and compare it with next fill date,if the patients next date is less then I am trying to adjust the date as previos svc_dt+days_supply .
The next svc_dt+days_supply should be done on the adjusted date and not on the svc date.

DROP TABLE TEST_PATS;
CREATE TABLE TEST_PATS (PAT_ID NUMBER,SVC_DT DATE,DAYS_SUPPLY NUMBER);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('2/6/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('3/6/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('4/5/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('5/7/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('9/4/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('10/1/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('10/29/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('11/28/2012','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('1/2/2013','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('1/29/2013','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('2/18/2013','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('3/27/2013','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('5/3/2013','MM/DD/YYYY'),30);
INSERT INTO TEST_PATS VALUES(76882818,TO_DATE('5/30/2013','MM/DD/YYYY'),30);

COMMIT;

DROP

DROP TABLE TEST_TMP1;
CREATE TABLE TEST_TMP1 AS
SELECT A.*,NVL(GREATEST(SVC_DT,ACTUAL_DT),SVC_DT) AS ADJUSTED_DT FROM(
SELECT A.* ,SVC_DT+DAYS_SUPPLY AS NEXT_SVC_DT ,LAG(SVC_DT+DAYS_SUPPLY ) OVER(PARTITION BY pat_id ORDER BY SVC_DT ) AS ACTUAL_DT
FROM TEST_PATS A ) A;
-- 76882818 2/6/2012 30 3/7/2012 2/6/2012
76882818 3/6/2012 30 4/5/2012 3/7/2012 3/7/2012
76882818 4/5/2012 30 5/5/2012 4/5/2012 4/5/2012
76882818 5/7/2012 30 6/6/2012 5/5/2012 5/7/2012
76882818 9/4/2012 30 10/4/2012 6/6/2012 9/4/2012
76882818 10/1/2012 30 10/31/2012 10/4/2012 10/4/2012
76882818 10/29/2012 30 11/28/2012 10/31/2012 10/31/2012-- this should become 10/4 +30 which is 11/3 and not 10/1+30
76882818 11/28/2012 30 12/28/2012 11/28/2012 11/28/2012- 12/3
76882818 1/2/2013 30 2/1/2013 12/28/2012 1/2/2013
76882818 1/29/2013 30 2/28/2013 2/1/2013 2/1/2013
76882818 2/18/2013 30 3/20/2013 2/28/2013 2/28/2013
76882818 3/27/2013 30 4/26/2013 3/20/2013 3/27/2013
76882818 5/3/2013 30 6/2/2013 4/26/2013 5/3/2013
76882818 5/30/2013 30 6/29/2013 6/2/2013 6/2/2013

DROP TABLE TEST_TMP2;
CREATE TABLE TEST_TMP2 AS
SELECT MONTH_ID,PAT_ID,ADJUSTED_DT AS SVC_DT, SVC_DT AS OLD_DOS,DAYS_SUPPLY FROM TEST_TMP1;

Report message to a moderator