I have a query that is bringing back the following record set:
LTC_P.VLTC_CLM_LOC_MO I_SYS_CLM I_TME_SUR N_LOC D_BGN_LOC D_END_LOC A_PYM_CLM Q_DAY_PYM_CLM 387 3710 HOME CARE - PHC 5/29/1993 4/27/1994 13,725.00 122 387 3710 NURSING HOME 5/2/1994 5/7/1994 750.00 5 387 3710 HOME CARE - PHC 5/9/1994 6/29/1994 1,800.00 16 387 3710 NURSING HOME 7/1/1994 11/7/1994 18,900.00 129 387 3710 HOME CARE - PHC 2/3/1995 5/20/1995 1,800.00 16 387 3710 NURSING HOME 5/21/1995 5/27/1995 900.00 6 387 3710 HOME CARE - PHC 6/9/1995 2/15/1996 6,075.00 99 387 3710 NURSING HOME 2/16/1996 2/23/1996 1,050.00 7 387 3710 HOME CARE - PHC 2/28/1996 6/25/1998 28,237.50 251 387 3710 NURSING HOME 6/25/1998 7/31/1998 187.50 -28 387 3710 HOME CARE - PHC 6/26/1998 4/1/2000 18,225.00 156 387 3710 NURSING HOME 4/7/2000 5/13/2000 5,400.00 36 387 3710 HOME CARE - PHC 5/17/2000 6/24/2000 1,350.00 12 387 3710 NURSING HOME 6/24/2000 9/1/2000 9,600.00 69
I want to be able to group all the "N_LOC" by date as well as get the sums like shown below. Is there any way to do this using straight SQL, or will I have to analyze the record set and read it row by row using SAS or something similar? I have been working on this for a while and it's really killing me! any help would be greatly appreciated!!!!! I have the original query as well if it makes sense for you to see... Otherwise I will make a temp table with the data above and then create another query if possible to get the below results. THANKS SO MUCH!
You'd better apologize again, source data and expected result set don't match at all :-)
But i think i know what you try to achieve: Whenever the SORCE_CLAIM_PMNT_DET_ID changes compared to the "previous" row then a new group starts. This might be done with nested OLAP-functions. Following query is syntactically correct, but i could't test it, because you didn't provide DDL and INSERTS:
SELECT SORCE_CLAIM_ID, SORCE_POLCY_ID, CLAIM_LOC_NM, MIN(PMNT_FROM_DT), MAX(PMNT_THRU_DT), SUM(PMNT_AMT), SUM(PMNT_DAYS_QTY) FROM ( SELECT dt.*, SUM(x) OVER (PARTITION BY SORCE_CLAIM_ID, SORCE_POLCY_ID ORDER BY PMNT_THRU_DT ROWS UNBOUNDED PRECEDING) AS grp FROM ( SELECT tab.*, CASE WHEN CLAIM_LOC_NM = MAX(CLAIM_LOC_NM) OVER (PARTITION BY SORCE_CLAIM_ID, SORCE_POLCY_ID ORDER BY PMNT_THRU_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS x FROM tab ) AS dt ) AS dt GROUP BY 1,2,3,grp ORDER BY 4
If it's the wrong answer then please post DDL/INSERTs/result set and try to explain in detail...
I just have to say, I don't know who you are or what you do, but you are amazing! and you have been a huge help!!!! Needless to say you have nailed this one just like the other questions I've had right off the start (even with my terrible explanations and incorrect expected results).
You time and effort are GREATLY appreciated. You are becoming well known around here as the genius on the Teradata Forum.