How to sum records where a particular value changes within a grouping?

Database
Enthusiast

How to sum records where a particular value changes within a grouping?

I had done something along these lines with this line from an earlier post:
,SUM(a.a_pym_det) OVER (ORDER BY a.d_bgn_pym_det ROWS UNBOUNDED PRECEDING) AS "Running Total"

....but it doesn't quite get to what I need - because it didn't collapse the lines - just accumulates each line on and on.
How do you interate through the rows one by one with SQL?

I'm trying to do something like this which I think may require creating a temp table?

From Date To Date Amount Days
9/7/03 9/10/03 425 3
9/10/03 10/1/03 1729 19
10/9/03 11/12/03 5861 34
Total 8015 56

Currently using this SQL:

Select SorceClaimID as Claim
,SorcePolicyID as Pol
,ClaimLocName as Location
,Min(pmnt_from_dt) as From Date
,Max(pmnt_thru_dt) as ToDate
,Sum(pmnt_amt) as Amount
,Sum(PaymentDaysQty) as Days
from GetLocDetail
group by 1,2,3
where SorceClaimID = "AH68288"

Again, I'm getting the totals properly, just not the individual payment details. Any help on this would be greatly appreciated!
7 REPLIES
Senior Apprentice

Re: How to sum records where a particular value changes within a grouping?

You post is a bit confusing, it would be helpful to get some more details what you're actually trying to achieve.
This sounds like a GROUP BY GROUPING SETS or a SUM OVER (PARTITION BY...)

Dieter
Enthusiast

Re: How to sum records where a particular value changes within a grouping?

Sorry... That was more than a bit confusing...

Basically what is happening. I have a table with claims belonging to a number of claimants.

Say I have a claimant going to a number of Facilities:

Claim ID Location PaymentFrom PaymentTo Amount
4875 Nursing 11/12/07 11/31/07 500.00
4875 Assisted 12/01/08 12/14/08 250.00
4875 Nursing 12/15/08 12/31/08 400.00

I want to Pull the last location "Nursing" the dates 12/15/08 - 12/31/08 and the payment amount of 400.00.

I'm assuming this would be date driven. Also, there will be MANY Claim ID's (which basically boil down to claimants) So for each group of claim IDs i need the max dates (payment from and payment to)... Can this be done in straight SQL or will something like SAS have to read the recordset?

THANKS!
Senior Apprentice

Re: How to sum records where a particular value changes within a grouping?

And now for something completely different :-)

You just want all the data for the row with the latest PaymentFrom per SorceClaimID?
This is neither GROUP BY GROUPING SETS nor SUM OVER (PARTITION BY...), it's a simple RANKing:

Select *
from GetLocDetail
qualify
rank() over (partition by SorceClaimID order by PaymentFrom desc) = 1

Dieter
Enthusiast

Re: How to sum records where a particular value changes within a grouping?

Awesome... One more thing... I'm trying to create a volatile table and I'm getting the following error:

"Column/Parameter DIW07.GetLocDetail.i_sys_clm does not exist"

Any Ideas? THANKS SO MUCH!

create volatile table GetLocDetail as (

SELECT b.i_sys_clm AS "SorceClaimID"
,a.c_pol AS "SorcePolicyID"
,c.i_sys_pym_det AS "SorceClaimPaymentID"
,(case
when d.c_loc = '99' then null
when d.c_loc = '01' then 'HOSPITAL'
when d.c_loc = '02' and c.c_pym_det = 'FAC' then 'ASSISTED LIVING - NH'
when d.c_loc = '02' and c.c_pym_det = 'PHC' then 'ASSISTED LIVING - HC'
when d.c_loc = '02' and c.c_pym_det = 'THC' then 'ASSISTED LIVING - HC'
when d.c_loc = '03' then 'HOME CARE - THC'
when c.c_pym_det = 'IHC' then 'HOME CARE - THC'
when c.c_pym_det = 'THC' then 'HOME CARE - THC'
when d.c_loc = '04' then 'NURSING HOME'
when c.c_pym_det = 'FAC' then 'NURSING HOME'
when d.c_loc = '05' then 'HOME CARE - THC'
when d.c_loc = '06' then 'HOME CARE - PHC'
when d.c_loc = '09' then 'HOME CARE - PHC'
when d.c_loc = '10' then 'HOME CARE - PHC'
when d.c_loc = '11' and substr(a.c_pol,1,3) = 'OAC' then 'HOME CARE - PHC - KC'
when d.c_loc = '11' then 'HOME CARE - PHC'
when d.c_loc = 'PHC' and substr(a.c_pol,1,3) = 'OAC' then 'HOME CARE - PHC - KC'
when c.c_pym_det = 'PHC' then 'HOME CARE - PHC'
when d.c_loc = '07' and c.c_pym_det = 'FAC' then 'RESPITE CARE - NH'
when d.c_loc = '07' and c.c_pym_det = 'PHC' then 'RESPITE CARE - HC'
when d.c_loc = '07' and c.c_pym_det = 'THC' then 'RESPITE CARE - HC'
when d.c_loc = '07' and c.c_pym_det = 'IHC' then 'RESPITE CARE - HC'
when d.c_loc = '08' and substr(a.c_pol,1,3) = 'OAC' then 'HOME CARE - PHC - KC'
when c.c_pym_det = '08' and c.c_pym_det = 'FAC' then 'NURSING HOME'
when c.c_pym_det = '08' and c.c_pym_det = 'PHC' then 'HOME CARE - PHC'
when c.c_pym_det = '08' and c.c_pym_det = 'THC' then 'HOME CARE - THC'
when d.c_loc = '12' then 'ACS Caregiver Training'
when d.c_loc = '13' then 'ACS Equipment'
when d.c_loc = '14' then 'ACS Special Services'
when d.c_loc = '15' then 'ACS Other'
when d.c_loc = '16' then 'Alt Care/rehab/non ACS'
when d.c_loc = '17' then 'Rehabilitation Services'
when d.c_loc = '18' then 'Physical Therapy Visits'
when d.c_loc = '19' then 'Speech Therapy Visits'
when d.c_loc = '20' then 'HHA/PCA'
when d.c_loc = '21' then 'Hospice/Palliative Care'
when d.c_loc = '22' then 'Companion'
when d.c_loc = '23' then 'Adult Day Care'
when d.c_loc = '24' then 'Social Worker'
when d.c_loc = '25' then 'Nutritionist'
when d.c_loc = '26' then 'Oxygen Therapy'
when d.c_loc = '28' then 'Diagnostic Services'
when d.c_loc = '29' then 'Therapeutic Therapy/Services'
when d.c_loc = '30' then 'Pool Therapy'
when d.c_loc = '31' then 'Massage Therapy'
when d.c_loc = '32' then 'Holistic Services'
when d.c_loc = '33' then 'Medication Management'
when d.c_loc = '34' then 'Occupational Therapy Visits'
when c_pym_det = '021' then 'HOME CARE _ HMH L1'
when c_pym_det = '022' then 'HOME CARE _ HMH L2'
when c_pym_det = '023' then 'NURSING HOME'
else 'Error'
end) as "ClaimLocName"

,(CASE
WHEN a_pym_det >= 0 THEN (c.d_end_pym_det - c.d_bgn_pym_det)
ELSE (c.d_end_pym_det- c.d_bgn_pym_det) * -1
END) AS "PaymentDaysQty"
,c.d_bgn_pym_det AS pmnt_from_dt
,c.d_end_pym_det AS pmnt_thru_dt
,b.d_rls_pym AS pmnt_rlse_dt
,b.c_typ_pym_clm AS pmnt_type_cd
,c.a_pym_det AS pmnt_amt
,SUM(c.a_pym_det) OVER (ORDER BY c.d_bgn_pym_det ROWS UNBOUNDED PRECEDING) AS "Running Total"
,SUM( "PaymentDaysQty") OVER (ORDER BY c.d_bgn_pym_det ROWS UNBOUNDED PRECEDING) AS "Running Pmnt Days"

FROM BCPM_DDBO.vltc900a_clm a
INNER JOIN BCPM_DDBO.vltc915a_pym b ON a.i_sys_clm = b.i_sys_clm
INNER JOIN BCPM_DDBO.vltc916a_pyd c ON b.i_sys_pym = c.i_sys_pym
LEFT OUTER JOIN BCPM_DDBO.vltc913a_avy d ON c.i_sys_avy = d.i_sys_avy

WHERE a.c_pol NOT BETWEEN 'LAC000001' AND 'LAC000999'

) with data primary index(i_sys_clm) on commit preserve rows;

Select *
from GetLocDetail
qualify
rank() over (partition by SorceClaimID order by pmnt_from_dt desc) = 1
Teradata Employee

Re: How to sum records where a particular value changes within a grouping?

You renamed the column, so change the PRIMARY INDEX claus for the volatile table to say SorceClaimID.
Enthusiast

Re: How to sum records where a particular value changes within a grouping?

Disregard that last post!
Enthusiast

Re: How to sum records where a particular value changes within a grouping?

And you were absolutely right :)

Thank you both very much!!!