Running total by ID# 1

[Ebes1099]

I'm trying to calc a running total by each person in my table. I would like to do this for the Paid column.

Here are the field I have in my table Date, ID, Billed, Paid.

This is the code I'm using. Right now it's calculating a total for each ID and putting that number in each line. (e.g. If ID=1 had 20 in Jan and 20 in Feb, it would put 40 for the running total in Jan and Feb. I would like it to be 20 in Jan and 40 in Feb.)

What am I doing wrong here?

Btw, I'm using 2008. I've read there's some nice handling for this in 2012 but I don't have that. Thanks.

SELECT 
       t1.[Date]
      ,t1.[ID]
      ,t1.[Billed]
      ,t1.[Paid]
      ,SUM(t2.[New_Mbr_Med_CS]) as 'Ttl_Med_CS'
 FROM #localtemp As t1
 INNER JOIN #localtemp as t2
 ON t1.[ID] = t2.[ID]
 GROUP BY
       t1.[Date]
      ,t1.[ID]
      ,t1.[Billed]
      ,t1.[Paid]
  ORDER BY
	t1.[ID]
	,t1.[Date]

 

[Ebes1099]

I was informed that we had 1 machine running SQL 2012 so I'm trying this code but getting an error on it.

  SELECT
	a.[DATE]
	,a.[ID]
	,a.[Paid]
	,SUM(a.[Paid]) OVER(PARTITION BY a.[ID] ORDER BY a.[ID], a.[DATE]) as 'Ttl'
FROM #localtemp a
ORDER BY
	a.[ID]
	,a.[DATE]

I'm getting this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'order'.

 

[JohnDuncanTB]

This is old school but should still work.

SELECT 
       t1.[Date]
      ,t1.[ID]
      ,t1.[Billed]
      ,t1.[Paid]
      ,(SELECT SUM(t2.[New_Mbr_Med_CS]) FROM #localtemp as t2 WHERE t2.[ID] = t1.[ID] AND t2.[Date] <= t1.[Date]) as 'Ttl_Med_CS'
 FROM #localtemp As t1
  ORDER BY
	t1.[ID]
	,t1.[Date]

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 

[Ebes1099]

That works great! Thanks.

Now I've got something a little more confusing but in this same mold.

Is there a good way to conditionally add values into different buckets?

Example:
If RunningTotal < Level1 then Add 100% of billed into Field1
Else If RunningTotal >Level1 AND <Level2 then Add Val% of Billed into Field1 and (1-Val)% of Billed into Field2
Else if Running Total >Level2 then Add 100% Billed into Field2

Ideally I would like Level1, Level2 and Val to be user adjustable variables. Can I use the @Level1, @Level2, @Val for these and define them at the top?

 

[Ebes1099]

I hate how you can't edit posts on here. At least not that I can find.

So this is actually more complicated than I thought. I've put an example below but I'll try and explain it in words too. I start with a Billed amount. 100% of that goes into the member bucket until the Member Cumulative cost hits Limit 1. In this case limit 1 is $750. Between Limit 1 and Limit 2 ($1500), 20% of billed goes into Member and 80% of billed goes into Plan. Once the Member Cumulative cost hits Limit 2, 100% of Billed goes into Plan.

Here's an example
Month -- ID --- Billed -- Plan_Month -- Plan_Yr_Ttl -- Mbr_Month -- Mbr_Yr_Ttl
Jan ---- 100 --- 500 --------- 0 ------------ 0 ----------- 500 --------- 500
Feb ---- 100 --- 300 -------- 240 ----------- 240 --------- 60 --------- 560
Mar ---- 100 --- 400 -------- 320 ----------- 560 --------- 80 --------- 640
Apr ---- 100 --- 800 -------- 690 ----------- 1250 --------- 110 --------- 750 *Limit 1 is $750 - 20% would have hit 800 so 50 subtracted and added to Plan
May ---- 100 --- 1000 ------- 800 ----------- 2050 --------- 200 --------- 950
Jun ---- 100 --- 600 -------- 480 ----------- 2530 --------- 120 --------- 1070
Jul ---- 100 ---- 700 -------- 560 ----------- 3090 --------- 140 --------- 1210
Aug ---- 100 --- 800 -------- 640 ----------- 3730 --------- 160 --------- 1370
Sep ---- 100 --- 800 -------- 670 ----------- 4400 --------- 160 --------- 1500 *Limit 2 is $1500, 20% would have hit $1550 so 30 subtracted and added to Plan
Oct ---- 100 --- 200 -------- 200 ----------- 4600 --------- 0 --------- 1500
Nov ---- 100 ---- 0 ---------- 0 ------------ 4600 --------- 0 --------- 1500
Dec ---- 100 --- 100 -------- 100 ----------- 4700 --------- 0 --------- 1500

 

[SQLScholar]

Just to get your code working. Can you try:

SELECT
	a.[DATE]
	,a.[ID]
	,a.[Paid]
	,SUM(a.[Paid]) OVER(PARTITION BY a.[ID]) as 'Ttl'
FROM #localtemp a
ORDER BY
	a.[ID]
	,a.[DATE]

I dont think you can order a partioned sum. Doesnt make much sense if you can. Usually this is if you are doing things like row_number.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------