How to redesign a query

Question

Post reply

How to redesign a query

kuopaz

Default port

Points: 1449

October 24, 2021 at 11:13 am

#3943753

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

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Collins SSCrazy Points: 2077 More actions · Answer 1

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

kuopaz Default port Points: 1449 More actions · Answer 2

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.

Steve Collins SSCrazy Points: 2077 More actions · Answer 3

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

kuopaz Default port Points: 1449 More actions · Answer 4

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.

Ken McKelvey SSCoach Points: 18775 More actions · Answer 5

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:

https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten

kuopaz Default port Points: 1449 More actions · Answer 6

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

kuopaz Default port Points: 1449 More actions · Answer 7

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

Ken McKelvey SSCoach Points: 18775 More actions · Answer 8

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.

This reply was modified 2 years, 6 months ago by Ken McKelvey.

kuopaz Default port Points: 1449 More actions · Answer 9

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.