Post reply

A little complicated Query

  • June 3, 2015 at 8:32 am

    #398537

    Dear SQL experts,

    Thanks for looking into my question.

    I have a scenario for which I can only write a query (No stored procedure 🙁 )..I am not sure how to implement it in a query.

  • June 3, 2015 at 8:35 am

    #1802648

    Implement *what* in a query?

    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

  • June 3, 2015 at 8:48 am

    #1802669

    Sorry..pressed submit before completion...

    Dear SQL experts,

    Thanks for looking into my question.

    I have a scenario for which I can only write a query (No stored procedure 🙁 )..I am not sure how to implement it in a query.

    I have a table which contains 4 columns TierLevelId, TierId , RangeLow, RangeHigh with the following sample data

    -- CREATE TABLE sampletbl (TierLevelId int, TierId int, RangeLow int, RangeHigh INT)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 1,1,2,3)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 2,1,4,6)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 3,1,6,9)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 4,2,1,3)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 5,2,4,8)

    INSERT INTO dbo.sampletbl ( TierLevelId ,TierId ,RangeLow ,RangeHigh) VALUES ( 6,2,9,10)

    I will supplied with TierId and single Range value. I should fetch the TierLevel Id for that Tier Id with in the Range value.

    For example with the above data, if I am given Tierid 1 and Range value 5, the query should return TierLevelId = 2.

    But, If the range value is lower than the lowest minimum range value available for that tier, it should return the TierlevelId of the lowest range and the same with higher range as well.

    For example, If I am given TierId of 1 and Range Value of 1, the query should return TierLevelId = 1

    If I am given TierId of 1 and Range value of 15, the query should return 3.

    How to acheive all this in a single query...

  • June 3, 2015 at 8:51 am

    #1802670

    Siva Ramasamy (6/3/2015)

    If I am given TierId of 1 and Range value of 15, the query should return 3.

    Why? 15 is not between 6 and 9

    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

  • June 3, 2015 at 9:01 am

    #1802680

    DECLARE @TierID INT = 1;

    DECLARE @Range INT = 1

    SELECT s.TierLevelId

    FROM dbo.sampletbl AS s

    INNER JOIN (SELECT s.TierId,

    MIN(RangeLow) AS LowestRange,

    MAX(RangeHigh) AS HighestRange

    FROM dbo.sampletbl AS s

    GROUP BY s.TierId

    ) AS Ranges ON s.TierId = Ranges.TierId

    WHERE s.TierId = @TierID

    AND ((@Range BETWEEN s.RangeLow AND s.RangeHigh)

    OR (@Range < s.RangeLow AND s.RangeLow = Ranges.LowestRange)

    OR (@Range > s.RangeHigh AND s.RangeHigh = Ranges.HighestRange));

    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

  • June 3, 2015 at 9:01 am

    #1802681

    Hi Gail,

    Yeah. Since the supplied range is higher than the highest range value, It should return the TierLevelId for the maximum range.

    Thanks!

  • June 3, 2015 at 9:05 am

    #1802685

    Brilliant...

    Thank you very much Microsoft Certified MASTER...!!

  • June 3, 2015 at 9:26 am

    #1802696

    I assume that you mean a single statement when you mention "no stored procedure".

    Here's a possibility and I'm sure that there are many other ways to do it.

    DECLARE @TierId int = 1,

    @Range int = 5 --Change the values as desired.

    SELECT TierLevelId

    FROM sampletbl

    WHERE TierId = @TierId

    AND @Range BETWEEN RangeLow AND RangeHigh

    UNION ALL

    SELECT TierLevelId

    FROM sampletbl

    WHERE TierId = @TierId

    AND TierLevelId IS NOT NULL

    AND RangeLow = (SELECT CASE WHEN MIN( RangeLow) > @Range THEN MIN( RangeLow)

    WHEN MAX( RangeHigh) < @Range THEN MAX( RangeLow)

    END

    FROM sampletbl

    WHERE TierId = @TierId

    AND @Range NOT BETWEEN RangeLow AND RangeHigh)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 3, 2015 at 9:29 am

    #1802698

    Thanks Luis

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

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