Results 1 to 2 of 2

Thread: Sql Query

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    134

    Sql Query

    I have this data in database as shown in pic1 i want to substring firsts row (Deb - Kred) = -1 as Sald,
    then ((first row Sald + seconds row Deb) - Seconds row Kred) = -2 As Sald and sow on...,im doing some querys like case when firsts row deb null bla bla .. but i cant get those results like in pic2.


    pic1.
    Name:  1.png
Views: 227
Size:  2.7 KB

    pic2.
    Name:  2.png
Views: 198
Size:  3.7 KB

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

    Re: Sql Query

    OK, you're in murky waters here.

    This can be done as a set based query by joining the table back to itself. That would look something like this (not syntax checked so be wary of errors):-
    Code:
    Select T1.No, Sum(T2.Kred) - Sum(T2.Deb)
    From MyTable T1
    join MyTable T2
       on T1.No >= T2.No
    Group By T1.No
    I don't recommend you do this however. It looks nice and elegant but it will create what's called a triangular join. That's a join from a table to itself based on an inequality. Over a small number of records this will appear to be fine but as the number of records grow it will tank the performance. That inequality means the number of row based operations required to resolve it increases exponentially.

    This is one of the rare occasions where you would actually be better using a loop or cursor. Simply create a loop that runs over the records in order of "No" and maintain a running total of Said in a variable, updating back to the current record if you need to.

    Even better, if you're using Sql Server you can take advantage of a bug/undocumented feature in the behaviour of the query engine called a "three part set" to perform what's called a Quirky Update. Jeff Moden has an excellent article here which explains how to do it along with the various issues you may encounter along the way. It's very lengthy, though, and may be more than you're looking for. If you are using sql server and don't mind risking the fact that Microsoft could remove the 3 part set (but haven't since SQLServer was created and almost certainly won't) then this would be my recommended approach.

    Finally, I have a feeling you could use SQL Server's Window functions to resolve this but I'm not very adept at them so I'm not sure what that would look like.

    PS, I'm also moving your thread to the DB section. It's a much better fit there.
    Last edited by FunkyDexter; Mar 4th, 2015 at 12:43 PM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - 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