October 24, 2021 at 11:13 am
Hi
Result is determined by the calculated Usage, With the below query, the result for the last output row is 0 because usage is based on the previous date and value (using LAG).
How can I rewrite this so that the calculated usage is based on the previous date and value where the result is 1. So the last row would become: Usage = (155 - 125) / 10 days = 3 and hence the Result is 1?
Thanks for any help, as stuck on it.
declare @MinAllowedUsage int = 0
declare @MaxAllowedUsage int = 10
declare @T table
(
ThisDate date not null primary key clustered,
ThisValue int not null,
Usage decimal(18, 2) null,
Result int null
)
insert @T
(
ThisDate,
ThisValue,
Usage,
Result
)
values
('2021-01-01', 100, null, 1),
('2021-01-06', 125, null, null),
('2021-01-08', 325, null, null),
('2021-01-16', 155, null, null)
;with T as
(
select
ThisDate,
ThisValue,
Usage = 1.0 * (ThisValue - lag(ThisValue) over(order by ThisDate))
/ nullif(datediff(day, lag(ThisDate) over(order by ThisDate), ThisDate), 0)
from @T
),
T1 as
(
select
ThisDate,
ThisValue,
Usage,
Result = case
when Usage is null or round(Usage, 0) between @MinAllowedUsage and @MaxAllowedUsage
then 1 else 0 end
from T
)
select *
from T1
order by ThisDate
October 24, 2021 at 4:40 pm
What are the expected Usage and Result values for each row of the @t table?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 24, 2021 at 4:50 pm
The first three rows output from my attempted query are what I expect.
The last row (2021-01-16) I want to be Usage = 3 and Result = 1. Currently, it is Usage = -21.25 and Result = 0.
October 24, 2021 at 5:57 pm
Sorry it's not clear to me. Maybe a more detailed example could help
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 24, 2021 at 6:05 pm
You have the input in @T.
The output required is:
'2021-01-01', 100, null, 1
'2021-01-06', 125, 5.00, 1
'2021-01-08', 325, 100.0, 0
'2021-01-16', 155, 3.00, 1
I don't know how I can make it any clearer, and I've explained the issue.
October 24, 2021 at 9:28 pm
As usage and result are dependent on previous calculations, which in turn are dependent on the previous calculations, windowed functions will not work. I would be inclined to use a cursor or the quirky update:
October 25, 2021 at 8:13 am
Thanks Ken, for the confirmation that it cannot be done through windowing functions. This post is actually a follow-up to an earlier one I put up - Is loop necessary? - where I made an even poorer attempt to explain the problem...
I didn't want to use a loop if there was another way as been caught out before, and performance is important in my case. I will look at the Quirky Update that I have used in the past (before running totals could be done with windowing functions?).
October 25, 2021 at 3:14 pm
Got it working with a version of the Quirky Update, so thanks to Jeff Moden.
Changed it to a temp table rather than table variable and ThisDate is a clustered index. Need to try it on my real case of a couple of million rows, but confident performance will be good.
declare @PrevDate date
declare @PrevValue int
declare @Usage decimal(18, 2)
declare @Result int
update #T
set
@Usage = Usage = 1.0 * (ThisValue - @PrevValue) / nullif(datediff(day, @PrevDate, ThisDate), 0),
@Result = Result =
case
when @Usage is null or round(@Usage, 0) between @MinAllowedUsage and @MaxAllowedUsage
then 1 else 0 end,
@PrevDate = case when @Result = 1 then ThisDate else @PrevDate end,
@PrevValue = case when @Result = 1 then ThisValue else @PrevValue end
from #T with (tablockx)
option (maxdop 1)
select *
from #T
order by ThisDate
drop table #T
October 25, 2021 at 4:39 pm
1. Jeff recommends always using an anchor and I am not sure that @PrevDate counts.
2. It seems there can sometimes be problems with the 3 part update so it might be wiser to switch to 2 part updates.
DECLARE @PrevDate date
,@PrevValue int
,@Usage decimal(18, 2)
,@Result int
,@Anchor date
,@MinAllowedUsage int = 0
,@MaxAllowedUsage int = 10;
UPDATE #t
SET @Usage = 1.0 * (ThisValue - @PrevValue) / NULLIF(DATEDIFF(day, @PrevDate, ThisDate), 0)
,Usage = @Usage
,@Result =
CASE
WHEN @Usage IS NULL OR ROUND(@Usage, 0) BETWEEN @MinAllowedUsage AND @MaxAllowedUsage
THEN 1
ELSE 0
END
,Result = @Result
,@PrevDate = CASE WHEN @Result = 1 THEN ThisDate ELSE @PrevDate END
,@PrevValue = CASE WHEN @Result = 1 THEN ThisValue ELSE @PrevValue END
,@Anchor = ThisDate
FROM #t WITH (TABLOCKX)
OPTION (MAXDOP 1);
select * from #t order by ThisDate;
Another alternative would be to use a .Net DataReader in the middle tier.
October 25, 2021 at 4:55 pm
Yes, the two-part method seems to be safer, from Jeff's article. It concludes with recommending an anchor but I couldn't find any examples of this earlier in the article so not sure what it is about.
A .NET CLR is not available to me, so has to be in SQL. The quirky update has solved a big problem. From using it before for a running total, I know it is very fast.
Thanks for all you help.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy