none
Comparing values of 2 columns RRS feed

  • Question

  • Dear All,

    I have a table like this

    DECLARE @Test TABLE
    (
       Qty INT,
       QtyCart INT
    )
    
    INSERT INTO @Test (Qty, QtyCart) VALUES (200, 10)
    INSERT INTO @Test (Qty, QtyCart) VALUES (3, 11)
    INSERT INTO @Test (Qty, QtyCart) VALUES (40, 10)
    
    
    select * from @Test
    

    I want to compare value of Qty with QtyCart. If all values in Qty column are greater than QtyCart then it should return true and if any 1 value is less than it should return false. Like in the table above 3 is less than 11 so it will return false. I think it should be done in a store procedure

    Any help would be greatly appreciated

    Tuesday, January 22, 2019 6:56 PM

All replies

  • select  Case when SUM(case when Qty <= QtyCart then 1 else 0 end)>0 then 'False' Else  'True' End
    from @Test

    Tuesday, January 22, 2019 7:14 PM
    Moderator
  • like this if what you need is a proc

    CREATE PROC ProcName
    AS
    IF EXISTS (
    SELECT 1
    FROM @Test
    WHERE Qty < QtyCart
    )
    SELECT 'False'
    ELSE
    SELECT "True'
    GO

    if you want is a simple select to return the status you can simply do

    SELECT CASE WHEN EXISTS (
    SELECT 1
    FROM @Test
    WHERE Qty < QtyCart
    ) THEN 'False' ELSE 'True' END AS YourStatus


    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue 
    Visakh 
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Tuesday, January 22, 2019 7:20 PM
  • Hi Sammy,

    In your posting , it is not necessary to use store procedure. So in following script , I will provide two forms to you and you can choose it freely.

     

    Please try it.

     
    DECLARE @Test TABLE
    (
       Qty INT,
       QtyCart INT
    )
    
    INSERT INTO @Test (Qty, QtyCart) VALUES (200, 10)
    INSERT INTO @Test (Qty, QtyCart) VALUES (3, 11)
    INSERT INTO @Test (Qty, QtyCart) VALUES (40, 10)
    
    declare @min_Qty int =(select min(Qty) from @Test)
    if @min_Qty>Any(select QtyCart from @Test)
    PRINT 'TRUE'   
    ELSE  
    PRINT 'FALSE' 
    /*
    FALSE
    */
    
    ----------->store procedure<-------------
    go
    alter procedure getresult
    as
    begin 
    DECLARE @Test TABLE
    (
       Qty INT,
       QtyCart INT
    )
    INSERT INTO @Test (Qty, QtyCart) VALUES (200, 10)
    INSERT INTO @Test (Qty, QtyCart) VALUES (3, 11)
    INSERT INTO @Test (Qty, QtyCart) VALUES (40, 10)
    declare @min_Qty int =(select min(Qty) from @Test)
    if @min_Qty>Any(select QtyCart from @Test)
    PRINT 'TRUE'   
    ELSE  
    PRINT 'FALSE' 
    end 
    exec getresult;
    /*
    FALSE
    */

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 23, 2019 2:55 AM