-
Mar 23rd, 2018, 05:15 AM
#1
Thread Starter
Lively Member
Sql 2005- find the difference between rows
I dunno if it its possible to generate a table that find the difference of one row to another.
Just like this:
it is group by by ID and sorted by date..
Let's assume that the AvailBalance column is not yet there how will I generate it like the picture above?
I want to do it directly on sql server like creating view or stored proc or anything that will lead me to this kind of output.
Note: I just made this table manually so I can explaine clearly what I want to do.
-
Mar 23rd, 2018, 06:01 AM
#2
Re: Sql 2005- find the difference between rows
Firstly, that's not grouped by id. Grouping means combining multiple records into one based on one or more common values. That data is sorted on id.
As for your question, it's not something I've done so I'm not 100% of the specifics but I would suggest that you look into the ROW_NUMBER function. You can query the original table and sort the data the way you have there and use ROW_NUMBER to assign a sequential value to each row. You can then query that result set and use the fact that adjacent rows have row numbers that differ by 1 perform calculations between them.
-
Mar 23rd, 2018, 07:19 AM
#3
Re: Sql 2005- find the difference between rows
Try this
Code:
Create Table Demo (Id int, Allowance money, DateFld datetime, Amount money, Avail_balance money)
Insert into Demo values (1,7000,'3/1/2018',1000,6000)
Insert into Demo values (1,7000,'3/2/2018',500,5500)
Insert into Demo values (1,7000,'3/3/2018',500,5000)
Insert into Demo values (2,8000,'3/1/2018',2000,6000)
Insert into Demo values (2,8000,'3/2/2018',500,5500)
Select * From Demo
Go
Create View DemoView
As
Select Row_Number() Over (Partition By Id Order by Id, DateFld) "RowNum",* From Demo
Go
Select * From DemoView DV
Left Join DemoView DV2 on DV2.Id=DV.Id and DV2.RowNum=DV.RowNum-1
Go
Drop View DemoView
Drop Table Demo
Should give you this - with the primary rows on the left and the right side showing each row's PRIOR row. Using the Row_Number function in a VIEW and then JOIN'ing back to that view.
Code:
RowNum Id Allowance DateFld Amount Avail_balance RowNum Id Allowance DateFld Amount Avail_balance
1 1 7000.00 2018-03-01 00:00:00.000 1000.00 6000.00 NULL NULL NULL NULL NULL NULL
2 1 7000.00 2018-03-02 00:00:00.000 500.00 5500.00 1 1 7000.00 2018-03-01 00:00:00.000 1000.00 6000.00
3 1 7000.00 2018-03-03 00:00:00.000 500.00 5000.00 2 1 7000.00 2018-03-02 00:00:00.000 500.00 5500.00
1 2 8000.00 2018-03-01 00:00:00.000 2000.00 6000.00 NULL NULL NULL NULL NULL NULL
2 2 8000.00 2018-03-02 00:00:00.000 500.00 5500.00 1 2 8000.00 2018-03-01 00:00:00.000 2000.00 6000.00
-
Mar 23rd, 2018, 08:25 AM
#4
Re: Sql 2005- find the difference between rows
Not sure but I think we're miss-understanding the users question. She asks: "Let's assume that the AvailBalance column is not yet there how will I generate it like the picture above?" So I think she's saying, given the other columns, how would she calculate the running balance that the Available balance columns represents. The Group By ID statement is miss-leading because she's not really grouping but is calculating a separate running balance for each ID partition.
If I've understood correctly then there is a set based way of doing it but it's a bad idea (I'll explan why in a sec). It looks something like this:-
Code:
Select T.ID,
T.Allowance,
T.Date,
T.Amount,
T.Allowance - sum(T_Older.Aount)
From MyTable T
Join MyTable T_Older
on T.ID = T_Older.ID
and T.Date > T_Older.Date
Group By T.ID,
T.Allowance,
T.Date,
T.Amount
This is a bad idea, though, because it creates what's called a "Triangular Join". That is a join from a table back to itself based on an inequality. It's bad because the number of rows the query has to touch in order to resolve increases exponentially (actually a half exponent) as the number of rows in the table increases. It's not quite as bad in your case because your calculating running totals across partitions rather than the entire table but it will still be some fraction of an exponent. If you do this on a small data set it will appear to run fine but , as the dataset grows, it will quickly bring your server to it's knees.
This is one of the very few occasions where a loop or cursor based approach is actually better than a set based one. If you're feeling brave and don't imnd using non-documented features, the fastest resolution would actually be a "Quirky Update". Here's an excellent article that goes into the detail.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Mar 23rd, 2018, 08:47 AM
#5
Re: Sql 2005- find the difference between rows
SzLamany's suggestion and the talk of Row_Number got me thinking about whether this could be efficiently solved by recursion these days. I think this will work but I'm not in a position to test it:-
Code:
with cteOrderedRows as
(
Select T.ID,
T.Allowance,
T.Date,
T.Amount,
Row_Number() Over (Partition By ID Order By Date) as RowNum
),
cteRecursive as
(
Select ID, Allowance, Date, Amount, Allowance - Amount as AvailBalance
From cteOrderedRows
Where RowNum = 1
Union All
Select O.ID, O.Allowance, O.Date, O.Amount, R.AvailBalance - O.Amount
From cteRecursive R
Join cteOrderedrows O
on R.ID = O.ID
and R.RowNum + 1 = O.RowNum
)
Select *
From cteRecursive
Much like the quirky update, this is actually a RBAR approach because it's creating a hidden loop but at least it's using fully supported features.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|