running total issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

running total issue

Author

Topic

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 13:46:05

I've read several articles I could find regarding various methods for capturing running totals in a query statement, but I'm still having issues.
I need a running total based on the first four columns in the query, but I need the additional columns to appear in the result set. Here's the trimmed down statement:

select
a.employid,
a.year,
a.period,
a.deprtmnt,
a.jobtitle,
a.emplclas,
a.annfactor,
sum(b.annfactor) as RunTotannfactor
from jlanUPR_TrxFutaInfo a
cross join jlanUPR_TrxFutaInfo b
where b.employid=a.employid and b.year=a.year and b.period=a.period and b.jobtitle=a.jobtitle
and b.emplclas=a.emplclas and b.deprtmnt<=a.deprtmnt and a.employid='0765'
group by a.employid,a.year,a.period,a.deprtmnt,a.jobtitle,a.emplclas,a.annfactor
order by a.employid,a.year,a.period,a.deprtmnt,a.jobtitle,a.emplclas,a.annfactor

Here are the results:
0765 2007 1 102103 PT DLNDFTH 1727.68000 1727.68000
0765 2007 1 102103 PT DLNDPRNH 1784.20000 1784.20000
0765 2007 1 128103 PT DLNDFTH 1100.88000 2828.56000
0765 2007 1 128103 PT DLNDPRNH 774.40000 2558.60000
0765 2007 1 128403 PT DLNDPRNH 168.30000 2726.90000
0765 2007 1 185103 PT DLNDFTH 1554.88000 4383.44000
0765 2007 1 185103 PT DLNDPRNH 1601.60000 4328.50000
0765 2007 1 185403 PT DLNDFTH 46.80000 4430.24000
0765 2007 1 352103 PT DLNDFTH 160.80000 4591.04000
0765 2007 1 352103 PT DLNDPRNH 690.25000 5018.75000
0765 2007 1 352403 PT DLNDFTH 940.50000 5531.54000
0765 2007 1 352403 PT DLNDPRNH 3360.96000 8379.71000
0765 2007 2 102103 PT DLNDPRNH 3377.55000 3377.55000
0765 2007 2 128103 PT DLNDPRNH 596.75000 3974.30000
0765 2007 2 185103 PT DLNDPRNH 3793.35000 7767.65000
0765 2007 2 352103 PT DLNDPRNH 1524.05000 9291.70000
0765 2007 2 352403 PT DLNDPRNH 395.18000 9686.88000

You can see that it's starting the count on each unique combination of all six fields, not the first four. I tried taking the 5th and 6th field names out of the where clause but then I get the following results:

0765 2007 1 102103 PT DLNDFTH 1727.68000 3511.88000
0765 2007 1 102103 PT DLNDPRNH 1784.20000 3511.88000
0765 2007 1 128103 PT DLNDFTH 1100.88000 5387.16000
0765 2007 1 128103 PT DLNDPRNH 774.40000 5387.16000
0765 2007 1 128403 PT DLNDPRNH 168.30000 5555.46000
0765 2007 1 185103 PT DLNDFTH 1554.88000 8711.94000
0765 2007 1 185103 PT DLNDPRNH 1601.60000 8711.94000
0765 2007 1 185403 PT DLNDFTH 46.80000 8758.74000
0765 2007 1 352103 PT DLNDFTH 160.80000 9609.79000
0765 2007 1 352103 PT DLNDPRNH 690.25000 9609.79000
0765 2007 1 352403 PT DLNDFTH 940.50000 13911.25000
0765 2007 1 352403 PT DLNDPRNH 3360.96000 13911.25000
0765 2007 2 102103 PT DLNDPRNH 3377.55000 3377.55000
0765 2007 2 128103 PT DLNDPRNH 596.75000 3974.30000
0765 2007 2 185103 PT DLNDPRNH 3793.35000 7767.65000
0765 2007 2 352103 PT DLNDPRNH 1524.05000 9291.70000
0765 2007 2 352403 PT DLNDPRNH 395.18000 9686.88000

That doesn't give me a true running total by line item, which is what I need.

Trying to keep this as simple as possible as I am self taught.
Please help.
Going absolutely crazy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-09 : 14:18:55

How are you outputting these results? Web page? Report? Which technology?

