Post reply

Running Total Issue - ANything New On this Aside from Jeff Modens "Quirky Update"?

  • June 30, 2011 at 2:45 pm

    #238674

    If one is trying to produce output that includes a running total or running balance and the "quirky Update" method from Jeff Moden is not an option and you're looking at very large data sets (think customer ledgers many pages in length for thousands of customers if not more) is there any better way to do it other then the old fashioned cursor or triangular join method?

    I fear there isn't but before I spend a good bit of time setting up a reportting tool to do this (something like Crystal Reports soleley to get the running total colunm in the output) I wanted to ask if anyone here had heard anything new about this commonly asked about T-SQL problem.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • June 30, 2011 at 2:51 pm

    #1346759

    YSLGuru (6/30/2011)

    If one is trying to produce output that includes a running total or running balance and the "quirky Update" method from Jeff Moden is not an option and you're looking at very large data sets (think customer ledgers many pages in length for thousands of customers if not more) is there any better way to do it other then the old fashioned cursor or triangular join method?

    I fear there isn't but before I spend a good bit of time setting up a reportting tool to do this (something like Crystal Reports soleley to get the running total colunm in the output) I wanted to ask if anyone here had heard anything new about this commonly asked about T-SQL problem.

    Thanks

    No, not really. You've actually gone to the right tool for the job there. The Running Totals issue in SQL is that it's really a presentation layer thing, not a SQL thing, which is why it causes such issues.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 30, 2011 at 3:23 pm

    #1346784

    Craig Farrell (6/30/2011)

    The Running Totals issue in SQL is that it's really a presentation layer thing, not a SQL thing, which is why it causes such issues.

    Craig, would you mind clarifying why you say it is a presentation layer thing? The reason I ask is because I've recently ran into scenarios where we need to identify the date some accounts hit a certain balance, and then from there insert new rows elsewhere in the DB. I can't imagine writing a report solely to get that info and then load that information back into the DB to do more tasks. Maybe I misinterpreted your comment and if so I apologize. Just wanted to better understand your perspective on this issue.

  • June 30, 2011 at 3:28 pm

    #1346786

    hwells (6/30/2011)

    Craig Farrell (6/30/2011)

    The Running Totals issue in SQL is that it's really a presentation layer thing, not a SQL thing, which is why it causes such issues.

    Craig, would you mind clarifying why you say it is a presentation layer thing? The reason I ask is because I've recently ran into scenarios where we need to identify the date some accounts hit a certain balance, and then from there insert new rows elsewhere in the DB. I can't imagine writing a report solely to get that info and then load that information back into the DB to do more tasks. Maybe I misinterpreted your comment and if so I apologize. Just wanted to better understand your perspective on this issue.

    Nope you read me completely correct and there ARE times you need the data layer to do things like this, but most often when you do running totals it's for end user reports. Those reporting tools are built to do things like this, and thus, is typically presentation layer.

    So, in your case, I'd agree with you, but it sounds more like yours is a one off corrective item after which your proc you do the CRUD work through will then be able to detect it real-time and perform the task on each change which might hit this boundary.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 30, 2011 at 8:26 pm

    #1346831

    hwells (6/30/2011)

    Craig Farrell (6/30/2011)

    The Running Totals issue in SQL is that it's really a presentation layer thing, not a SQL thing, which is why it causes such issues.

    Craig, would you mind clarifying why you say it is a presentation layer thing? The reason I ask is because I've recently ran into scenarios where we need to identify the date some accounts hit a certain balance, and then from there insert new rows elsewhere in the DB. I can't imagine writing a report solely to get that info and then load that information back into the DB to do more tasks. Maybe I misinterpreted your comment and if so I apologize. Just wanted to better understand your perspective on this issue.

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    --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)

  • June 30, 2011 at 8:27 pm

    #1346832

    YSLGuru (6/30/2011)

    If one is trying to produce output that includes a running total or running balance and the "quirky Update" method from Jeff Moden is not an option and you're looking at very large data sets (think customer ledgers many pages in length for thousands of customers if not more) is there any better way to do it other then the old fashioned cursor or triangular join method?

    I fear there isn't but before I spend a good bit of time setting up a reportting tool to do this (something like Crystal Reports soleley to get the running total colunm in the output) I wanted to ask if anyone here had heard anything new about this commonly asked about T-SQL problem.

    Thanks

    If fear is involved in the decision as to whether or not to using the "Quirky Update", but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    --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)

  • June 30, 2011 at 8:51 pm

    #1346836

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 1, 2011 at 7:26 am

    #1347014

    Craig Farrell (6/30/2011)

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    Ben-Gan has a couple of open CONNECT items on the subject of making "previous row" and "next row" aggregates such a SUM work correctly.

    --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)

  • July 1, 2011 at 10:33 am

    #1347164

    Jeff Moden (7/1/2011)

    Craig Farrell (6/30/2011)

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    Ben-Gan has a couple of open CONNECT items on the subject of making "previous row" and "next row" aggregates such a SUM work correctly.

    Jeff - I'm about as hesitant to use anything from you as I am afraid that the Easter Bunny is going to attack me. Seriously though, it’s more of an accounting/auditing thing. The higher ups don't want me using anything out of the standard T-SQL approved documentation for these specific financials especially if the desired result can be done at the report level with something like Crystal. I just prefer doing something like this once in the T-SQL as a source (i.e. a view) so that many reports can reference it verses having to do this in every report and we will have many.

    I will take a look at Ben-Gans stuff though just out of curiosity.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • July 1, 2011 at 10:37 am

    #1347165

    While we’re on the topic of Running Totals..

    Has anyone heard if the next iteration (or the one after) of SQL Server, SQL 2010, will include some kind of update in T-SQL to do this kind of thing, to keep a running total?

    I admit I am not an engineer of the MS DB team and so maybe this kind of thing is not easily doable in SQL Server/T-SQL but I would think it would be. It’s like the whole GROUP BY thing to me where you have to explicitly list every item in the GROUP BY instead of just doing something like GROUP BY ALL as shorthand to imply group by whatever is in the SELECT that is not an aggregate calculation. After all it does not matter what order you list the items in GROUP BY so why can’t one just imply GROUP BY everything?

    Anyway…

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • July 2, 2011 at 4:40 pm

    #1347498

    YSLGuru (7/1/2011)

    Jeff Moden (7/1/2011)

    Craig Farrell (6/30/2011)

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    Ben-Gan has a couple of open CONNECT items on the subject of making "previous row" and "next row" aggregates such a SUM work correctly.

    Jeff - I'm about as hesitant to use anything from you as I am afraid that the Easter Bunny is going to attack me. Seriously though, it’s more of an accounting/auditing thing. The higher ups don't want me using anything out of the standard T-SQL approved documentation for these specific financials especially if the desired result can be done at the report level with something like Crystal. I just prefer doing something like this once in the T-SQL as a source (i.e. a view) so that many reports can reference it verses having to do this in every report and we will have many.

    I will take a look at Ben-Gans stuff though just out of curiosity.

    Thanks

    Thank you for the nice compliment. :blush: I'm humbled.

    I understand the "higher ups" being a little nervous about using anything that's undocumented especially when most reporting tools have some form of documented running total build in.

    I've not yet tried the new method without the correctly ordered clustered index... but do you suppose they'd "buy in" if it did? Don't forget, the 3 part UPDATE IS a documented feature. 🙂

    --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)

  • July 3, 2011 at 9:32 am

    #1347548

    Jeff Moden (7/2/2011)

    YSLGuru (7/1/2011)

    Jeff Moden (7/1/2011)

    Craig Farrell (6/30/2011)

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    Ben-Gan has a couple of open CONNECT items on the subject of making "previous row" and "next row" aggregates such a SUM work correctly.

    Jeff - I'm about as hesitant to use anything from you as I am afraid that the Easter Bunny is going to attack me. Seriously though, it’s more of an accounting/auditing thing. The higher ups don't want me using anything out of the standard T-SQL approved documentation for these specific financials especially if the desired result can be done at the report level with something like Crystal. I just prefer doing something like this once in the T-SQL as a source (i.e. a view) so that many reports can reference it verses having to do this in every report and we will have many.

    I will take a look at Ben-Gans stuff though just out of curiosity.

    Thanks

    Thank you for the nice compliment. :blush: I'm humbled.

    I understand the "higher ups" being a little nervous about using anything that's undocumented especially when most reporting tools have some form of documented running total build in.

    I've not yet tried the new method without the correctly ordered clustered index... but do you suppose they'd "buy in" if it did? Don't forget, the 3 part UPDATE IS a documented feature. 🙂

    I agree, but what isn't in official documentation (and IMHO is what most folks are riled up about) is the 10 rules to follow to get it to work right. :crying:

    FYI: I've been hearing rumours that the next Denali CTP will have extended support for the ANSI SQL of the over clause - which would include things that would allow a running total to be implemented (not sure if that means a full implementation of the ANSI SQL for that...). However, I'll bet that by following the rules in Jeff's running totals article[/url], that the QU that Jeff shows us how to do it with will perform better.

    BTW, any word on when the latest rewrite of that article is going to be published? (Or did I miss it?)

    FYI: Here's a link to Itzik Ben-Gan's Connect items on the ANSI SQL features that the SQL Server implementation of the OVER clause is missing. If you haven't already voted for these, please do so!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 3, 2011 at 1:14 pm

    #1347557

    Jeff Moden (7/2/2011)

    YSLGuru (7/1/2011)

    Jeff Moden (7/1/2011)

    Craig Farrell (6/30/2011)

    Jeff Moden (6/30/2011)

    I don't know if it'll make people feel any better about the "Quirky Update" but Tom Thompson and Paul White added a couple of things to it to make it absolutely safe.

    Oh, I love the serial/quirky update for single passes if I need to use it, but I also understand why there's not a lot of tools in SQL to handle it. If they ever 'fix' that 'feature', we're in trouuubbblllllleeeee.....

    Ben-Gan has a couple of open CONNECT items on the subject of making "previous row" and "next row" aggregates such a SUM work correctly.

    Jeff - I'm about as hesitant to use anything from you as I am afraid that the Easter Bunny is going to attack me. Seriously though, it’s more of an accounting/auditing thing. The higher ups don't want me using anything out of the standard T-SQL approved documentation for these specific financials especially if the desired result can be done at the report level with something like Crystal. I just prefer doing something like this once in the T-SQL as a source (i.e. a view) so that many reports can reference it verses having to do this in every report and we will have many.

    I will take a look at Ben-Gans stuff though just out of curiosity.

    Thanks

    Thank you for the nice compliment. :blush: I'm humbled.

    I understand the "higher ups" being a little nervous about using anything that's undocumented especially when most reporting tools have some form of documented running total build in.

    I've not yet tried the new method without the correctly ordered clustered index... but do you suppose they'd "buy in" if it did? Don't forget, the 3 part UPDATE IS a documented feature. 🙂

    Is there a link to the updated code with saftey checks? I haven't been on ssc as much as I'd like to be so I think I may have missed part of this exchange of knowledge.

  • July 3, 2011 at 4:48 pm

    #1347578

    Lynn Pettis (7/3/2011)

    Is there a link to the updated code with saftey checks? I haven't been on ssc as much as I'd like to be so I think I may have missed part of this exchange of knowledge.

    If you go to the article, I believe I included a link to the code that Paul and Tom came up with. It's stupid-simple-brilliant and I don't know why I didn't think of it to begin with.

    --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)

  • July 4, 2011 at 8:27 am

    #1347887

    CELKO (7/4/2011)

    T-SQL is about a decade behind Oracle and DB2. We only got part of the window clause for aggregates; the full syntax is:

    You said a mouthful there! I 100% agree. I'd really like it if MS would spend more time on making T-SQL more robust instead of messing around with SSRS, SSIS, SSAS, and a whole lot of other 4 letter words. 😀

    --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)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply