June 25, 2014 at 12:04 pm
pwalter83 (6/25/2014)
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 !!!
Do you understand the ramifications of the warning that Lynn delivered with his code?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2014 at 12:11 pm
Jeff Moden (6/25/2014)
pwalter83 (6/25/2014)
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 !!!
Do you understand the ramifications of the warning that Lynn delivered with his code?
Actually, it's the OPs code with just a few slight modifications.
June 25, 2014 at 12:53 pm
Jeff Moden (6/25/2014)
pwalter83 (6/25/2014)
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 !!!
Do you understand the ramifications of the warning that Lynn delivered with his code?
Thanks Jeff, actually I have thoroughly tested the report and it seems to be running fine. Also, there is not a lot of data involved so should be okay.
June 25, 2014 at 12:59 pm
pwalter83 (6/25/2014)
Jeff Moden (6/25/2014)
pwalter83 (6/25/2014)
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 !!!
Do you understand the ramifications of the warning that Lynn delivered with his code?
Thanks Jeff, actually I have thoroughly tested the report and it seems to be running fine. Also, there is not a lot of data involved so should be okay.
I'll try to be the first to say it, famous last words.
My advice, keep a close eye on it. You never know when an increase in data may cause the SQL to hit a tipping point and performance suddenly goes downhill fast.
June 26, 2014 at 1:59 am
Lynn Pettis (6/25/2014)
pwalter83 (6/25/2014)
Jeff Moden (6/25/2014)
pwalter83 (6/25/2014)
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 !!!
Do you understand the ramifications of the warning that Lynn delivered with his code?
Thanks Jeff, actually I have thoroughly tested the report and it seems to be running fine. Also, there is not a lot of data involved so should be okay.
I'll try to be the first to say it, famous last words.
My advice, keep a close eye on it. You never know when an increase in data may cause the SQL to hit a tipping point and performance suddenly goes downhill fast.
Cheers Lynn !!! I will definitely keep an eye for that. Thanks very much.
June 30, 2014 at 6:10 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.
Hi Lynn,
Thanks a lot for your help last time, however, I am stuck on another issue with the same query.
I need to calculate the remittance ratio using the Running total code you suggested. The formula for the calculation will be as follows:
SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) / Previous(Running total calculated above) *100).
The biggest obstacle to this calculation is to incorporate the previous row for running total and then use it to calculate the remittance ratio. Also, if the denominator is 0, it should not return an error.
Would you be able to help me please in this regard ? This should be the last query for this project. Please find the the query that I have been able to create using your example above but it doesnt work :
----------------------------------------
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] ,
(SELECT 100*(YourSum) AS Expr1
FROM (SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END)/ SUM(CASE WHEN SOA_TYPE IN ('Agent', 'Freight', 'Cost') OR
AC = '16208' THEN USD_AMOUNT ELSE 0 END) AS YourSum
FROM GL AS b
WHERE ([WK NO] < = a.[WK NO]) AND (a.COMP = COMP)) AS t) AS [Remittance Ratio]
FROM #GLT a
GROUP BY [COMP], a.[WK NO]
ORDER BY [COMP], a.[WK NO]
----------------------------------------
Thanks.
July 1, 2014 at 10:04 am
Can somebody please help on this ?
July 1, 2014 at 10:13 am
First, you haven't detailed what exactly is wrong.
Second, what have you done to try and resolve the problem?
Third, how does the running total part of all this fit in or is this a separate calculation?
Fourth,Ii using the same DDL and sample data what is the expected results?
We can only pull so much out of thin air. Also we are volunteers who have our own jobs to do as well. We help when have the time to help. The more information you provide the better help we can provide in return.
July 2, 2014 at 2:28 am
Lynn Pettis (7/1/2014)
First, you haven't detailed what exactly is wrong.Second, what have you done to try and resolve the problem?
Third, how does the running total part of all this fit in or is this a separate calculation?
Fourth,Ii using the same DDL and sample data what is the expected results?
We can only pull so much out of thin air. Also we are volunteers who have our own jobs to do as well. We help when have the time to help. The more information you provide the better help we can provide in return.
Hi Lynn,
Thanks for your reply. Sorry for not explaining the problem in detail.
Please find the answers to your queries.
1. I am trying to calculate Remittance ratio using the formula below:
(SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END)/ Previous row of Running total (which was your solution) * 100
The problem with the above calculation is I cannot get to work out the previous row for the running total using sql. I tried to do this in SSRS using the Previous function but it doesnt work properly as it does not calculate the ratio for the first row. The reason for this is being that the running total is calculated on the sql level itself and the ratio calculation depends on it. So it should also be calculated in sql.
The other issue I need to factor in is the case where denominator could be 0 and in that case it would just error out.
2. I was only able to reach to the point of creating the following query:
(SELECT 100*(YourSum) AS Expr1
FROM (SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END)/ SUM(CASE WHEN SOA_TYPE IN ('Agent', 'Freight', 'Cost') OR
AC = '16208' THEN USD_AMOUNT ELSE 0 END) AS YourSum
FROM GL AS b
WHERE ([WK NO] < = a.[WK NO]) AND (a.COMP = COMP)) AS t) AS [Remittance Ratio]
However as I mentioned above, I could not work out the previous row for running total so still stuck on this.
3. Remittance ratio is based on the running total as I mentioned in point 1.
4. Please find the result set for the sample data attached. The calculation is based on the following calculation:
AUSD / Previous Running total * 100.
As an example - the 3rd row has been calculated using the values as below:
-2667204.23 / 396906.84 (previous running total) *100 = -671%
I hope I have been able to explain the scenario in detail.
Thanks.
July 2, 2014 at 3:42 am
pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...
If your calculation depends upon a value from a previous row, what should the result of the calculation be when there isn't a previous row?
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
July 2, 2014 at 3:48 am
ChrisM@Work (7/2/2014)
pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...If your calculation depends upon a value from a previous row, what should the result of the calculation be when there isn't a previous row?
Thanks.
If there is no previous running total, then the result should be blank. It should also be blank in the case of running total value being zero.
July 2, 2014 at 4:46 am
pwalter83 (7/2/2014)
ChrisM@Work (7/2/2014)
pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...If your calculation depends upon a value from a previous row, what should the result of the calculation be when there isn't a previous row?
Thanks.
If there is no previous running total, then the result should be blank. It should also be blank in the case of running total value being zero.
What does the PREVIOUS function return when there is no previous row?
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
July 2, 2014 at 7:57 am
ChrisM@Work (7/2/2014)
pwalter83 (7/2/2014)
ChrisM@Work (7/2/2014)
pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...If your calculation depends upon a value from a previous row, what should the result of the calculation be when there isn't a previous row?
Thanks.
If there is no previous running total, then the result should be blank. It should also be blank in the case of running total value being zero.
What does the PREVIOUS function return when there is no previous row?
If you referring to the Previous function in SSRS, then it returns a blank. However, as this is a parameter based report (weeks), the report does not display data for the 1st row even if there was a running total balance before it.
For e.g. if the user select weeks from 9 till 12, then the previous function will not return a value for the 9th week row as the running total for 8th week row is not displayed on the report but it does exist in the database.
This is because the previous function calculates results on the fly whereas the running total is being calculated at the sql level. As a result, the previous of running total needs to be calculated at the sql level.
Thanks.
July 2, 2014 at 8:15 am
Again, based on the existing sample data, what are the expected results.
July 2, 2014 at 9:48 am
try this....based on your sample data (I think)
WITH rt_cte 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),
[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]
rn = row_number() over (order by MAX(COMP),a.[WK NO])
FROM #GLT a
GROUP BY [COMP], a.[WK NO] )
SELECT r1.[WK NO]
, r1.AC
, r1.comp
, r1.AUSD
, r1.FUSD
, r1.CUST
, r1.[AGENT not in deal]
, r1.AGENT_notindeal_WKBALANCE
, r1.[WK BALANCE]
, r1.[Running Total]
, CASE WHEN r1.AUSD <> 0 THEN CAST( (r1.AUSD / r2.[Running Total]) * 100 as decimal (10,2)) ELSE 0 END as remitratio
FROM rt_cte AS r1
left outer JOIN rt_cte AS r2
ON r1.rn-1 = r2.rn
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 61 through 75 (of 75 total)
You must be logged in to reply to this topic. Login to reply