Running totals are much easier to do at the client and much more efficient rather than doing this in SQL Server, unless you need those running total results for further calculations within T-SQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-03-09 : 14:25:06

Try to select the four fields you need and a running total from jlanUPR_TrxFutaInfo into temp table
and then select all fields you need from temp table inner join jlanUPR_TrxFutaInfo

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 14:33:29

I'd like to output the results to a table. The results eventually get pulled back by line item into another table and must be at this detailed level because they are a stepping stone to further calculations.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-09 : 15:27:48

What is the PK of this jlanUPR_TrxFutaInfo table? That is very important to be sure that we are getting unique results with the self-join.

Also -- you gave us results that are close, and then results are that are wrong. How about results that are what you are looking for? I don't quite understand how you want to get a running total on 4 columns, not all 6, without some group'ing happening.

- Jeff
http://weblogs.sqlteam.com/JeffS

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 16:04:11

PK -In order to pull the data from the running total temp table back into the original table for further evaluation, the combination of fields that must be unique are the first six fields; employid, year, period, deprtmnt, jobtitle, emplclas.

What I want is a running total by empoyee, year, period BUT also still need the other fields in the result set in order to link back to the original data. I'm trying to get this:

0765 2007 1 102103 PT DLNDFTH 1727.68 1727.68
0765 2007 1 102103 PT DLNDPRNH 1784.2 3511.88
0765 2007 1 128103 PT DLNDFTH 1100.88 4612.76
0765 2007 1 128103 PT DLNDPRNH 774.4 5387.16
0765 2007 1 128403 PT DLNDPRNH 168.3 5555.46
0765 2007 1 185103 PT DLNDFTH 1554.88 7110.34
0765 2007 1 185103 PT DLNDPRNH 1601.6 8711.94
0765 2007 1 185403 PT DLNDFTH 46.8 8758.74
0765 2007 1 352103 PT DLNDFTH 160.8 8919.54
0765 2007 1 352103 PT DLNDPRNH 690.25 9609.79
0765 2007 1 352403 PT DLNDFTH 940.5 10550.29
0765 2007 1 352403 PT DLNDPRNH 3360.96 13911.25
0765 2007 2 102103 PT DLNDPRNH 3377.55 3377.55
0765 2007 2 128103 PT DLNDPRNH 596.75 3974.3
0765 2007 2 185103 PT DLNDPRNH 3793.35 7767.65
0765 2007 2 352103 PT DLNDPRNH 1524.05 9291.7
0765 2007 2 352403 PT DLNDPRNH 395.18 9686.88

The running total should start over on every change of employee and within each employee on every change of year/period. Look at the 5th row from the bottom for an example of when the period changes and the running total starts over.

There was another posted suggestion to do the running total on just the first three fields to a temp table and then join the temp table data back into the original table. The problem there is that the join is not unique b/c the temp table does not contain the additional fields.

Orig table
employid
year
period
department
jobtitle
class

Temp table
employid
year
period

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-09 : 16:14:21

I still don't understand ... How is your data ordered? From your data and results, it looks like you are asking for this:

"For each combination of employee, year and period, I want to accumulate the running sum of the annfactor column for each row, ordered by dept, title and class."

And that doesn't really make sense. Typically, you accumulate running totals over dates or some some other sequentially orderable column(s), not over things like title or class. Is there another key in the data that you wish to order by? For example, "ChangeDate" or even an identity "ID" or something like that? In order for a relational database to do running totals, there must have a unique ordering on your data, but ordering a running total by dept, title and class doesn't seem to make any sense.

edit: fixed some typos
- Jeff
http://weblogs.sqlteam.com/JeffS

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 16:41:06

Sorry, will get back to you later this evening. Have to go to a client's office. When I get back I will explain the logic.

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 20:32:36

As irregular as it may seem, your assessment is correct.

"For each combination of employee, year and period, I want to accumulate the running sum of the annfactor column for each row, ordered by department, title and class."

Here's the situation. We're dealing with payroll tax expense where the tax is predicated on wage limits. Client has a very sophisticated payroll and general ledger structure. The ERP looks to the combination of department, title and class to identify which GL account gets hit. The problem is that the ERP, after processing all of the data, posts the final numbers to the GL in summary. Client needs the detail for further analysis reporting. So we are essentially working back through the ERP logic to break down the GL distribution back out into the original detail. Since the wage limit is by employee, we need the running total at the employee level by year and period. But we need the department, title and class to remain in tact in the process as those key pieces of information are later used to identify the GL expense account that was affected by each line item. The sub-sorting on department, title and class is extremely important. Right or wrong, when the wage limit is reached in a period the taxable wages are not proportionately distributed to the GL based on department, title and class but rather in alphanumeric order by department, title and class.

I took the sample data we've been using out a few more steps in Excel to demonstrate what's happening, but I can't get the sample data to paste into this message window neatly. Long and short is that in reality there are far better ways to process the tax data, but unfortunately we have to follow the same processes and principles that the ERP uses in order to come up with the same results. Up until now the core data was coming directly from the ERP tables and some custom views/stored procedures used for some preliminary evaluation. That data was then being pulled into Crystal, where it is very simple to do the running total as previously described. The problem is that the run-time on the reports is in the range of hours now based on the volume of data that has to be processed. So we're attempting to push all of the processing back into SQL to be run nightly via DTS package so that on-demand request for data is really on-demand and not hours later. The DTS package has many steps. In this single step I just want to retrieve the running total by line item to throw back into the data set for further calculation.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-09 : 21:10:55

Ok, if that's really what you need, here it is:


select
  a.employid,
  a.year,
  a.period,
  a.deprtmnt,
  a.jobtitle,
  a.emplclas,
  a.annfactor,
  (select sum(annfactor) from jlanUPR_TrxFutaInfo b
        where a.employid = b.employid and
              a.year = b.year and
              a.period = b.period and
              ((b.deprtmnt < a.deprtmnt) OR
               (b.deprtmnt = a.deprtmnt AND b.jobtitle < a.jobtitle) OR
               (b.deprtmnt = a.deprtmnt AND b.jobtitle = a.jobtitle 
                                        AND b.emplclass < a.emplclass) OR
               (b.deprtmnt = a.deprtmnt AND b.jobtitle = a.jobtitle 
                                        AND b.emplclass = a.emplclass AND b.annfactor < a.annfactor)
              ) as RunningTotal
from
  jlanUPR_TrxFutaInfo a
order by
  a.employid, a.year, a.period, a.deprtmnt, a.jobtitle, a.emplclass, a.annfactor

That basically does the trick. it resets the running total on employid/year/period, and essentially runs it sorted by deprmt, jobtitle, emplclass AND annfactor (since that needs to be included too w/o a SUM() formula).

I used a correlated subquery instead of a cross join or derived table, it is a little easier to follow. Any way you slice it, performance will be pretty bad. The key is that big WHERE condition in there, if you can follow it you will see that it makes
sense.

If this still doesn't quite work for you, you MUST submit some a sample CREATE TABLE statement and some sample INSERT statements to create a sample environment for us to work in, along with what results you are looking for based on that sample. It is important for you to provide us with the complete scenerio so that we can all help out; I promise you that if you give us a script we can run to make a small test environment to work with, you will get answers not only quickly but very accurately as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

hanley_l
Starting Member

6 Posts

Posted - 2007-03-09 : 21:42:55

You are the bomb!!

Just one slight adjustment. I removed the annfactor evaluation from the where clause as it was causing some NULL values to be returned in the running total column. Without that, it works perfectly. I can't thank you enough. I'd been staring at this for quite some time.

select
a.employid,
a.year,
a.period,
a.deprtmnt,
a.jobtitle,
a.emplclas,
a.annfactor,
(select sum(annfactor) from jlanUPR_TrxFutaInfo b
where a.employid = b.employid and
a.year = b.year and
a.period = b.period and
((b.deprtmnt < a.deprtmnt) OR
(b.deprtmnt = a.deprtmnt AND b.jobtitle < a.jobtitle) OR
(b.deprtmnt = a.deprtmnt AND b.jobtitle = a.jobtitle
AND b.emplclas < a.emplclas) OR
(b.deprtmnt = a.deprtmnt AND b.jobtitle = a.jobtitle
AND b.emplclas = a.emplclas))
) as RunningTotal
from
jlanUPR_TrxFutaInfo a
order by
a.employid, a.year, a.period, a.deprtmnt, a.jobtitle, a.emplclas, a.annfactor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources