February 13, 2021 at 9:08 pm
So I'm trying to work out logic for a "count" column that
1. Starts with an initial value if the date is today
2. That has every row's value less than the previous one by a rate, which is constant for each id
3. The date in column is 365 days including today for each id.
I have been able to capture the logic for each of these 3 bullet points, via a table with the Ids and 365 days for each Id.
The interesting part is that when the count column gets to negative, we want to show it as zero. Also, when the "Date to Arrive" Column is not null a fixed quantity (q1) is added to the count. So, when a count is negative and a q1 of 57 is added, it should show 57 and start decreasing by the rate, until it gets to negative, then it shows zero. If a q1 is added before it goes negative (or zero), it should be the current count + q1. So current count of 10 and q1 of 57 should be 67.
The logic I had worked until the negative (zero) counts get q1 added. Any help/ideas on how to go about this problem? Thanks
February 14, 2021 at 4:39 am
Put yourself in my place and pretend you know absolutely nothing about the problem that you just described and then read what you just posted and compare that to the generic means-nothing annotated graphics you posted and understand when I say that I have no clue as to what you're trying to do. Also, see the first link in my signature line below for how to post readily consumable data.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2021 at 4:19 pm
To the OP it makes clear sense no doubt. In this case the OP says they have part of the solution but only offer pictures of the problem. Maybe we could see "The logic I had worked..."? There were a couple of "running total" questions on SSC a few weeks ago. The universe where the question makes sense could be where there's an initial count which is exhaustively reduced to zero (at some point over a fixed span of 365 days) and to which additional counts are added at irregular intervals. Here's code. It uses a tally function to generate the date ranges. In order to attrite the additional counts requires SUM OVER to be applied first. Then 's_cnt' and 'a_cnt' are the columns to which final SUM() OVER's are applied. The sum of the 'strt_cnt' and 'arrv_cnt' columns is intended to equal the OP's "count" column. In reality maybe it could all be different
declare
@today date=cast(getdate() as date);
with
counts_cte(id, start_count, rate) as (
select 50363, 38, -0.045
union all
select 50365, 12, -0.10),
arrivals_cte(id, arrival_dt, q_add) as (
select 50363, '20210615', 57
union all
select 50363, '20210622', 57
union all
select 50363, '20210629', 117),
expand_cte(id, start_count, rate, dt, arrival_dt, q_add, sum_arrivals) as (
select c.*, v.dt, ac.arrival_dt, ac.q_add,
sum(isnull(ac.q_add, 0)) over (partition by c.id order by v.dt)
from counts_cte c
cross join dbo.fnTally(0, 364) fn
cross apply (values (dateadd(day, fn.n, @today))) v(dt)
left join arrivals_cte ac on c.id=ac.id
and v.dt=ac.arrival_dt)
select ex.*, calc_count.s_cnt, calc_arriv.a_cnt,
case when (sum(calc_count.s_cnt) over (partition by ex.id order by ex.dt))<0 then 0
else (sum(calc_count.s_cnt) over (partition by ex.id order by ex.dt)) end strt_cnt,
case when (sum(calc_arriv.a_cnt) over (partition by ex.id order by ex.dt))<0 then 0
else (sum(calc_arriv.a_cnt) over (partition by ex.id order by ex.dt)) end arrv_cnt
from expand_cte ex
cross apply (values (case when ex.dt=@today
then ex.start_count
else ex.start_count*ex.rate end)) calc_count(s_cnt)
cross apply (values (case when ex.arrival_dt is not null
then ex.q_add+(ex.sum_arrivals-ex.q_add)*ex.rate
else ex.sum_arrivals*ex.rate end)) calc_arriv(a_cnt)
order by ex.id, ex.dt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 14, 2021 at 4:40 pm
Please follow forum netiquette and post DDL. I presume that your boss does not make you try to program from graphics and pretty pictures. I also hope you know the tables have no ordering; they model sets, and not sequences. Any ordering that you get. You have to put in explicitly as a value in a column of a row in the table (ever read Dr. Codd? I believe this is his 12th law). Do you really want help and are you willing to try again?
Please post DDL and follow ANSI/ISO standards when asking for help.
February 14, 2021 at 5:23 pm
Apologies for the incomprehensible post.
While I have seen an answer from Ten Centuries that I will try to implement, I am currently reading and researching the right nettiquette for posting on these forums as I have benefited from here a lot in the past. I will be tidying up my post once I'm sure that my question meets your requirements and is easy to understand.
Apologies and thanks for the already helpful information
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply