Running Total by period - 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 2005 Forums

Transact-SQL (2005)

Running Total by period

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-25 : 10:12:29

I would like to add another column that totals the year up to each month. The code below gives me everything except NEWCOLUMN in my sample data. I wold like to add NEWCOLUMN if possible to the code below.

The code below is currently a view. If another view could be created to get what I'm looking for that would be fine as well.


SELECT     TOP (100) PERCENT dbo.gbkmut.reknr, SUM(dbo.gbkmut.bdr_hfl) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS 'Year', 
                      MONTH(dbo.gbkmut.datum) AS Month
FROM         dbo.gbkmut LEFT OUTER JOIN
                      dbo.grtbk ON dbo.gbkmut.reknr = dbo.grtbk.reknr
GROUP BY dbo.gbkmut.reknr, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.transtype
HAVING      (dbo.grtbk.bal_vw = 'W') AND (dbo.gbkmut.transtype <> 'V')
ORDER BY 'Year', Month

    reknr      Amount  Bal_vw   Year   Month   NEWCOLUMN
    52502       4000    W       2008    12      6000
    52502	1.5	W	2009	1       1.5
    52502	1.5	W	2009	2       3.0
    52502       2.0     W       2009    3       5.0
    55740	4300	W	2009	1       4300
    55740	246.00	W	2009	2       4545
    55740       200     W       2009    3       4745

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 10:23:25

This looks very simililar to this question:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133304

The only difference seems to be that now you want it in a new colum. Did you ever figure out why cross apply wasn't working for you?

Be One with the Optimizer
TG

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-25 : 10:26:27

The Code I have above solves the question in the link you gave. Now I need to do a running total for the Year.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 11:12:56

umm - ok.

The other topic did provide a running total but didn't limit it to current year since that wasn't requested. But if you don't like that solution there are plenty of others posted here. Have you searched for "running total" ? Here is one of the results:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128565&SearchTerms=running,total

Be One with the Optimizer
TG

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-25 : 13:14:49

I tried this suggestion, but it returns the same value to ExpenseBudget that is in my amount field

SELECT reknr, Month, Year, Amount,
(SELECT SUM(Amount) AS Expr1
FROM dbo.ProfitLossView
WHERE (reknr = b.reknr) AND (Month = b.Month) AND (Year = b.Year) AND (100 * Year + Month <= 100 * b.Year + b.Month))
AS ExpenseBudget
FROM dbo.ProfitLossView AS b

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-25 : 13:23:37

Try this:


SELECT reknr
       ,Month
       ,Year
       ,Amount
       ,(
              SELECT SUM(Amount) AS Expr1
              FROM dbo.ProfitLossView
              WHERE reknr = b.reknr 
             -- AND Month = b.Month 
             -- AND Year = b.Year 
              AND 100 * Year + Month <= 100 * b.Year + b.Month
              ) AS ExpenseBudget
FROM   dbo.ProfitLossView AS b

So, out of curiosity, why didn't you go with the CROSS APPLY variations since you are using 2005?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources