Results 1 to 5 of 5

Thread: Sql 2005- find the difference between rows

  1. #1

    Thread Starter
    Lively Member Carmell's Avatar
    Join Date
    Dec 2015
    Posts
    105

    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:

    Name:  13.JPG
Views: 227
Size:  18.8 KB

    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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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
  •  



Click Here to Expand Forum to Full Width