Subtract running amount

  • Hello,

    I have a couple of tables. One tracks Rate and Effective Date, another that tracks Earned Hours and Earn Date.  I made a table, see below, that mimics this combined information (I use a sum and Cross Join).

    If I need to pay out Earned Hours at the most recent rate and working backwards. How can I do this?

    Insert Into #MyTempTable (EE, EffDate, Salary, Accrued)

    Values (720, '12/1/2020',18,40)

    , (720, '11/1/2020',16,53.28)

    , (720, '10/1/2020',14,113.16)

    , (720, '9/1/2020',12,39.24)

    , (720, '8/1/2020',10,2.91)

    ;

    If I wanted to pay out 150 hours, then it should be at:

    40 @ 18

    53.28 @ 16

    56.72 @ 14

    Thank you for any help.

  • You could try something like this

    drop table if exists #MyTempTable
    go
    create table #MyTempTable(
    EE int not null,
    EffDate date not null,
    Salary int not null,
    Accrued decimal(14, 2) not null);
    go

    Insert Into #MyTempTable(EE, EffDate, Salary, Accrued) Values
    (720, '12/1/2020',18,40)
    ,(720, '11/1/2020',16,53.28)
    ,(720, '10/1/2020',14,113.16)
    ,(720, '9/1/2020',12,39.24)
    ,(720, '8/1/2020',10,2.91);

    declare @hrs int=150;

    with
    sum_accrued_cte(EE, EffDate, Salary, Accrued, sum_accrued) as (
    select *, sum(Accrued) over (order by EffDate desc)
    from #MyTempTable),
    cum_accrued_cte(EffDate, Salary, cum_accrued) as (
    select EffDate, Salary,
    case when (@hrs-sum_accrued)>0 then Accrued
    else (@hrs-lag(sum_accrued) over (order by EffDate desc)) end
    from sum_accrued_cte)
    select *
    from cum_accrued_cte
    where cum_accrued>0
    order by EffDate desc;

    Output

    EffDate		Salary	cum_accrued
    2020-12-01 18 40.00
    2020-11-01 16 53.28
    2020-10-01 14 56.72

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum.  I'm thinking we're going to need another "Quirky Update" solution against a Temp Table.

    Ken... you good folks should really make a plan to upgrade in the near future.  2005 was great in its day but has been out of support for a whole lotta years now and you're missing out on some serious new functionality that would make this and other problems seem fairly trivial to solve compared to how we'll need to solve them in 2005.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum.  I'm thinking we're going to need another "Quirky Update" solution against a Temp Table.

    Ken... you good folks should really make a plan to upgrade in the near future.  2005 was great in its day but has been out of support for a whole lotta years now and you're missing out on some serious new functionality that would make this and other problems seem fairly trivial to solve compared to how we'll need to solve them in 2005.

    I did find some old documentation for SQL Server 2005 which says the SUM function may be followed by the OVER clause.  However, I also used the LAG function which clearly was not available.  Pretty sure ROW_NUMBER existed and could be used in place of LAG.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Let me try this in sql. I think this should work fine.

    Thanks,

  • Steve Collins wrote:

    I did find some old documentation for SQL Server 2005 which says the SUM function may be followed by the OVER clause.

    Yep... and I didn't contradict.  Go back and look at what I said...

    Jeff Moden wrote:

    Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum.

    I didn't say SUM couldn't be used with OVER() in 2005... I just said that it didn't have the capability to do a running total in 2005. 😉

    The same 2005 documentation set also says the same thing although it's one of those famous things that Microsoft does in the documentation where it's "hidden in plain sight" and they don't explicitly list the limitation even when they demonstrate it.  Please see the article on OVER() at the following link from that same documentation set...

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189461(v=sql.90)

    What you're looking for is at the very top where it says...

    Aggregate Window Functions

    <OVER_CLAUSE> :: =

    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

    There's no ORDER BY available when used in Aggregate Functions back in 2005.  The only thing it could do with Aggregate Functions was return the full value (SUM, AVG, whatever) for the given partition.  It couldn't do a running sum, or any other kind of running aggregate back in 2005.  If you look at the examples at the bottom of that same article I cited above, you'll not find a single example of any running aggregate.

    Itzek Ben-Gan was one of the folks that was also incensed (I was totally pissed but I don't have Microsoft's ear) by this short coming and pushed hard (thank you Mr. Ben-Gan) on Microsoft for running total aggregates.  Using SUM() OVER() to produce a running total didn't become a reality until LEAD and LAG came out (2012, IIRC), which Itzek also pushed for.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... this is why us old dinosaurs rock...

    Someone else: "Hey there, Grandpa!  Can you tell us that old story about what it was like to calculate running totals in the old days?  Some of us haven't heard it before."

    Old Dude:  "Heh... yep... I remember way back in '97... yadda, "Quirky Update", Cursors, Triangular Joins, yadda, yadda... and, in '05 we prayed the SUM() OVER() jewel would have the power to yadda, yadda, but had a crack in it that could only be filled by computational gold and, yadda, yadda, and then this mighty warrior, Sir Itzik of Ben-Gan, and others of his high status, beat on the fetid MS beast with their "Clubs of Knowledge" for nigh on to the length of a dog year until the beast finally gave up its grip on the computational gold and the SUM() OVER() jewel was finally repaired and the SQL gods finally forgave mere mortals for their workarounds.  But nothing is ever free for the MS beast smote the mortals with FORMAT  and STRING_SPLIT and left the severely wounded PIVOT and "new" temporal functions and datatypes and built-in Tally function to rot and writhe in pain without dying and left the EOMonth function without a mate to test the ultimate will and fortitude of the mortals but that's another story. The end... Now go get me a beer, kid." 😀

    "Heh... ok, ok... PLEASE go get me a beer, kid." 😀

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Steve Collins wrote:

    I did find some old documentation for SQL Server 2005 which says the SUM function may be followed by the OVER clause.

    Yep... and I didn't contradict.  Go back and look at what I said...

    Jeff Moden wrote:

    Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum.

    I didn't say SUM couldn't be used with OVER() in 2005... I just said that it didn't have the capability to do a running total in 2005. 😉 

    Yea that's the key distinction I missed.  The big release for me was 2008 R2.  There was a project that lasted several years and caused me to frequently read the BOL etc.  Prior to 2008 R2 it's all a little hazy.  Then I took a break and switched to C# for several years and missed the SQL Server 2012 and 2014 releases.  2012 was huge.  When I came back to SQL programming it was at 2016 and today all of my projects run on Azure SQL compatibility level 140 which is equivalent to 2017.

    Running total are easy in SAS which what I also used for many years.  Nobody talks about SAS but it's still around and many big companies still use it afaik.

    Why is there no BOMONTH to go along with EOMONTH?  Imo it's because DATEFROMPARTS already exists and BOMONTH would just be a special case of that function and therefore it's not necessary 🙂

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    Why is there no BOMONTH to go along with EOMONTH?  Imo it's because DATEFROMPARTS already exists and BOMONTH would just be a special case of that function and therefore it's not necessary 🙂

    Nah...there really isn't a need for BOMONTH because we have EOMONTH.  We can use the following: DATEADD(day, 1, EOMONTH(getdate(), -1)).  However - EOMONTH has a real distinct problem...it returns a DATE data type regardless of input which means you MUST cast/convert the value to the appropriate data type.

    EOMONTH should have been built to return the same data type as the input parameter - but nope, it is broken.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Steve Collins wrote:

    Why is there no BOMONTH to go along with EOMONTH?  Imo it's because DATEFROMPARTS already exists and BOMONTH would just be a special case of that function and therefore it's not necessary 🙂

    Nah...there really isn't a need for BOMONTH because we have EOMONTH.  We can use the following: DATEADD(day, 1, EOMONTH(getdate(), -1)).  However - EOMONTH has a real distinct problem...it returns a DATE data type regardless of input which means you MUST cast/convert the value to the appropriate data type.

    EOMONTH should have been built to return the same data type as the input parameter - but nope, it is broken.

    I definitely agree that EOMonth is broken but there's also no need for it if you do temporal criteria correctly.  Heh... and if anyone is  using EOMonth to calculate a BOMonth, they should see a doctor. 😀 😀 😀 (That IS supposed to be a joke and not a personal slam).

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 🙂

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve Collins wrote:

    Why is there no BOMONTH to go along with EOMONTH?  Imo it's because DATEFROMPARTS already exists and BOMONTH would just be a special case of that function and therefore it's not necessary 🙂

    As you know, you don't need DATEFROMPARTS to get BOMonth but I get what you're saying 😀 though BOMonth would be a very nice convenience and would save on a whole lot of teaching headaches.  The thing I object to the most about them creating EOMonth is that it encourages the sometimes thoughtless/erroneous use of BETWEEN (closed/closed endpoints) for temporal range criteria rather than the much safer >= and <  (closed/open endpoints).  That and, like Jeffrey Williams reminds us in his post above, it's actually broken in the it doesn't return the datatype provided to it's single parameter.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I will say you've give me a lot to read and think about.

    Thanks,

Viewing 13 posts - 1 through 13 (of 13 total)

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