locked
Sql query Data Based on condition RRS feed

  • Question

  • User2033107836 posted

    Hello

    Assalmuallikum
    
    ItemName		InvNewRate	LooseNewRate
    A		         10		12
    B		          5		7
    C		          2		4
    D		          1		3
    
    If user enter  D & 1 then i want to show "Seal"
    
    if user enter D & 3 then i want to show "Loose"

    Need SQL Query

    Wednesday, November 16, 2016 6:42 PM

Answers

  • User-183374066 posted

    Try following query

    CREATE TABLE MyTable5
        (
          ItemName VARCHAR(10) NOT NULL ,
          InvNewRate INT NOT NULL ,
          LooseNewRate INT NOT NULL
        );
    
    INSERT  INTO MyTable5( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'A' ,10 ,12);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'B' ,5 ,7);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'C' ,2 ,4);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'D' ,1 ,3);
    

    Create Procedure

    CREATE PROCEDURE Select_Value
        @Name VARCHAR(10) ,
        @Value INT
    AS
        BEGIN
    
            IF EXISTS ( SELECT  *
                        FROM    MyTable5
                        WHERE   ItemName = @Name
                                AND InvNewRate = @Value )
                SELECT  'Seal';
    
            ELSE
                IF EXISTS ( SELECT  *
                            FROM    MyTable5
                            WHERE   ItemName = @Name
                                    AND LooseNewRate = @Value )
                    SELECT  'Loose';
    
                ELSE
                    SELECT  'Doesn''t exists.';
        END;

    Then 

    EXEC Select_Value 'D', 3; -- output: Loose
    EXEC Select_Value 'D', 1; --output: Seal
    EXEC Select_Value 'D', 2; -- Doesn't exists.
    EXEC Select_Value 'A', 10; -- output: Seal
    EXEC Select_Value 'A', 12; -- output: Loose
    EXEC Select_Value 'A', 8; -- output: Doesn't exists.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 16, 2016 7:43 PM

All replies

  • User-183374066 posted

    Try following query

    CREATE TABLE MyTable5
        (
          ItemName VARCHAR(10) NOT NULL ,
          InvNewRate INT NOT NULL ,
          LooseNewRate INT NOT NULL
        );
    
    INSERT  INTO MyTable5( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'A' ,10 ,12);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'B' ,5 ,7);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'C' ,2 ,4);
    INSERT  INTO MyTable5 ( ItemName ,InvNewRate ,LooseNewRate) VALUES  ( 'D' ,1 ,3);
    

    Create Procedure

    CREATE PROCEDURE Select_Value
        @Name VARCHAR(10) ,
        @Value INT
    AS
        BEGIN
    
            IF EXISTS ( SELECT  *
                        FROM    MyTable5
                        WHERE   ItemName = @Name
                                AND InvNewRate = @Value )
                SELECT  'Seal';
    
            ELSE
                IF EXISTS ( SELECT  *
                            FROM    MyTable5
                            WHERE   ItemName = @Name
                                    AND LooseNewRate = @Value )
                    SELECT  'Loose';
    
                ELSE
                    SELECT  'Doesn''t exists.';
        END;

    Then 

    EXEC Select_Value 'D', 3; -- output: Loose
    EXEC Select_Value 'D', 1; --output: Seal
    EXEC Select_Value 'D', 2; -- Doesn't exists.
    EXEC Select_Value 'A', 10; -- output: Seal
    EXEC Select_Value 'A', 12; -- output: Loose
    EXEC Select_Value 'A', 8; -- output: Doesn't exists.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 16, 2016 7:43 PM
  • User281315223 posted

    Is there any specific method behind this? What is the number value referring to (i.e. D & 1 is "ItemName" and "InvNewRate" or?). If you elaborate a bit more on exactly what you are trying to do with this query, it may be easier to help you out.

    Wednesday, November 16, 2016 7:45 PM
  • User-1716253493 posted

    Waalaikum salam,

    Your question is not clear. You have D,1,3

    Do you mean like this

    Select ItemName, InvNewRate, LooseNewRate, (CASE WHEN InvNewRate>=LooseNewRate THEN 'Seal' ELSE 'Loose' END) as [Result] from yourtable

    Thursday, November 17, 2016 12:42 AM