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
EffDateSalarycum_accrued
2020-12-011840.00
2020-11-011653.28
2020-10-011456.72
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 9, 2020 at 4:17 am
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
Change is inevitable... Change for the better is not.
December 9, 2020 at 1:41 pm
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
December 9, 2020 at 2:47 pm
Let me try this in sql. I think this should work fine.
Thanks,
December 11, 2020 at 11:28 pm
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...
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
Change is inevitable... Change for the better is not.
December 12, 2020 at 12:06 am
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
Change is inevitable... Change for the better is not.
December 12, 2020 at 3:30 pm
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
December 12, 2020 at 5:23 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 12, 2020 at 6:15 pm
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
Change is inevitable... Change for the better is not.
December 12, 2020 at 6:18 pm
🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 12, 2020 at 6:26 pm
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
Change is inevitable... Change for the better is not.
December 15, 2020 at 7:36 pm
I will say you've give me a lot to read and think about.
Thanks,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply