sql finding running total for each record

Question

This is my query

SELECT 
B.name,B[work_date],B.work_day,B[start_week],B.hours,B.prjname,
B.prj_number,
(SELECT SUM(A.hours) FROM  [timesheet] A WHERE A.[local_date]<=B.[local_date] 
 AND B.name=A.name AND A.[start_week]=B.[start_week] 
)cumulative
FROM [timesheet] 
ORDER BY B.name,B.work_date,B.prj_number

so I am getting this

Name    Work_date   Work_day    start_week  hours   Cumulative  PrjName PrjNumber
James   7/13/15     Mon          7/13/15    5.5     12          ProjectA    1
James   7/13/15     Mon          7/13/15    4.25    12          Project B   2
James   7/13/15     Mon          7/13/15    2.25    12          Project C   3

I did not complete, I am sorry I wanted to add that is you see the cumulative, its adding all the 3 row, instead of summing each row.

Please see my comment on the last line. Please, I need suggestions asap. The cumulative should be summing each row Like .. for first row it should be 5.5 and second row 9.75 and third 12.00 — Nisha Nethani, Jul 24 '15 at 18:15
Seems you need a running total. What version of sql server are you using? If you need help so quickly it would greatly aid your cause if you posted ddl and sample in a consumable format. Assuming you are using 2012 or later you should look into LAG. There are thousands of examples of doing running totals. — Sean Lange, Jul 24 '15 at 18:25
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Tab Alleman, Jul 24 '15 at 18:40

score 0 · Answer 1 · answered Jul 24 '15 at 17:49

Consider using analytic instead of the subquery.

Updates made:

added . after b in many of the selects statements.
used an analytic to get the total for each date/name grouping
Used convert to just get the date part of work_date incase it had a time component.

.

SELECT B.name,
       B.[work_date],
       B.work_day,
       B.[start_week],
       B.hours,
       B.prjname,
       B.prj_number, 
       SUM(A.hours) over (partition by B.name,CONVERT(date, B.[work_date])) 
cumulative
FROM [timesheet] 
ORDER BY B.name,
         B.work_date,
         B.prj_number

with the version of sql I have I cannot use partition. – Nisha Nethani Jul 24 '15 at 19:03 — Nisha Nethani, Jul 24 '15 at 19:03

score 0 · Answer 2 · answered Jul 24 '15 at 20:14

I believe this should work for you:

SELECT 
     q1.[NAME]
    ,q1.[work_date]
    ,q1.[work_day]
    ,q1.[start_week]
    ,q1.[hours]
    ,q1.[prjname]
    ,q1.[prj_number]
    ,sum(q2.hours) as cumulative

FROM
[timesheet] q1
JOIN timesheet q2 on q1.prj_number+1 > q2.prj_number --the trick is right here 
GROUP BY 
     q1.[NAME]
    ,q1.[work_date]
    ,q1.[work_day]
    ,q1.[start_week]
    ,q1.[hours]
    ,q1.[prjname]
    ,q1.[prj_number]
ORDER BY q1.[name], 
         q1.[work_date],
         q1.[prj_number];

SQL Fiddle Demo

sql finding running total for each record

2 Answers2