Running total error

Question

Post reply

Running total error

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Bear with me here but is this the result you are after?

😎

MyLongDesiredRunningTotalSequenceWK NOACCOMPUSD_AMOUNTSOA_TYPE

100.00191620112003100.00Agent

300.002101620412003200.00Freight

450.003111620712003150.00Agent

700.005121620812003250.00Costs

1100.006131622412003400.00Cost

1600.007141629512003500.00Freight

2140.008151629512003540.00Cost

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Eirikur Eiriksson (6/20/2014)
Bear with me here but is this the result you are after?
😎
MyLongDesiredRunningTotalSequenceWK NOACCOMPUSD_AMOUNTSOA_TYPE
100.00191620112003100.00Agent
300.002101620412003200.00Freight
450.003111620712003150.00Agent
700.005121620812003250.00Costs
1100.006131622412003400.00Cost
1600.007141629512003500.00Freight
2140.008151629512003540.00Cost

Yes, this is exactly the result I am looking for. Could you please what query you used to get to this ?

Thanks.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Can I ask what is not working in the SSRS RunningTotal, it may just be a scope problem?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

pwalter83 (6/20/2014)
I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on the fly calculation).

What's happening in the SSRS that you're getting the incorrect value? Going to SQL for Running Totals is tossing the screwdriver aside to grab the sledgehammer to drive the screw.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Take a look at this article, How to Return Running Product Totals.

I think the method I used earlier has already been posted on this thread, it is the same as in the Listings 1. in the above mentioned article.

😎

USE tempdb;

GO

SELECT

G1.Sequence AS Sequence

,MIN(G1.[WK NO]) AS [WK NO]

,MIN(G1.AC) AS AC

,MIN(G1.COMP) AS COMP

,SUM(G2.USD_AMOUNT) AS USD_RT

,MIN(G1.SOA_TYPE) AS SOA_TYPE

FROM dbo.GL G1

INNER JOIN dbo.GL G2

ON G1.Sequence >= G2.Sequence

GROUP BY G1.Sequence

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
I dont understand the problem here.Your query seems to be achieving it.
CREATE TABLE [dbo].[GL](
[Sequence] [int] NOT NULL,
[WK NO] [int] NULL,
[AC] [nvarchar](10) NULL,
[COMP] [nvarchar](10) NULL,
[USD_AMOUNT] [numeric](16, 2) NULL,
[SOA_TYPE] [nvarchar](20) NULL
) ON [PRIMARY]
insert into GL
values('1','9','16201','12003','100','Agent')
insert into GL
values('2','10','16204','12003','200','Freight')
insert into GL
values('3','11','16207','12003','150','Agent')
insert into GL
values('5','12','16208','12003','250','Costs')
insert into GL
values('6','13','16224','12003','400','Cost')
insert into GL
values('7','14','16295','12003','500','Freight')
insert into GL
values('8','15','16295','12003','540','Cost')
GO
SELECT
(SELECT SUM(YourSum) FROM
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]
FROM GL a
GROUP BY a.[WK NO], a.COMP
ORDER BY a.[WK NO], a.COMP
No its not working, I just checked it. This is the resultset that I get:
WKNO AUSD FUSD CUST agent_.. WK BAL Running Total
9100.000.000.000.00100.001552722.28
100.00200.000.000.00200.006866522.21
11150.000.000.000.00150.008658921.50
120.000.000.00250.00250.00-2440896.12
130.000.00400.000.00400.00-3911241.30
140.00500.000.000.00500.004083099.88
150.000.00540.000.00540.009459770.45
The Running total is all incorrect. I dont why its showing values in millions and decimals. Do you have any idea ?
Thanks.

Did you even run the query ?

This is the result set coming out from the sample data provided.

100.00

300.00

450.00

700.00

1100.00

1600.00

2140.00

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 7

Eirikur Eiriksson (6/20/2014)
Take a look at this article, How to Return Running Product Totals.
I think the method I used earlier has already been posted on this thread, it is the same as in the Listings 1. in the above mentioned article.
😎
USE tempdb;
GO
SELECT
G1.Sequence AS Sequence
,MIN(G1.[WK NO]) AS [WK NO]
,MIN(G1.AC) AS AC
,MIN(G1.COMP) AS COMP
,SUM(G2.USD_AMOUNT) AS USD_RT
,MIN(G1.SOA_TYPE) AS SOA_TYPE
FROM dbo.GL G1
INNER JOIN dbo.GL G2
ON G1.Sequence >= G2.Sequence
GROUP BY G1.Sequence

Thanks for your suggestion but the query that I hve to use to get the result is not that simple. I need to achieve the above result using the below query (because it involves a SUM of the SUM and then calculating the running total):

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

SELECT a.[WK NO],

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) AS AUSD,

SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) AS FUSD,

SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) AS CUST,

SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) AS AGENT_notindeal_WKBALANCE,

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) as [WK BALANCE],

(SELECT SUM(YourSum) FROM

(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 1 END) YourSum

FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]

FROM GL a

GROUP BY a.[WK NO], a.COMP

ORDER BY a.[WK NO], a.COMP

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

Thanks.

pwalter83 SSChampion Points: 14575 More actions · Answer 8

Sachin Nandanwar (6/20/2014)
pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
I dont understand the problem here.Your query seems to be achieving it.
CREATE TABLE [dbo].[GL](
[Sequence] [int] NOT NULL,
[WK NO] [int] NULL,
[AC] [nvarchar](10) NULL,
[COMP] [nvarchar](10) NULL,
[USD_AMOUNT] [numeric](16, 2) NULL,
[SOA_TYPE] [nvarchar](20) NULL
) ON [PRIMARY]
insert into GL
values('1','9','16201','12003','100','Agent')
insert into GL
values('2','10','16204','12003','200','Freight')
insert into GL
values('3','11','16207','12003','150','Agent')
insert into GL
values('5','12','16208','12003','250','Costs')
insert into GL
values('6','13','16224','12003','400','Cost')
insert into GL
values('7','14','16295','12003','500','Freight')
insert into GL
values('8','15','16295','12003','540','Cost')
GO
SELECT
(SELECT SUM(YourSum) FROM
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]
FROM GL a
GROUP BY a.[WK NO], a.COMP
ORDER BY a.[WK NO], a.COMP
No its not working, I just checked it. This is the resultset that I get:
WKNO AUSD FUSD CUST agent_.. WK BAL Running Total
9100.000.000.000.00100.001552722.28
100.00200.000.000.00200.006866522.21
11150.000.000.000.00150.008658921.50
120.000.000.00250.00250.00-2440896.12
130.000.00400.000.00400.00-3911241.30
140.00500.000.000.00500.004083099.88
150.000.00540.000.00540.009459770.45
The Running total is all incorrect. I dont why its showing values in millions and decimals. Do you have any idea ?
Thanks.
Did you even run the query ?
This is the result set coming out from the sample data provided.
100.00
300.00
450.00
700.00
1100.00
1600.00
2140.00

I ran the query and that is how I got the result. As mentioned earlier I need to use the following query to get the desired resultset. I think that is where the problem is:

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

SELECT a.[WK NO],

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) AS AUSD,

SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) AS FUSD,

SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) AS CUST,

SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) AS AGENT_notindeal_WKBALANCE,

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) as [WK BALANCE],

(SELECT SUM(YourSum) FROM

(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208' THEN USD_AMOUNT ELSE 1 END) YourSum

FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]

FROM GL a

GROUP BY a.[WK NO], a.COMP

ORDER BY a.[WK NO], a.COMP

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

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

pwalter83 (6/23/2014)

... I need to use the following query to get the desired resultset. I think that is where the problem is

If the query you "need to use" doesn't work and you can't perform a sum of a sum (without using derived tables or CTE's), then what are you left with? Break the groundhog day mental cycle - use a different method. You've been working on this for about four days now Paul. Cut the dogma and the time-wasting guesses and give the folks around here a chance to do what they do. All you have to do is provide a decent sample data set (which works) and is representative of your real data - and an adequate description including a result set.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pwalter83 SSChampion Points: 14575 More actions · Answer 10

ChrisM@Work (6/23/2014)

pwalter83 (6/23/2014)
... I need to use the following query to get the desired resultset. I think that is where the problem is
If the query you "need to use" doesn't work and you can't perform a sum of a sum (without using derived tables or CTE's), then what are you left with? Break the groundhog day mental cycle - use a different method. You've been working on this for about four days now Paul. Cut the dogma and the time-wasting guesses and give the folks around here a chance to do what they do. All you have to do is provide a decent sample data set (which works) and is representative of your real data - and an adequate description including a result set.

I have already done this in my earlier post. Please find the same below:

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

CREATE TABLE [dbo].[GL](

[Sequence] [int] NOT NULL,

[WK NO] [int] NULL,

[AC] [nvarchar](10) NULL,

[COMP] [nvarchar](10) NULL,

[USD_AMOUNT] [numeric](16, 2) NULL,

[SOA_TYPE] [nvarchar](20) NULL

) ON [PRIMARY]

insert into GL

values('1','9','16201','12003','100','Agent')

insert into GL

values('2','10','16204','12003','200','Freight')

insert into GL

values('3','11','16207','12003','150','Agent')

insert into GL

values('5','12','16208','12003','250','Costs')

insert into GL

values('6','13','16224','12003','400','Cost')

insert into GL

values('7','14','16295','12003','500','Freight')

insert into GL

values('8','15','16295','12003','540','Cost')

GO

SELECT a.[WK NO],

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) AS AUSD,

SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) AS FUSD,

SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) AS CUST,

SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) AS AGENT_notindeal_WKBALANCE,

SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END) as [WK BALANCE],

(SELECT SUM(YourSum) FROM

(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208' THEN USD_AMOUNT ELSE 1 END) YourSum

FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]

FROM GLT a

GROUP BY a.[WK NO], a.COMP

ORDER BY a.[WK NO], a.COMP

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

Thanks.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

mister.magoo (6/20/2014)
Can I ask what is not working in the SSRS RunningTotal, it may just be a scope problem?

Evil Kraig F (6/20/2014)
pwalter83 (6/20/2014)
I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on the fly calculation).
What's happening in the SSRS that you're getting the incorrect value? Going to SQL for Running Totals is tossing the screwdriver aside to grab the sledgehammer to drive the screw.

Can you please explain what is going wrong with your SSRS report?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

pwalter83 (6/23/2014)

ChrisM@Work (6/23/2014)
pwalter83 (6/23/2014)
... I need to use the following query to get the desired resultset. I think that is where the problem is
If the query you "need to use" doesn't work and you can't perform a sum of a sum (without using derived tables or CTE's), then what are you left with? Break the groundhog day mental cycle - use a different method. You've been working on this for about four days now Paul. Cut the dogma and the time-wasting guesses and give the folks around here a chance to do what they do. All you have to do is provide a decent sample data set (which works) and is representative of your real data - and an adequate description including a result set.
I have already done this in my earlier post. Please find the same below:
.
.
.

Seven rows might be sufficient. Can you provide your expected result from this sample data? Is the end result an update to a table or an output set from a query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 13

Is the output for RunningTotal right based on the sample data you have provided ? If not then what it should be ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 14

mister.magoo (6/23/2014)
mister.magoo (6/20/2014)
Can I ask what is not working in the SSRS RunningTotal, it may just be a scope problem?
Evil Kraig F (6/20/2014)
pwalter83 (6/20/2014)
I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on the fly calculation).
What's happening in the SSRS that you're getting the incorrect value? Going to SQL for Running Totals is tossing the screwdriver aside to grab the sledgehammer to drive the screw.
Can you please explain what is going wrong with your SSRS report?

I had initially used the calculation within ssrs but it does not get the desired result. This is a parameter based report with the start and end week values. What happens is the running totals calculation begins from the start week (selected by the user) and do not account for the calculation before this week which is incorrect. For eg. if there was a running total balance till end of week 201410. This is does not show up if I select the start week as 201411. It just begins a new running total balance.

Thanks.

pwalter83 SSChampion Points: 14575 More actions · Answer 15

ChrisM@Work (6/23/2014)

pwalter83 (6/23/2014)
ChrisM@Work (6/23/2014)
pwalter83 (6/23/2014)
... I need to use the following query to get the desired resultset. I think that is where the problem is
If the query you "need to use" doesn't work and you can't perform a sum of a sum (without using derived tables or CTE's), then what are you left with? Break the groundhog day mental cycle - use a different method. You've been working on this for about four days now Paul. Cut the dogma and the time-wasting guesses and give the folks around here a chance to do what they do. All you have to do is provide a decent sample data set (which works) and is representative of your real data - and an adequate description including a result set.
I have already done this in my earlier post. Please find the same below:
.
.
.
Seven rows might be sufficient. Can you provide your expected result from this sample data? Is the end result an update to a table or an output set from a query?

Thanks Chris,

The expected result would be as follows by the using the query mentioned:

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

Running total

9100.000.00540.000.00640.00640.00

100.00200.000.000.00200.00840.00

11150.000.000.000.00150.00990.00

120.000.000.00250.00250.001240.00

130.000.00400.000.00400.001640.00

140.00500.000.000.00500.002140.00

150.000.00540.000.00540.002680.00

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

Bu the result I am getting is:

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

Running totals

9100.000.00540.000.00640.001552722.28

100.00200.000.000.00200.006866522.21

11150.000.000.000.00150.008658921.50

120.000.000.00250.00250.00-2440896.12

130.000.00400.000.00400.00-3911241.30

140.00500.000.000.00500.004083099.88

150.000.00540.000.00540.009459770.45

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

I dont know why I am getting the total in millions and in decimal values.

Thanks.