Running total
-
I've got a complex query i'm trying to write. I want to create a running total column of a calculation( column1 - column2).
In the attachment, you will see:
column H = (G-F)
Column I is the Running total
Column J is the same as column H (Column G - Column F)
When the Running total (Column I is Greater than Column L (static value), then Column J = (Column L - Column K) (from the previous record)
-
GrassHopper (11/23/2014)
I've got a complex query i'm trying to write. I want to create a running total column of a calculation( column1 - column2).In the attachment, you will see:
column H = (G-F)
Column I is the Running total
Column J is the same as column H (Column G - Column F)
When the Running total (Column I is Greater than Column L (static value), then Column J = (Column L - Column K) (from the previous record)
First, the JPG is a nice visual but it doesn't help much other than that. Please see the article at the first link under "Helpful Links" in my signature line below for the best way to post some sample data to help us help you. Thanks.
Second, this has been posted under "General". What version of SQL Server are you using? It'll make real difference here.
Third, which column absolutely controls the [font="Arial Black"]order [/font]that the running total should be calculated by.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am using SQL Server 2008. Line Number controls the order. Here is some test data to use for my example:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable(
[GroupID] [nvarchar](50) NULL,
[Line Number] [int] NULL,
[From] [decimal](18, 4) NULL,
[To] [decimal](18, 4) NULL,
[ID] [int] NOT NULL,
--[ID] [int] identity(1,1) NOT NULL,
CONSTRAINT [PK_ID_DealEntry] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE #mytable ADD DEFAULT ((0)) FOR [From]
GO
ALTER TABLE #mytable ADD DEFAULT ((0)) FOR [To]
GO
Select 'SELECT '
+ QUOTENAME(GroupID, '''')+','
+ QUOTENAME([Line Number], '''')+','
+ QUOTENAME([From], '''')+','
+ QUOTENAME([To], '''')+','
+ QUOTENAME([ID], '''')+','
+ ' UNION ALL'
FROM [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
Insert Into #mytable (GroupID,[Line Number], [from], [to],ID )
SELECT '110300162','1','0.0000','1000.0000','1' UNION ALL
SELECT '110300162','2','1001.0000','500000.0000','2' UNION ALL
SELECT '110300162','3','5000001.0000','5000060.0000','3' UNION ALL
SELECT '110300162','4','5000061.0000','10000000000.0000','4' UNION ALL
SELECT '110300163','1','0.0000','10000.0000','5' UNION ALL
SELECT '110300163','2','10001.0000','500000000000.0000','10' UNION ALL
SELECT '110300163','3','0.0000','0.0000','11' UNION ALL
SELECT '110300163','4','0.0000','0.0000','12' UNION ALL
SELECT '110300168','1','0.0000','10000000000.0000','13' UNION ALL
SELECT '110300169','1','0.0000','10000000000.0000','14' UNION ALL
SELECT '110300169','2','0.0000','0.0000','15' UNION ALL
SELECT '110300170','1','0.0000','10000000000.0000','16' UNION ALL
SELECT '110300171','1','0.0000','10000000000.0000','17' UNION ALL
SELECT '110300172','1','0.0000','1000.0000','18' UNION ALL
SELECT '110300172','2','1001.0000','5000000.0000','26' UNION ALL
SELECT '110300172','3','5000001.0000','1000000000.0000','27' UNION ALL
SELECT '110300173','1','0.0000','10000000000.0000','28' UNION ALL
SELECT '110300182','1','0.0000','10000000000.0000','29' UNION ALL
SELECT '110300187','1','0.0000','10000000000.0000','30' UNION ALL
SELECT '110300188','1','0.0000','0.0000','31' UNION ALL
SELECT '110300199','1','0.0000','10000000000.0000','32' UNION ALL
SELECT '110300207','1','0.0000','10000000000.0000','33' UNION ALL
SELECT '110300210','1','0.0000','0.0000','34' UNION ALL
SELECT '110300222','1','0.0000','10000000000.0000','35' UNION ALL
SELECT '110300222','2','0.0000','0.0000','36' UNION ALL
SELECT '110300228','1','0.0000','10000000000.0000','37' UNION ALL
SELECT '110300239','1','0.0000','10000000000.0000','38' UNION ALL
SELECT '110300239','1','0.0000','10000000000.0000','39' UNION ALL
SELECT '110300240','1','0.0000','1000.0000','40' UNION ALL
SELECT '110300240','2','1001.0000','50000.0000','41' UNION ALL
SELECT '110300240','3','50001.0000','800000000.0000','42' UNION ALL
SELECT '110300247','1','0.0000','10000000000.0000','43' UNION ALL
SELECT '110300248','1','0.0000','10000000000.0000','44' UNION ALL
SELECT '110300256','1','0.0000','10000000000.0000','46' UNION ALL
SELECT '110300259','1','0.0000','1000.0000','48' UNION ALL
SELECT '110300259','2','1001.0000','50000.0000','49' UNION ALL
SELECT '110300259','3','50001.0000','8000000000.0000','50' UNION ALL
SELECT '110300260','1','0.0000','10000000000.0000','51' UNION ALL
SELECT '110300261','1','0.0000','10000000000.0000','52' UNION ALL
SELECT '110300262','1','0.0000','1000.0000','54' UNION ALL
SELECT '110300262','2','1001.0000','5000000000.0000','55' UNION ALL
SELECT '110300263','1','0.0000','0.0000','56' UNION ALL
SELECT '110300264','1','0.0000','100000000000.0000','57' UNION ALL
SELECT '110300265','1','0.0000','0.0000','58' UNION ALL
SELECT '110300266','1','0.0000','0.0000','59' UNION ALL
SELECT '110300270','1','0.0000','10000000000.0000','60' UNION ALL
SELECT '110300274','1','0.0000','1000.0000','61' UNION ALL
SELECT '110300274','2','1001.0000','5000.0000','62' UNION ALL
SELECT '110300274','3','5001.0000','50000.0000','63' UNION ALL
SELECT '110300274','4','50001.0000','100000000.0000','64' UNION ALL
SELECT '110300275','1','0.0000','10000000000.0000','65' UNION ALL
SELECT '110300275','2','0.0000','0.0000','66' UNION ALL
SELECT '110300275','3','0.0000','0.0000','67' UNION ALL
SELECT '110300276','1','0.0000','10000000000.0000','68' UNION ALL
SELECT '110300276','2','0.0000','0.0000','69' UNION ALL
SELECT '110300277','1','0.0000','10000000.0000','70' UNION ALL
SELECT '110300277','2','10000000.0100','1000000000.0000','71' UNION ALL
SELECT '110300284','1','0.0000','10000000000.0000','72' UNION ALL
SELECT '110300287','1','0.0000','5000000.0000','73' UNION ALL
SELECT '110300287','2','5000000.0100','100000000.0000','74' UNION ALL
SELECT '110300289','1','0.0000','10000000000.0000','75' UNION ALL
SELECT '110300297','1','0.0000','10000000000.0000','76' UNION ALL
SELECT '110300298','1','0.0000','10000000.0000','77' UNION ALL
SELECT '110300298','2','10000000.0100','100000000.0000','78' UNION ALL
SELECT '110300336','1','0.0000','10000000000.0000','79' UNION ALL
SELECT '110300336','2','0.0000','0.0000','80' UNION ALL
SELECT '110300336','3','0.0000','0.0000','81' UNION ALL
SELECT '110300342','1','0.0000','0.0000','82' UNION ALL
SELECT '110300343','1','0.0000','0.0000','83' UNION ALL
SELECT '110300349','1','0.0000','10000000000.0000','84' UNION ALL
SELECT '110300353','1','0.0000','10000000000.0000','85' UNION ALL
SELECT '110300361','1','0.0000','10000000000.0000','86' UNION ALL
SELECT '110300361','2','0.0000','100000.0000','87' UNION ALL
SELECT '110300361','3','100000.0000','5000000.0000','88' UNION ALL
SELECT '110300361','4','0.0000','0.0000','89' UNION ALL
SELECT '110300361','5','0.0000','0.0000','90' UNION ALL
SELECT '110300361','6','0.0000','0.0000','91' UNION ALL
SELECT '110300274','5','0.0000','0.0000','92' UNION ALL
SELECT '110300298','3','0.0000','0.0000','93' UNION ALL
SELECT '110300298','4','0.0000','0.0000','94' UNION ALL
SELECT '110300298','5','0.0000','0.0000','95' UNION ALL
SELECT '110300298','6','0.0000','0.0000','96' UNION ALL
SELECT '110300298','7','0.0000','0.0000','97' UNION ALL
SELECT '110300298','8','0.0000','0.0000','98' UNION ALL
SELECT '110300298','9','0.0000','0.0000','99' UNION ALL
SELECT '110300298','10','0.0000','0.0000','100' UNION ALL
SELECT '110300298','11','0.0000','0.0000','101' UNION ALL
SELECT '110300274','6','0.0000','0.0000','102' UNION ALL
SELECT '110300274','7','100000.0000','1000000.0000','103'
and the volume used is a sum of a table that comes out to be :
324820.80
-
If you use windowing functions, this isn't that hard. Since I didn't understand how the create table/insert statements were related to your question, this is a somewhat generic answer.
It seems that you need a couple of windowing functions. To do a running total, you need something like this:
SELECT empID, ordermonth, qty,SUM(qty) OVER(PARTITION BY empID
ORDER BY orderMonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runQty
FROM sales.EmpOrders;
To refer to Previous Record: (from Itzik Ben-Gan's book) (p.39)
SELECT empid, ordermonth,qty AS currentQty,
MAX(qty) OVER (PARTITION BY empid
ORDER by orderMonth
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS prevQty,
MAX(qty) OVER (PARTITION BY empid
ORDER by orderMonth
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) AS nextQty
FROM Sales.EmpOrders;
-
Tried this:
Select groupid, [line number], [to]-[from] as ToFrom,
MAX([to]-[from]) over (partition by groupid
order by [line number]
rows between 1 Preceding
AND 1 preceding) as prevToFrom,
MAX([to]-[from]) over (partition by groupid
order by [line number]
rows between 1 Following
AND 1 Following) as nextToFrom
FROM [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
got an error saying "Incorrect syntax near rows".
I am using sql server 2008.
-
pietlinden (11/24/2014)
If you use windowing functions, this isn't that hard. Since I didn't understand how the create table/insert statements were related to your question, this is a somewhat generic answer.It seems that you need a couple of windowing functions. To do a running total, you need something like this:
SELECT empID, ordermonth, qty,SUM(qty) OVER(PARTITION BY empID
ORDER BY orderMonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runQty
FROM sales.EmpOrders;
To refer to Previous Record: (from Itzik Ben-Gan's book) (p.39)
SELECT empid, ordermonth,qty AS currentQty,
MAX(qty) OVER (PARTITION BY empid
ORDER by orderMonth
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS prevQty,
MAX(qty) OVER (PARTITION BY empid
ORDER by orderMonth
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) AS nextQty
FROM Sales.EmpOrders;
"PRECEDING" isn't available until 2012. The OP is using 2008 according to his previous post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
GrassHopper (11/25/2014)
Tried this:Select groupid, [line number], [to]-[from] as ToFrom,
MAX([to]-[from]) over (partition by groupid
order by [line number]
rows between 1 Preceding
AND 1 preceding) as prevToFrom,
MAX([to]-[from]) over (partition by groupid
order by [line number]
rows between 1 Following
AND 1 Following) as nextToFrom
FROM [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
got an error saying "Incorrect syntax near rows".
I am using sql server 2008.
I'm on my way to work. Let me know if you're opposed to using undocumented features or not and, if no one beats me to it, I'll show you a very fast way of doing this tonight if your not or we'll need to resort to a sort of fast loop to do it if you are.
If someone posts a solution with a "<=" relationship in it, beware that will be a "Triangular Join", which will eat the face off your server for performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm open to undocumented features. Glad you mentioned the issues with the "<=" because I was trying to do something with that. I'll trash that idea and wait to see what you are going to recommend.
thanks!
-
GrassHopper (11/24/2014)
I am using SQL Server 2008. Line Number controls the order. Here is some test data to use for my example:SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable(
[GroupID] [nvarchar](50) NULL,
[Line Number] [int] NULL,
[From] [decimal](18, 4) NULL,
[To] [decimal](18, 4) NULL,
[ID] [int] NOT NULL,
--[ID] [int] identity(1,1) NOT NULL,
CONSTRAINT [PK_ID_DealEntry] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE #mytable ADD DEFAULT ((0)) FOR [From]
GO
ALTER TABLE #mytable ADD DEFAULT ((0)) FOR [To]
GO
Select 'SELECT '
+ QUOTENAME(GroupID, '''')+','
+ QUOTENAME([Line Number], '''')+','
+ QUOTENAME([From], '''')+','
+ QUOTENAME([To], '''')+','
+ QUOTENAME([ID], '''')+','
+ ' UNION ALL'
FROM [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
Insert Into #mytable (GroupID,[Line Number], [from], [to],ID )
SELECT '110300162','1','0.0000','1000.0000','1' UNION ALL
SELECT '110300162','2','1001.0000','500000.0000','2' UNION ALL
SELECT '110300162','3','5000001.0000','5000060.0000','3' UNION ALL
SELECT '110300162','4','5000061.0000','10000000000.0000','4' UNION ALL
SELECT '110300163','1','0.0000','10000.0000','5' UNION ALL
SELECT '110300163','2','10001.0000','500000000000.0000','10' UNION ALL
SELECT '110300163','3','0.0000','0.0000','11' UNION ALL
SELECT '110300163','4','0.0000','0.0000','12' UNION ALL
SELECT '110300168','1','0.0000','10000000000.0000','13' UNION ALL
SELECT '110300169','1','0.0000','10000000000.0000','14' UNION ALL
SELECT '110300169','2','0.0000','0.0000','15' UNION ALL
SELECT '110300170','1','0.0000','10000000000.0000','16' UNION ALL
SELECT '110300171','1','0.0000','10000000000.0000','17' UNION ALL
SELECT '110300172','1','0.0000','1000.0000','18' UNION ALL
SELECT '110300172','2','1001.0000','5000000.0000','26' UNION ALL
SELECT '110300172','3','5000001.0000','1000000000.0000','27' UNION ALL
SELECT '110300173','1','0.0000','10000000000.0000','28' UNION ALL
SELECT '110300182','1','0.0000','10000000000.0000','29' UNION ALL
SELECT '110300187','1','0.0000','10000000000.0000','30' UNION ALL
SELECT '110300188','1','0.0000','0.0000','31' UNION ALL
SELECT '110300199','1','0.0000','10000000000.0000','32' UNION ALL
SELECT '110300207','1','0.0000','10000000000.0000','33' UNION ALL
SELECT '110300210','1','0.0000','0.0000','34' UNION ALL
SELECT '110300222','1','0.0000','10000000000.0000','35' UNION ALL
SELECT '110300222','2','0.0000','0.0000','36' UNION ALL
SELECT '110300228','1','0.0000','10000000000.0000','37' UNION ALL
SELECT '110300239','1','0.0000','10000000000.0000','38' UNION ALL
SELECT '110300239','1','0.0000','10000000000.0000','39' UNION ALL
SELECT '110300240','1','0.0000','1000.0000','40' UNION ALL
SELECT '110300240','2','1001.0000','50000.0000','41' UNION ALL
SELECT '110300240','3','50001.0000','800000000.0000','42' UNION ALL
SELECT '110300247','1','0.0000','10000000000.0000','43' UNION ALL
SELECT '110300248','1','0.0000','10000000000.0000','44' UNION ALL
SELECT '110300256','1','0.0000','10000000000.0000','46' UNION ALL
SELECT '110300259','1','0.0000','1000.0000','48' UNION ALL
SELECT '110300259','2','1001.0000','50000.0000','49' UNION ALL
SELECT '110300259','3','50001.0000','8000000000.0000','50' UNION ALL
SELECT '110300260','1','0.0000','10000000000.0000','51' UNION ALL
SELECT '110300261','1','0.0000','10000000000.0000','52' UNION ALL
SELECT '110300262','1','0.0000','1000.0000','54' UNION ALL
SELECT '110300262','2','1001.0000','5000000000.0000','55' UNION ALL
SELECT '110300263','1','0.0000','0.0000','56' UNION ALL
SELECT '110300264','1','0.0000','100000000000.0000','57' UNION ALL
SELECT '110300265','1','0.0000','0.0000','58' UNION ALL
SELECT '110300266','1','0.0000','0.0000','59' UNION ALL
SELECT '110300270','1','0.0000','10000000000.0000','60' UNION ALL
SELECT '110300274','1','0.0000','1000.0000','61' UNION ALL
SELECT '110300274','2','1001.0000','5000.0000','62' UNION ALL
SELECT '110300274','3','5001.0000','50000.0000','63' UNION ALL
SELECT '110300274','4','50001.0000','100000000.0000','64' UNION ALL
SELECT '110300275','1','0.0000','10000000000.0000','65' UNION ALL
SELECT '110300275','2','0.0000','0.0000','66' UNION ALL
SELECT '110300275','3','0.0000','0.0000','67' UNION ALL
SELECT '110300276','1','0.0000','10000000000.0000','68' UNION ALL
SELECT '110300276','2','0.0000','0.0000','69' UNION ALL
SELECT '110300277','1','0.0000','10000000.0000','70' UNION ALL
SELECT '110300277','2','10000000.0100','1000000000.0000','71' UNION ALL
SELECT '110300284','1','0.0000','10000000000.0000','72' UNION ALL
SELECT '110300287','1','0.0000','5000000.0000','73' UNION ALL
SELECT '110300287','2','5000000.0100','100000000.0000','74' UNION ALL
SELECT '110300289','1','0.0000','10000000000.0000','75' UNION ALL
SELECT '110300297','1','0.0000','10000000000.0000','76' UNION ALL
SELECT '110300298','1','0.0000','10000000.0000','77' UNION ALL
SELECT '110300298','2','10000000.0100','100000000.0000','78' UNION ALL
SELECT '110300336','1','0.0000','10000000000.0000','79' UNION ALL
SELECT '110300336','2','0.0000','0.0000','80' UNION ALL
SELECT '110300336','3','0.0000','0.0000','81' UNION ALL
SELECT '110300342','1','0.0000','0.0000','82' UNION ALL
SELECT '110300343','1','0.0000','0.0000','83' UNION ALL
SELECT '110300349','1','0.0000','10000000000.0000','84' UNION ALL
SELECT '110300353','1','0.0000','10000000000.0000','85' UNION ALL
SELECT '110300361','1','0.0000','10000000000.0000','86' UNION ALL
SELECT '110300361','2','0.0000','100000.0000','87' UNION ALL
SELECT '110300361','3','100000.0000','5000000.0000','88' UNION ALL
SELECT '110300361','4','0.0000','0.0000','89' UNION ALL
SELECT '110300361','5','0.0000','0.0000','90' UNION ALL
SELECT '110300361','6','0.0000','0.0000','91' UNION ALL
SELECT '110300274','5','0.0000','0.0000','92' UNION ALL
SELECT '110300298','3','0.0000','0.0000','93' UNION ALL
SELECT '110300298','4','0.0000','0.0000','94' UNION ALL
SELECT '110300298','5','0.0000','0.0000','95' UNION ALL
SELECT '110300298','6','0.0000','0.0000','96' UNION ALL
SELECT '110300298','7','0.0000','0.0000','97' UNION ALL
SELECT '110300298','8','0.0000','0.0000','98' UNION ALL
SELECT '110300298','9','0.0000','0.0000','99' UNION ALL
SELECT '110300298','10','0.0000','0.0000','100' UNION ALL
SELECT '110300298','11','0.0000','0.0000','101' UNION ALL
SELECT '110300274','6','0.0000','0.0000','102' UNION ALL
SELECT '110300274','7','100000.0000','1000000.0000','103'
and the volume used is a sum of a table that comes out to be :
324820.80
based on this data ....can you please post expected results.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
GrassHopper (11/25/2014)
I'm open to undocumented features. Glad you mentioned the issues with the "<=" because I was trying to do something with that. I'll trash that idea and wait to see what you are going to recommend.thanks!
suggest you read this article http://www.sqlservercentral.com/articles/T-SQL/68467/
me thinks this is what Jeff Moden is referring to as a method.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Heh... "Holy shades of 'Office Space', Batman!"
I've had some time to do deeper dive on your request and, although I could certainly be wrong, I think there's a fundamental flaw in the logic even in the spreadsheet that would make any accountant worth their salt just absolutely lose it.
If we look at your spreadsheet...
... we see that the "TO" value on row 5 is 60. That means that the total volume for those 3 entries is 60. Yet, the running total in column K, which is based on the value of To-From, is only 59.98. The reason for that is that I don't believe that the FROM values of 10.01 and 20.01 on rows 4 and 5 are correct. Instead, they should be 10.00 and 20.00 as the TO value of the previous rows. A simile of this problem would be that you don't get to add a penny to your checkbook just because you open it the next day. 🙂
I need you verify that we actually want to make what I believe is an accounting error.
There's also no way for me to determine what value you want for Column L and it's not included in your data.
And, yes... we can actually determine the correct volume for each row by using TOn-TO(n-1) but I need to know that's alright as well as have you provide "Column L" data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am posting the desired results in a jpg. I will explain how I get to this:
At the top cell J and K, we have the Volume. It is calculates with this query using another table:
Select SUM(Calc_line_net_amount) as volume,GroupID,[Include]
from [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].ClaimTank
Group by GroupID, [Include]
Having Include <> 0
order by GroupID
Then from the table called "[DealEntry_Rebate_ClaimTank]", we want to do the following:
1) Get the ToFrom in cell F which is Cell D - Cell E.
2) I put the Running total in a separate cell (Cell G) for explanation purposes. But what we really want is for the for cell f to be a running total.
3) When the Running Total is greater than the Volume (cell K), then instead of putting the running total amount, we subtract the previous value in the Running total from the Volume amount for that Groupid. (in the above query we get the volume total for each groupid).
4) If there is another record for that groupid, we can just put NULL values in the ToFrom and RunningTotal.
I used groupid '110300163' as a good example and put the amount in red (volume - Running Total for ID#10). Then I put that value in H:11.
Remember the value in H11(23,391,678.19) should really be in F:11. I just used Column G for showing the work.
I hope this makes it more clear. I had to change the numbers from what I gave above in the test data in order to explain the process better. Hope this helps?
thanks!
-
In the attachment I will explain what that means:
The volume in Cell K, I get by using this query:
Select SUM(Calc_line_net_amount) as volume,GroupID,[Include]
from [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].ClaimTank
Group by GroupID, [Include]
Having Include <> 0
order by GroupID
From another table called [DealEntry_Rebate_ClaimTank] We have the data below:
1) Column F is a Cell D - Cell E
2) Column G is a Running Total for each GroupID (only used for explanation purposes)
3) The amounts in Column H for ID# 11, should really be in Column F
4) When the Running total is going to be greater than the Volume for the groupid, then use this calculation to populate Column F. Volume - Previous Runnig total value. In this example in gray, it would be 23,891,677.19 - 499,999.00 and the result in red (23,391,678.19) would be put in column F for ID#11 (the same row it is in). I just put the total in H for showing the work.
5) if there are more records in that groupid, then it would just be populated with NULL Values in the ToFrom.
I only used the volume for groupid '110300163' to show an example. Hope this helps.
Thanks!
-
Sorry for this double post...i didn't realize it went to another page and thought it didn't go thru the first time.
GrassHopper (11/26/2014)
In the attachment I will explain what that means:The volume in Cell K, I get by using this query:
Select SUM(Calc_line_net_amount) as volume,GroupID,[Include]
from [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].ClaimTank
Group by GroupID, [Include]
Having Include <> 0
order by GroupID
From another table called [DealEntry_Rebate_ClaimTank] We have the data below:
1) Column F is a Cell D - Cell E
2) Column G is a Running Total for each GroupID (only used for explanation purposes)
3) The amounts in Column H for ID# 11, should really be in Column F
4) When the Running total is going to be greater than the Volume for the groupid, then use this calculation to populate Column F. Volume - Previous Runnig total value. In this example in gray, it would be 23,891,677.19 - 499,999.00 and the result in red (23,391,678.19) would be put in column F for ID#11 (the same row it is in). I just put the total in H for showing the work.
5) if there are more records in that groupid, then it would just be populated with NULL Values in the ToFrom.
I only used the volume for groupid '110300163' to show an example. Hope this helps.
Thanks!
-
The answer to your question is that when the Running total is greater than the Volume total (60), instead of entering in the running total amount, you calculate the total volume minus the previous running total and then put the value in that cell. i.e 60 - 59.98 = .02
That is how I got the .02. Also, if there is a record below that one, you would stop calculating running total and ToFrom and put Null values as a value for those 2 columns. for the rest of the records in that groupid. (see my example I posted with better explanation).
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply