Post reply

columns compare

  • July 2, 2014 at 9:31 am

    #308859

    --drop table #temp

    create table #temp (id int, idvalue int)

    insert into #temp(id,idvalue)

    select 1095,75

    union all

    select 1096,61

    union all

    select 1097,65

    union all

    select 1098,69

    union all

    select 1099,63

    select * from #temp

    I need to take the idvalue from maximum's id, and compare the rest idvalue from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success.

    i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.

    suggest me suitable solution. i am considering Cursors .

  • July 2, 2014 at 9:49 am

    #1726556

    Cursors? Why?

    SELECT MAX(ABS(t.idvalue - maxID.idvalue))

    FROM #temp AS t

    CROSS JOIN (SELECT TOP (1)

    idvalue

    FROM #temp AS t

    ORDER BY id DESC

    ) maxID

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 3, 2014 at 3:11 am

    #1726714

    Thanks ..it is working fine for me.

  • July 3, 2014 at 3:43 am

    #1726717

    There are quite a few ways of doing this

    SELECT

    t.id,

    t.idvalue,

    [Diff] = ABS(t.idvalue - x.idvalue),

    Flag = CASE WHEN ABS(t.idvalue - x.idvalue) > 18 THEN 'failure' ELSE 'success' END

    FROM #temp t

    CROSS APPLY (SELECT TOP 1 idvalue FROM #temp ORDER BY id DESC) x

    SELECT

    Flag = CASE WHEN MAX(ABS(t.idvalue - x.idvalue)) > 18 THEN 'failure' ELSE 'success' END

    FROM #temp t

    CROSS APPLY (SELECT TOP 1 idvalue FROM #temp ORDER BY id DESC) x

    SELECT

    Flag = CASE WHEN x.Diff > 18 THEN 'failure' ELSE 'success' END

    FROM (SELECT TOP 1 idvalue FROM #temp ORDER BY id DESC) m

    CROSS APPLY (

    SELECT TOP 1 [Diff] = ABS(t.idvalue - m.idvalue) FROM #temp t ORDER BY ABS(t.idvalue - m.idvalue) DESC

    ) x

    - but do you really have the results of only one test in your table?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

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