Running total error

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

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

Thanks, I have closed the other thread.

Could you please suggest how to do a Running total on this query below:

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

Thanks.

ChrisM@Work (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

Thanks, I have closed the other thread.

Could you please suggest how to do a Running total on this query below:

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

Thanks.

That's...not really how it works! Can I suggest that you confirm this can't be achieved in SSRS before you resort to TSQL? Two or three folks on this thread appear to be fairly confident that it can. Since it was your original plan and TSQL was plan B, it makes sense.

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

Thanks, I have closed the other thread.

Could you please suggest how to do a Running total on this query below:

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

Thanks.

That's...not really how it works! Can I suggest that you confirm this can't be achieved in SSRS before you resort to TSQL? Two or three folks on this thread appear to be fairly confident that it can. Since it was your original plan and TSQL was plan B, it makes sense.

I expected some solution at the end of this lengthy discussion but this has led to nowhere. I am at the same point where I started.

Jeff Moden (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

Thanks, I have closed the other thread.

Could you please suggest how to do a Running total on this query below:

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

Thanks.

That's...not really how it works! Can I suggest that you confirm this can't be achieved in SSRS before you resort to TSQL? Two or three folks on this thread appear to be fairly confident that it can. Since it was your original plan and TSQL was plan B, it makes sense.

I expected some solution at the end of this lengthy discussion but this has led to nowhere. I am at the same point where I started.

Reading back a bit through this thread, we have unreal data and code that works but it doesn't work on the data. So, let's get back to the basics.

Please see the first link under "Helpful Links" in my signature line below. Provide the CREATE TABLE statements and some readily consumable test data in the manner identified in the article. This running total thing isn't difficult but it's tough to demo without the right kind of data. It would be extremely helpful if you included the CREATE statements for the indexes on the tables, as well, because indexes will determine the route we need to take on this problem.

Also, as I said on the other thread, although the <= criteria will work in the creation of running totals, it could be (and usually is) really bad for performance and resource usage because, in the presence of aggregates, it forms a "Triangular Join" that's a little bit more than half as bad as a full up accidental CROSS JOIN (Cartesian Product). That's a part of the reason why I need to see the data.

Lynn Pettis (6/23/2014)

---

Jeff Moden (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

pwalter83 (6/23/2014)

---

ChrisM@Work (6/23/2014)

---

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something like "No further posts to this thread, please see ..." with a hyperlink to the thread you have chosen.

Thanks, I have closed the other thread.

Could you please suggest how to do a Running total on this query below:

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

Thanks.

That's...not really how it works! Can I suggest that you confirm this can't be achieved in SSRS before you resort to TSQL? Two or three folks on this thread appear to be fairly confident that it can. Since it was your original plan and TSQL was plan B, it makes sense.

I expected some solution at the end of this lengthy discussion but this has led to nowhere. I am at the same point where I started.

Reading back a bit through this thread, we have unreal data and code that works but it doesn't work on the data. So, let's get back to the basics.

Please see the first link under "Helpful Links" in my signature line below. Provide the CREATE TABLE statements and some readily consumable test data in the manner identified in the article. This running total thing isn't difficult but it's tough to demo without the right kind of data. It would be extremely helpful if you included the CREATE statements for the indexes on the tables, as well, because indexes will determine the route we need to take on this problem.

Also, as I said on the other thread, although the <= criteria will work in the creation of running totals, it could be (and usually is) really bad for performance and resource usage because, in the presence of aggregates, it forms a "Triangular Join" that's a little bit more than half as bad as a full up accidental CROSS JOIN (Cartesian Product). That's a part of the reason why I need to see the data.

I agree with Jeff. I have run your code against the sample data you provided and with the exception of being off 540.00 from your expected results (there is no 540.00 for the first entry in your sample data, but the expected results you posted shows 540.00 more for that entry) it works as expected.

ChrisM@Work (6/24/2014)

---

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?

IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT

CREATE TABLE #GLT (

[Sequence] [int] 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 #GLT values

('1','9','16201','12003','-63807.41','Agent'),

('2','10','16204','12003','460714.25','Freight'),

('3','11','16207','12003','-2667204.23','Agent'),

('5','12','16208','12003','687871.30','Costs'),

('6','13','16224','12003','38231.14','Cost'),

('7','14','16295','12003','14098.86','Freight'),

('8','15','16295','12003','4013.10','Cost'),

('9','9','16295','12004','-1544194.95','Agent'),

('10','9','16295','12004','687871.30','Agent')

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

SELECT a.[WK NO],

AC= MAX(AC),

comp= MAX(comp),

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

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

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

[AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

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

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

[Running Total] = (

SELECT SUM(YourSum)

FROM ( -- t

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

FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]

) t

) -- [Running Total]

FROM #GLT a

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

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

pwalter83 (6/24/2014)

---

ChrisM@Work (6/24/2014)

---

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?

IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT

CREATE TABLE #GLT (

[Sequence] [int] 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 #GLT values

('1','9','16201','12003','-63807.41','Agent'),

('2','10','16204','12003','460714.25','Freight'),

('3','11','16207','12003','-2667204.23','Agent'),

('5','12','16208','12003','687871.30','Costs'),

('6','13','16224','12003','38231.14','Cost'),

('7','14','16295','12003','14098.86','Freight'),

('8','15','16295','12003','4013.10','Cost'),

('9','9','16295','12004','-1544194.95','Agent'),

('10','9','16295','12004','687871.30','Agent')

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

SELECT a.[WK NO],

AC= MAX(AC),

comp= MAX(comp),

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

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

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

[AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

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

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

[Running Total] = (

SELECT SUM(YourSum)

FROM ( -- t

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

FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]

) t

) -- [Running Total]

FROM #GLT a

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

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

Thanks.

Yes, its only the running total calculation which is incorrect.

Jeff Moden (6/24/2014)

---

pwalter83 (6/24/2014)

---

ChrisM@Work (6/24/2014)

---

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?

IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT

CREATE TABLE #GLT (

[Sequence] [int] 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 #GLT values

('1','9','16201','12003','-63807.41','Agent'),

('2','10','16204','12003','460714.25','Freight'),

('3','11','16207','12003','-2667204.23','Agent'),

('5','12','16208','12003','687871.30','Costs'),

('6','13','16224','12003','38231.14','Cost'),

('7','14','16295','12003','14098.86','Freight'),

('8','15','16295','12003','4013.10','Cost'),

('9','9','16295','12004','-1544194.95','Agent'),

('10','9','16295','12004','687871.30','Agent')

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

SELECT a.[WK NO],

AC= MAX(AC),

comp= MAX(comp),

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

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

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

[AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

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

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

[Running Total] = (

SELECT SUM(YourSum)

FROM ( -- t

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

FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]

) t

) -- [Running Total]

FROM #GLT a

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

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

Thanks.

Yes, its only the running total calculation which is incorrect.

In that data, you have entries for "Cost" and one entry for "Costs". It that one entry supposed to be just "Cost?

pwalter83 (6/24/2014)

---

Jeff Moden (6/24/2014)

---

pwalter83 (6/24/2014)

---

ChrisM@Work (6/24/2014)

---

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?

IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT

CREATE TABLE #GLT (

[Sequence] [int] 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 #GLT values

('1','9','16201','12003','-63807.41','Agent'),

('2','10','16204','12003','460714.25','Freight'),

('3','11','16207','12003','-2667204.23','Agent'),

('5','12','16208','12003','687871.30','Costs'),

('6','13','16224','12003','38231.14','Cost'),

('7','14','16295','12003','14098.86','Freight'),

('8','15','16295','12003','4013.10','Cost'),

('9','9','16295','12004','-1544194.95','Agent'),

('10','9','16295','12004','687871.30','Agent')

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

SELECT a.[WK NO],

AC= MAX(AC),

comp= MAX(comp),

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

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

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

[AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

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

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

[Running Total] = (

SELECT SUM(YourSum)

FROM ( -- t

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

FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]

) t

) -- [Running Total]

FROM #GLT a

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

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

Thanks.

Yes, its only the running total calculation which is incorrect.

In that data, you have entries for "Cost" and one entry for "Costs". It that one entry supposed to be just "Cost?

Sorry about the typo. it is supposed to be Cost.

Thanks.

ChrisM@Work (6/24/2014)

---

pwalter83 (6/24/2014)

---

Jeff Moden (6/24/2014)

---

pwalter83 (6/24/2014)

---

ChrisM@Work (6/24/2014)

---

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?

IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT

CREATE TABLE #GLT (

[Sequence] [int] 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 #GLT values

('1','9','16201','12003','-63807.41','Agent'),

('2','10','16204','12003','460714.25','Freight'),

('3','11','16207','12003','-2667204.23','Agent'),

('5','12','16208','12003','687871.30','Costs'),

('6','13','16224','12003','38231.14','Cost'),

('7','14','16295','12003','14098.86','Freight'),

('8','15','16295','12003','4013.10','Cost'),

('9','9','16295','12004','-1544194.95','Agent'),

('10','9','16295','12004','687871.30','Agent')

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

SELECT a.[WK NO],

AC= MAX(AC),

comp= MAX(comp),

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

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

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

[AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

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

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

[Running Total] = (

SELECT SUM(YourSum)

FROM ( -- t

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

FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]

) t

) -- [Running Total]

FROM #GLT a

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

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

Thanks.

Yes, its only the running total calculation which is incorrect.

In that data, you have entries for "Cost" and one entry for "Costs". It that one entry supposed to be just "Cost?

Sorry about the typo. it is supposed to be Cost.

Thanks.

Paul, can you show us what you expect to see in the running totals column please? It would be nice if you could do this by adding an extra column to your sample data in the script above. Thanks.