June 25, 2014 at 1:52 am
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
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
June 25, 2014 at 2:24 am
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
Thanks but I did not understand your reply. Do you mean the calculation is okay ?
As displayed below, the first row should display a running total of -63807.41 but due to some error in calculation it comes up as -920131.06. And this continues with the second row and so forth. I dont know why this happens with decimal values only.
-------------------------------------------------
WK BALANCE RUNNING TOTAL
-63807.41 -920131.06
-856323.65 -920131.06
460714.25 -459416.81
-2667204.23 -3126621.04
687871.30 -2438749.74
38231.14 -2400518.60
14098.86 -2386419.74
4013.10 -2382406.64
-------------------------------------------------
Thanks.
June 25, 2014 at 2:57 am
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
Thanks but I did not understand your reply. Do you mean the calculation is okay ?
As displayed below, the first row should display a running total of -63807.41 but due to some error in calculation it comes up as -920131.06. And this continues with the second row and so forth. I dont know why this happens with decimal values only.
-------------------------------------------------
WK BALANCE RUNNING TOTAL
-63807.41 -920131.06
-856323.65 -920131.06
460714.25 -459416.81
-2667204.23 -3126621.04
687871.30 -2438749.74
38231.14 -2400518.60
14098.86 -2386419.74
4013.10 -2382406.64
-------------------------------------------------
Thanks.
Paul, please post the values you expect to see for the running totals column. The whole column, not just the first value. Like this:
WK BALANCE WRONG RUNNING TOTAL CORRECT RUNNING TOTAL
-63807.41 -920131.06 0
-856323.65 -920131.06 0
460714.25 -459416.81 0
-2667204.23 -3126621.04 0
687871.30 -2438749.74 0
38231.14 -2400518.60 0
14098.86 -2386419.74 0
4013.10 -2382406.64 0
Replace the zero's in the "CORRECT RUNNING TOTAL" column with the values you would like to have.
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
June 25, 2014 at 3:32 am
ChrisM@Work (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
Thanks but I did not understand your reply. Do you mean the calculation is okay ?
As displayed below, the first row should display a running total of -63807.41 but due to some error in calculation it comes up as -920131.06. And this continues with the second row and so forth. I dont know why this happens with decimal values only.
-------------------------------------------------
WK BALANCE RUNNING TOTAL
-63807.41 -920131.06
-856323.65 -920131.06
460714.25 -459416.81
-2667204.23 -3126621.04
687871.30 -2438749.74
38231.14 -2400518.60
14098.86 -2386419.74
4013.10 -2382406.64
-------------------------------------------------
Thanks.
Paul, please post the values you expect to see for the running totals column. The whole column, not just the first value. Like this:
WK BALANCE WRONG RUNNING TOTAL CORRECT RUNNING TOTAL
-63807.41 -920131.06 0
-856323.65 -920131.06 0
460714.25 -459416.81 0
-2667204.23 -3126621.04 0
687871.30 -2438749.74 0
38231.14 -2400518.60 0
14098.86 -2386419.74 0
4013.10 -2382406.64 0
Replace the zero's in the "CORRECT RUNNING TOTAL" column with the values you would like to have.
Thanks. I expect the running total to be as in the attached file. Please note the running total would start new for each COMPANY.
Thanks.
June 25, 2014 at 3:40 am
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
Thanks but I did not understand your reply. Do you mean the calculation is okay ?
As displayed below, the first row should display a running total of -63807.41 but due to some error in calculation it comes up as -920131.06. And this continues with the second row and so forth. I dont know why this happens with decimal values only.
-------------------------------------------------
WK BALANCE RUNNING TOTAL
-63807.41 -920131.06
-856323.65 -920131.06
460714.25 -459416.81
-2667204.23 -3126621.04
687871.30 -2438749.74
38231.14 -2400518.60
14098.86 -2386419.74
4013.10 -2382406.64
-------------------------------------------------
Thanks.
Paul, please post the values you expect to see for the running totals column. The whole column, not just the first value. Like this:
WK BALANCE WRONG RUNNING TOTAL CORRECT RUNNING TOTAL
-63807.41 -920131.06 0
-856323.65 -920131.06 0
460714.25 -459416.81 0
-2667204.23 -3126621.04 0
687871.30 -2438749.74 0
38231.14 -2400518.60 0
14098.86 -2386419.74 0
4013.10 -2382406.64 0
Replace the zero's in the "CORRECT RUNNING TOTAL" column with the values you would like to have.
Thanks. I expect the running total to be like this:
-----------------------------------
COMPANYWEEKWEEK BALANCERUNNING TOTALCORRECT RUNNING TOTAL
120039-4746150.73-3193528.45-4746150.73
12003105848249.312120271.481102098.58
12003113978540.13912670.775080638.68
1200312-11281250.17-7187146.85-6200611.49
12003131510035.33-8657492.03-4690576.16
120041129679.28 -3193528.4529679.28
1200414358901.772120271.48388581.05
1200415-202004.283912670.77186576.77
120041619253.06 -7187146.85205829.83
120058-527377.44-3193528.45-527377.44
120059200424.982120271.48-326952.46
1200510268731.7 3912670.77-58220.76
1200511-1390424.24-7187146.85-1448645
--------------------------------------------
Please note the running total would start new for each COMPANY.
Thanks.
This doesn't look like it's generated from the sample data. Without a crystal ball, black pointy hat and broomstick I can't generate it. Either 1) amend the sample data so that this result can be obtained from it or 2) amend this result to match what you would expect from the existing sample data. Option 1 is preferable. Come on Paul, work with us on this.
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
June 25, 2014 at 3:48 am
ChrisM@Work (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
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 #GLTCREATE 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.
Thanks Chris.
In the Running total column, it needs to calculate the running total for the WK BALANCE column as shown below (at present the calculation is incorrect):
91620112003-63807.410.000.000.000.00-63807.41-920131.06
91629512004-856323.650.000.000.000.00-856323.65-920131.06
1016204120030.00460714.250.000.000.00460714.25-459416.81
111620712003-2667204.230.000.000.000.00-2667204.23-3126621.04
1216208120030.000.000.000.00687871.30687871.30-2438749.74
1316224120030.000.0038231.140.000.0038231.14-2400518.60
1416295120030.0014098.860.000.000.0014098.86-2386419.74
1516295120030.000.004013.100.000.004013.10-2382406.64
This is the current result set which you say is incorrect - which is ok. It is a running total but it doesn't meet your expectations.
...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.
Thanks but I did not understand your reply. Do you mean the calculation is okay ?
As displayed below, the first row should display a running total of -63807.41 but due to some error in calculation it comes up as -920131.06. And this continues with the second row and so forth. I dont know why this happens with decimal values only.
-------------------------------------------------
WK BALANCE RUNNING TOTAL
-63807.41 -920131.06
-856323.65 -920131.06
460714.25 -459416.81
-2667204.23 -3126621.04
687871.30 -2438749.74
38231.14 -2400518.60
14098.86 -2386419.74
4013.10 -2382406.64
-------------------------------------------------
Thanks.
Paul, please post the values you expect to see for the running totals column. The whole column, not just the first value. Like this:
WK BALANCE WRONG RUNNING TOTAL CORRECT RUNNING TOTAL
-63807.41 -920131.06 0
-856323.65 -920131.06 0
460714.25 -459416.81 0
-2667204.23 -3126621.04 0
687871.30 -2438749.74 0
38231.14 -2400518.60 0
14098.86 -2386419.74 0
4013.10 -2382406.64 0
Replace the zero's in the "CORRECT RUNNING TOTAL" column with the values you would like to have.
Thanks. I expect the running total to be like this:
-----------------------------------
COMPANYWEEKWEEK BALANCERUNNING TOTALCORRECT RUNNING TOTAL
120039-4746150.73-3193528.45-4746150.73
12003105848249.312120271.481102098.58
12003113978540.13912670.775080638.68
1200312-11281250.17-7187146.85-6200611.49
12003131510035.33-8657492.03-4690576.16
120041129679.28 -3193528.4529679.28
1200414358901.772120271.48388581.05
1200415-202004.283912670.77186576.77
120041619253.06 -7187146.85205829.83
120058-527377.44-3193528.45-527377.44
120059200424.982120271.48-326952.46
1200510268731.7 3912670.77-58220.76
1200511-1390424.24-7187146.85-1448645
--------------------------------------------
Please note the running total would start new for each COMPANY.
Thanks.
This doesn't look like it's generated from the sample data. Without a crystal ball, black pointy hat and broomstick I can't generate it. Either 1) amend the sample data so that this result can be obtained from it or 2) amend this result to match what you would expect from the existing sample data. Option 1 is preferable. Come on Paul, work with us on this.
Just wondering, how do I make myself more clear on RUNNING TOTAL, it is what it says RUNNING TOTAL.
2+2 should be 4 and not 5 that is the issue. The expected result should be the correct mathematical result and not what I personally expect.
I will try to work on this myself.
June 25, 2014 at 3:56 am
The issue is a lack of clarity.
Don't give up. Have a look at the result of this:
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')
----------------------------------------------------------------------------------------
;WITH Preaggregate AS (
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),
rn = ROW_NUMBER() OVER(ORDER BY a.[WK NO])--,
--[Wrong Running Total] = (
--SELECT SUM(YourSum)
--FROM ( -- t
--SELECT YourSum = SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END)
--FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]
--) t
--) -- [Running Total]
FROM #GLT a
GROUP BY a.[WK NO], [COMP]
)
SELECT *,
[New Running Total] = (SELECT SUM([WK BALANCE]) FROM Preaggregate b WHERE b.comp = a.comp AND b.rn <= a.rn)
FROM Preaggregate a
ORDER BY a.[WK NO], [COMP]
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
June 25, 2014 at 7:26 am
ChrisM@Work (6/25/2014)
The issue is a lack of clarity.Don't give up. Have a look at the result of this:
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLTCREATE 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')
----------------------------------------------------------------------------------------
;WITH Preaggregate AS (
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),
rn = ROW_NUMBER() OVER(ORDER BY a.[WK NO])--,
--[Wrong Running Total] = (
--SELECT SUM(YourSum)
--FROM ( -- t
--SELECT YourSum = SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END)
--FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]
--) t
--) -- [Running Total]
FROM #GLT a
GROUP BY a.[WK NO], [COMP]
)
SELECT *,
[New Running Total] = (SELECT SUM([WK BALANCE]) FROM Preaggregate b WHERE b.comp = a.comp AND b.rn <= a.rn)
FROM Preaggregate a
ORDER BY a.[WK NO], [COMP]
Thanks Chris, it works but in the same way as the running total function in ssrs. I am still facing the same problem. The attached screenshot would make the issue more clear I suppose.
Thanks.
June 25, 2014 at 8:06 am
EDIT: Looks like Chris found the issue with the part I did. Now to try and figure out the rest.
I found the problem, try the following:
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] and a.COMP = b.COMP
) t
) -- [Running Total]
FROM #GLT a
GROUP BY [COMP], a.[WK NO]
ORDER BY [COMP], a.[WK NO]
This code will not scale well depending on the data and its distribution since it currently has a triangular join in the code.
June 25, 2014 at 8:14 am
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
The issue is a lack of clarity.Don't give up. Have a look at the result of this:
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLTCREATE 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')
----------------------------------------------------------------------------------------
;WITH Preaggregate AS (
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),
rn = ROW_NUMBER() OVER(ORDER BY a.[WK NO])--,
--[Wrong Running Total] = (
--SELECT SUM(YourSum)
--FROM ( -- t
--SELECT YourSum = SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END)
--FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]
--) t
--) -- [Running Total]
FROM #GLT a
GROUP BY a.[WK NO], [COMP]
)
SELECT *,
[New Running Total] = (SELECT SUM([WK BALANCE]) FROM Preaggregate b WHERE b.comp = a.comp AND b.rn <= a.rn)
FROM Preaggregate a
ORDER BY a.[WK NO], [COMP]
Thanks Chris, it works but in the same way as the running total function in ssrs. I am still facing the same problem. The attached screenshot would make the issue more clear I suppose.
Thanks.
Actually, at least for me, the screen shot adds nothing as it doesn't even look like what we are working with. It is like comparing apples and oranges.
June 25, 2014 at 8:20 am
Please explain what it is you are trying to accomplish? Is case 2 trying to show current running totals for the weeks 18 to 23 but include the calculations as if run from week 9 through 23?
If that is the case, your sample data does not support that scenario.
June 25, 2014 at 9:27 am
Lynn Pettis (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
The issue is a lack of clarity.Don't give up. Have a look at the result of this:
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLTCREATE 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')
----------------------------------------------------------------------------------------
;WITH Preaggregate AS (
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),
rn = ROW_NUMBER() OVER(ORDER BY a.[WK NO])--,
--[Wrong Running Total] = (
--SELECT SUM(YourSum)
--FROM ( -- t
--SELECT YourSum = SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END)
--FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]
--) t
--) -- [Running Total]
FROM #GLT a
GROUP BY a.[WK NO], [COMP]
)
SELECT *,
[New Running Total] = (SELECT SUM([WK BALANCE]) FROM Preaggregate b WHERE b.comp = a.comp AND b.rn <= a.rn)
FROM Preaggregate a
ORDER BY a.[WK NO], [COMP]
Thanks Chris, it works but in the same way as the running total function in ssrs. I am still facing the same problem. The attached screenshot would make the issue more clear I suppose.
Thanks.
Actually, at least for me, the screen shot adds nothing as it doesn't even look like what we are working with. It is like comparing apples and oranges.
Dear Lynn,
That doesnt matter anymore as you already nailed it !!!!
Thanks very much for your solution, it worked perfectly !!! I hope you were not burning the midnight oil for this !!! 🙂
June 25, 2014 at 9:28 am
Lynn Pettis (6/25/2014)
Please explain what it is you are trying to accomplish? Is case 2 trying to show current running totals for the weeks 18 to 23 but include the calculations as if run from week 9 through 23?If that is the case, your sample data does not support that scenario.
Well dont need to as its accomplished thanks to you !!!
June 25, 2014 at 9:29 am
Lynn Pettis (6/25/2014)
EDIT: Looks like Chris found the issue with the part I did. Now to try and figure out the rest.I found the problem, try the following:
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] and a.COMP = b.COMP
) t
) -- [Running Total]
FROM #GLT a
GROUP BY [COMP], a.[WK NO]
ORDER BY [COMP], a.[WK NO]
This code will not scale well depending on the data and its distribution since it currently has a triangular join in the code.
Genius !!!
June 25, 2014 at 9:31 am
ChrisM@Work (6/25/2014)
The issue is a lack of clarity.Don't give up. Have a look at the result of this:
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLTCREATE 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')
----------------------------------------------------------------------------------------
;WITH Preaggregate AS (
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),
rn = ROW_NUMBER() OVER(ORDER BY a.[WK NO])--,
--[Wrong Running Total] = (
--SELECT SUM(YourSum)
--FROM ( -- t
--SELECT YourSum = SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END)
--FROM #GLT b WHERE b.[WK NO] < = a.[WK NO]
--) t
--) -- [Running Total]
FROM #GLT a
GROUP BY a.[WK NO], [COMP]
)
SELECT *,
[New Running Total] = (SELECT SUM([WK BALANCE]) FROM Preaggregate b WHERE b.comp = a.comp AND b.rn <= a.rn)
FROM Preaggregate a
ORDER BY a.[WK NO], [COMP]
Thanks a lot, Chris for your brain storming. Your solution came very close.
Viewing 15 posts - 46 through 60 (of 75 total)
You must be logged in to reply to this topic. Login to reply