How to Group by dates, Location and then sum?

Database
Enthusiast

How to Group by dates, Location and then sum?

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!

I_SYS_CLM I_TME_SUR N_LOC D_BGN_LOC D_END_LOC A_PYM_CLM Q_DAY_PYM_CLM
43946 3680 NURSING HOME 4/16/2007 4/25/2007 900.00 9
43946 3680 ASSISTED LIVING - NH 6/8/2007 9/14/2007 9,600.00 196
43946 3680 NURSING HOME 10/3/2007 39,413 5,500.00 55

3 REPLIES
Enthusiast

Re: How to Group by dates, Location and then sum?

My apologies

I posted the wrong data.... I want the top half as a result and my data looks like this:

SORCE_CLAIM_ID SORCE_POLCY_ID SORCE_CLAIM_PMNT_DET_ID CLAIM_LOC_NM PMNT_FROM_DT PMNT_THRU_DT PMNT_RLSE_DT PMNT_TYPE_CD PMNT_AMT PMNT_DAYS_QTY
387 LAC503447 2925 HOME CARE - THC 5/29/1993 5/30/1993 12/9/1993 FP 112.50 1
387 LAC503447 2926 HOME CARE - THC 6/1/1993 6/6/1993 12/9/1993 FP 562.50 5
387 LAC503447 2927 HOME CARE - THC 6/7/1993 6/13/1993 12/9/1993 FP 675.00 6
387 LAC503447 2928 HOME CARE - THC 6/14/1993 6/20/1993 12/9/1993 FP 675.00 6
387 LAC503447 2929 HOME CARE - THC 6/21/1993 6/26/1993 12/9/1993 FP 562.50 5
387 LAC503447 2930 HOME CARE - THC 6/27/1993 7/4/1993 12/9/1993 FP 787.50 7
387 LAC503447 2931 HOME CARE - THC 7/5/1993 7/11/1993 12/9/1993 FP 675.00 6
387 LAC503447 2932 HOME CARE - THC 7/12/1993 7/18/1993 12/9/1993 FP 675.00 6
387 LAC503447 2933 HOME CARE - THC 7/19/1993 7/22/1993 12/9/1993 FP 337.50 3
387 LAC503447 2934 HOME CARE - THC 7/23/1993 7/25/1993 12/9/1993 FP 225.00 2
387 LAC503447 2935 HOME CARE - THC 7/26/1993 7/29/1993 12/9/1993 FP 337.50 3
387 LAC503447 2936 HOME CARE - THC 7/30/1993 7/31/1993 12/9/1993 FP 112.50 1
387 LAC503447 2937 HOME CARE - THC 8/2/1993 8/5/1993 12/9/1993 FP 337.50 3
387 LAC503447 2938 HOME CARE - THC 8/9/1993 8/15/1993 12/9/1993 FP 675.00 6
387 LAC503447 2939 HOME CARE - THC 8/16/1993 8/21/1993 12/9/1993 FP 562.50 5
387 LAC503447 2940 HOME CARE - THC 8/23/1993 8/26/1993 12/9/1993 FP 337.50 3
387 LAC503447 2941 HOME CARE - THC 8/27/1993 8/28/1993 12/9/1993 FP 112.50 1
387 LAC503447 3324 HOME CARE - THC 8/30/1993 8/31/1993 2/18/1994 SP 112.50 1
387 LAC503447 3325 HOME CARE - THC 9/1/1993 9/2/1993 2/18/1994 SP 112.50 1
387 LAC503447 3326 HOME CARE - THC 9/6/1993 9/7/1993 2/18/1994 SP 112.50 1
387 LAC503447 3327 HOME CARE - THC 9/13/1993 9/14/1993 2/18/1994 SP 112.50 1
387 LAC503447 3328 HOME CARE - THC 9/20/1993 9/21/1993 2/18/1994 SP 112.50 1
387 LAC503447 3329 HOME CARE - THC 9/27/1993 9/28/1993 2/18/1994 SP 112.50 1
387 LAC503447 3330 HOME CARE - THC 10/4/1993 10/5/1993 2/18/1994 SP 112.50 1
387 LAC503447 3331 HOME CARE - THC 10/11/1993 10/12/1993 2/18/1994 SP 112.50 1
387 LAC503447 3332 HOME CARE - THC 10/18/1993 10/19/1993 2/18/1994 SP 112.50 1
387 LAC503447 3333 HOME CARE - THC 10/25/1993 10/26/1993 2/18/1994 SP 112.50 1
387 LAC503447 3334 HOME CARE - THC 11/1/1993 11/2/1993 2/18/1994 SP 112.50 1
387 LAC503447 3335 HOME CARE - THC 11/8/1993 11/9/1993 2/18/1994 SP 112.50 1
387 LAC503447 3336 HOME CARE - THC 11/15/1993 11/16/1993 2/18/1994 SP 112.50 1
387 LAC503447 3337 HOME CARE - THC 11/22/1993 11/23/1993 2/18/1994 SP 112.50 1
387 LAC503447 3338 HOME CARE - THC 11/29/1993 11/30/1993 2/18/1994 SP 112.50 1
387 LAC503447 3339 HOME CARE - THC 12/1/1993 12/2/1993 2/18/1994 SP 112.50 1
387 LAC503447 3340 HOME CARE - THC 12/6/1993 12/7/1993 2/18/1994 SP 112.50 1
387 LAC503447 3341 HOME CARE - THC 12/13/1993 12/14/1993 2/18/1994 SP 112.50 1
387 LAC503447 3342 HOME CARE - THC 12/20/1993 12/21/1993 2/18/1994 SP 112.50 1
387 LAC503447 3343 HOME CARE - THC 12/27/1993 12/28/1993 2/18/1994 SP 112.50 1
387 LAC503447 3904 HOME CARE - THC 1/3/1994 1/5/1994 5/19/1994 SP 225.00 2
387 LAC503447 3905 HOME CARE - THC 1/10/1994 1/12/1994 5/19/1994 SP 225.00 2
387 LAC503447 3906 HOME CARE - THC 1/17/1994 1/18/1994 5/19/1994 SP 112.50 1
387 LAC503447 3907 HOME CARE - THC 1/24/1994 1/26/1994 5/19/1994 SP 225.00 2
387 LAC503447 3908 HOME CARE - THC 2/1/1994 2/3/1994 5/19/1994 SP 225.00 2
387 LAC503447 3909 HOME CARE - THC 2/7/1994 2/9/1994 5/19/1994 SP 225.00 2
387 LAC503447 3910 HOME CARE - THC 2/14/1994 2/16/1994 5/19/1994 SP 225.00 2
387 LAC503447 3911 HOME CARE - THC 2/21/1994 2/23/1994 5/19/1994 SP 225.00 2
387 LAC503447 3912 HOME CARE - THC 3/1/1994 3/3/1994 5/19/1994 SP 225.00 2
387 LAC503447 3913 HOME CARE - THC 3/7/1994 3/9/1994 5/19/1994 SP 225.00 2
387 LAC503447 3914 HOME CARE - THC 3/14/1994 3/16/1994 5/19/1994 SP 225.00 2
387 LAC503447 3915 HOME CARE - THC 3/21/1994 3/23/1994 5/19/1994 SP 225.00 2
387 LAC503447 3916 HOME CARE - THC 3/28/1994 3/30/1994 5/19/1994 SP 225.00 2
387 LAC503447 4804 HOME CARE - PHC 4/4/1994 4/6/1994 9/7/1994 SP 225.00 2
387 LAC503447 4805 HOME CARE - PHC 4/11/1994 4/13/1994 9/7/1994 SP 225.00 2
387 LAC503447 4806 HOME CARE - PHC 4/18/1994 4/20/1994 9/7/1994 SP 225.00 2
387 LAC503447 4807 HOME CARE - PHC 4/25/1994 4/27/1994 9/7/1994 SP 225.00 2
387 LAC503447 4808 NURSING HOME 5/2/1994 5/7/1994 9/7/1994 SP 750.00 5
387 LAC503447 4809 HOME CARE - PHC 5/9/1994 5/11/1994 9/7/1994 SP 225.00 2
387 LAC503447 4810 HOME CARE - PHC 5/16/1994 5/18/1994 9/7/1994 SP 225.00 2
387 LAC503447 4811 HOME CARE - PHC 5/23/1994 5/25/1994 9/7/1994 SP 225.00 2
387 LAC503447 4812 HOME CARE - PHC 5/30/1994 6/1/1994 9/7/1994 SP 225.00 2
387 LAC503447 4813 HOME CARE - PHC 6/6/1994 6/8/1994 9/7/1994 SP 225.00 2
387 LAC503447 4814 HOME CARE - PHC 6/13/1994 6/15/1994 9/7/1994 SP 225.00 2
387 LAC503447 4815 HOME CARE - PHC 6/20/1994 6/22/1994 9/7/1994 SP 225.00 2
387 LAC503447 4816 HOME CARE - PHC 6/27/1994 6/29/1994 9/7/1994 SP 225.00 2
387 LAC503447 4544 NURSING HOME 7/1/1994 8/1/1994 8/3/1994 SP 4,500.00 31
387 LAC503447 4817 NURSING HOME 8/1/1994 9/1/1994 9/7/1994 SP 4,500.00 31
387 LAC503447 5329 NURSING HOME 9/1/1994 10/1/1994 10/21/1994 SP 4,500.00 30
387 LAC503447 5444 NURSING HOME 10/1/1994 11/7/1994 11/8/1994 SP 5,400.00 37
387 LAC503447 7224 HOME CARE - PHC 2/3/1995 2/4/1995 3/24/1995 SP 112.50 1
387 LAC503447 7225 HOME CARE - PHC 2/10/1995 2/11/1995 3/24/1995 SP 112.50 1
387 LAC503447 7226 HOME CARE - PHC 2/17/1995 2/18/1995 3/24/1995 SP 112.50 1
387 LAC503447 7227 HOME CARE - PHC 2/24/1995 2/25/1995 3/24/1995 SP 112.50 1
387 LAC503447 7228 HOME CARE - PHC 3/3/1995 3/4/1995 3/24/1995 SP 112.50 1
387 LAC503447 7229 HOME CARE - PHC 3/10/1995 3/11/1995 3/24/1995 SP 112.50 1
387 LAC503447 8106 HOME CARE - PHC 3/17/1995 3/18/1995 5/23/1995 SP 112.50 1
387 LAC503447 8107 HOME CARE - PHC 3/24/1995 3/25/1995 5/23/1995 SP 112.50 1
387 LAC503447 8108 HOME CARE - PHC 3/31/1995 4/1/1995 5/23/1995 SP 112.50 1
387 LAC503447 8109 HOME CARE - PHC 4/7/1995 4/8/1995 5/23/1995 SP 112.50 1
387 LAC503447 8110 HOME CARE - PHC 4/15/1995 4/16/1995 5/23/1995 SP 112.50 1
387 LAC503447 8111 HOME CARE - PHC 4/21/1995 4/22/1995 5/23/1995 SP 112.50 1
387 LAC503447 8112 HOME CARE - PHC 4/28/1995 4/29/1995 5/23/1995 SP 112.50 1
387 LAC503447 8113 HOME CARE - PHC 5/5/1995 5/6/1995 5/23/1995 SP 112.50 1
387 LAC503447 8823 HOME CARE - PHC 5/12/1995 5/13/1995 7/10/1995 SP 112.50 1
387 LAC503447 8824 HOME CARE - PHC 5/19/1995 5/20/1995 7/10/1995 SP 112.50 1
387 LAC503447 8825 NURSING HOME 5/21/1995 5/27/1995 7/10/1995 SP 900.00 6
387 LAC503447 8826 HOME CARE - PHC 6/9/1995 6/10/1995 7/10/1995 SP 112.50 1
387 LAC503447 8827 HOME CARE - PHC 6/16/1995 6/17/1995 7/10/1995 SP 112.50 1
387 LAC503447 9790 HOME CARE - PHC 6/23/1995 6/24/1995 9/8/1995 SP 112.50 1
387 LAC503447 9791 HOME CARE - PHC 6/30/1995 7/1/1995 9/8/1995 SP 112.50 1
387 LAC503447 9792 HOME CARE - PHC 7/7/1995 7/8/1995 9/8/1995 SP 112.50 1
387 LAC503447 9793 HOME CARE - PHC 7/14/1995 7/15/1995 9/8/1995 SP 112.50 1

So disregard the SORCE_CLAIM_ID of "43946"

THANKS!
Senior Apprentice

Re: How to Group by dates, Location and then sum?

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...

Dieter
Enthusiast

Re: How to Group by dates, Location and then sum?

Dieter,

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.