How to Create a Running Total in SQL? 1

[mktran]

Hi,

I am trying to create a running balance for 12 months, and have created the code below for reference. My query table is based off of a summary table (drill_sum), which totals all activity per month for each account number. This query works correctly, however I would like to include a balance for those months where there is no activity. If you look below, I would like to create a balance for 2008M07 to 2008M10. These are the months that have no activity and are not within the summary table. Thank you in advance for your help. Much appreciated!!

Current Results:

Period entityid acctnum dept jobcode total run. Total
2008M04 3305 1270-0000 0 0 -85.83 -85.83
2008M05 3305 1270-0000 0 0 -85.83 -171.66
2008M06 3305 1270-0000 0 0 -85.84 -257.5
2008M11 3305 1270-0000 0 0 591.38 333.88
2008M12 3305 1270-0000 0 0 -84.49 249.39
2009M01 3305 1270-0000 0 0 -84.48 164.91
2009M02 3305 1270-0000 0 0 -84.48 80.43
2009M03 3305 1270-0000 0 0 -84.48 -4.05


Desired Results:

Period entityid acctnum dept jobcode total run. total
2008M04 3305 1270-0000 0 0 -85.83 -85.83
2008M05 3305 1270-0000 0 0 -85.83 -171.66
2008M06 3305 1270-0000 0 0 -85.84 -257.5

2008M07 3305 1270-0000 0 0 0 -257.5
2008M08 3305 1270-0000 0 0 0 -257.5
2008M09 3305 1270-0000 0 0 0 -257.5
2008M10 3305 1270-0000 0 0 0 -257.5

2008M11 3305 1270-0000 0 0 591.38 333.88
2008M12 3305 1270-0000 0 0 -84.49 249.39
2009M01 3305 1270-0000 0 0 -84.48 164.91
2009M02 3305 1270-0000 0 0 -84.48 80.43
2009M03 3305 1270-0000 0 0 -84.48 -4.05




select a.Period,
a.entityid,
a.acctnum,
a.department,
a.jobcode,
a.total,
(select sum(b.total) from dbo.drill_sum b
where left(b.period,4) + right(b.period, 2) <= left(a.period, 4) + right(a.period,2)
and a.acctnum = b.acctnum
and a.entityid = b.entityid
and a.department = b.department
and a.jobcode = b.jobcode
and b.PERIOD between '2008m04' and '2009m03' ) as 'running total'
FROM dbo.drill_sum a
WHERE a.entityid in ('3305','3662','F506','F523')
and a.period between '2008M04' and '2009M03'
and a.ACCTNUM < '5000-0000'
ORDER BY a.ENTITYID, a.ACCTNUM, a.PERIOD, a.DEPARTMENT, a.jobcode

 

[Qik3Coder]

You need a dates table.
Then you join on this as a base.

The source of your problem is that you do not have a date to tie these records to.

If you wanted to be sneaky, you could probably do a distinct on your date range passed in. This would require you to have some activity with a customer for each month.



You've got questions and source code. We want both!

 

[mktran]

Thanks Qik3Coder!

I have created a Dates table, which holds all of the different periods that I am trying to get a balance for, but I am unsure of how to populate the empty records? Not sure where to go from here.

 

[Qik3Coder]

You have to start from the dates table, with the date range you're interested in. Then you can join in you actual data. This will be for the base and the inner query.

You will have to add an isNull() to the dollar amounts so that the sums work correctly.

This will be along the lines of
FROM dbo.dates d
LEFT JOIN dbo.drill_sum ds ON
d.date = ds.date
...
WHERE d.date between @StartDate and @EndDate

You've got questions and source code. We want both!

 

[mktran]

oooh, thanks!

I tried a left join on the dates table as you have mentioned, and for some reason, it doesnt return the missing dates and only return records for those in my 2nd table (drill sum). I'm not sure what i'm doing wrong or what i'm missing?

Melanie

 

[drlex]

Without seeing your code, I'm guessing that you've probably put a condition on your filter (the bit after the 'WHERE') which has had the effect of turning the LEFT JOIN suggested by Qik3coder into an INNER JOIN.

E.G. If you've added "and a.ACCTNUM < '5000-0000'", then you'd need to amend it to "and (a.ACCTNUM < '5000-0000' or a.ACCTNUM is NULL)" or put those requirements in the join, not the filter.


soi là, soi carré

 

[mktran]

Thanks drlex!! I really appreciate your help! it worked for a specific entity and specific account number in my code below, however, when i tried to include a range of accounts and entities, it does not return records for the months where there is no activity.

Instead of:

(entityid = '3305' or entityid is null) and
(acctnum = '1270-0000' or acctnum is null)

I used:

(entityid in ('3305','3662','F506','F523') or entityid is null)
and (acctnum < '5000-0000' or acctnum is null)


On another note, is there a way to create a running balance for the record where jobcode is 027? I would like to have a running balance for the other 11 months where there is no activity.


select distinct isnull(b.PERIOD, a.period) as 'Period',
b.ENTITYID,
b.ACCTNUM,
b.DEPARTMENT,
b.JOBCODE,
ISNULL(b.TOTAL, 0) as 'Amount',
(select sum(isnull(c.total, 0)) from dbo.drill_sum c
where left(c.period,4) + right(c.period, 2) <= left(a.period, 4) + right(a.period,2)
and c.PERIOD between '2008M04' and '2009M03'
and b.acctnum = c.acctnum
and b.entityid = c.entityid
and b.department = c.department
and b.jobcode = c.jobcode) as 'Running Total'
from dbo.Dates a
left join dbo.drill_sum b
on a.period = b.period and
(entityid = '3305' or entityid is null) and
(acctnum = '1270-0000' or acctnum is null)
where a.period between '2008M04' and '2009M03'

Results:

Period ENTITYID ACCTNUM DEPT JOBCODE Amount Run.Total
2008M04 3305 1270-0000 000 000 -85.83 -85.83
2008M04 3305 1270-0000 000 027 257.50 257.50
2008M05 3305 1270-0000 000 000 -85.83 -171.66
2008M06 3305 1270-0000 000 000 -85.84 -257.50
2008M07 NULL NULL NULL NULL 0.00 NULL
2008M08 NULL NULL NULL NULL 0.00 NULL
2008M09 NULL NULL NULL NULL 0.00 NULL
2008M10 NULL NULL NULL NULL 0.00 NULL
2008M11 3305 1270-0000 000 000 591.38 333.88
2008M12 3305 1270-0000 000 000 -84.49 249.39
2009M01 3305 1270-0000 000 000 -84.48 164.91
2009M02 3305 1270-0000 000 000 -84.48 80.43
2009M03 3305 1270-0000 000 000 -84.48 -4.05