How to redesign a query

  • 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
    ('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
    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
    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


  • 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

  • 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.

  • 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

  • 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.


  • 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:

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

  • 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
    @Usage = Usage = 1.0 * (ThisValue - @PrevValue) / nullif(datediff(day, @PrevDate, ThisDate), 0),

    @Result = Result =
    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



  • 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 =
    WHEN @Usage IS NULL OR ROUND(@Usage, 0) BETWEEN @MinAllowedUsage AND @MaxAllowedUsage
    THEN 1
    ELSE 0
    ,Result = @Result
    ,@PrevDate = CASE WHEN @Result = 1 THEN ThisDate ELSE @PrevDate END
    ,@PrevValue = CASE WHEN @Result = 1 THEN ThisValue ELSE @PrevValue END
    ,@Anchor = ThisDate

    select * from #t order by ThisDate;

    Another alternative would be to use a .Net DataReader in the middle tier.


    • This reply was modified 1 month, 1 week ago by  Ken McKelvey.
  • 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