CODEWITHSUNDEEP
sqlsql-serverstored-procedures
R B
R B

Reputation: 423

Stored procedure to return data based on sum of 3 columns

I have a stored procedure as follows:

SELECT   DiscountId
        ,CompanyId
        ,Discount1
        ,Discount2
        ,Discount3  
        ,(Discount1+Discount2+Discount3) as Total
FROM PriceDiscount

This can return one or more rows.

I also need to check the aggregate of Discount1, Discount2 and Discount3. If the sum for any of the rows is greater than 0, I want to set a column isDiscounted = true and return it also.

Please help me - how can I achieve this?

I do not want to check this in the code or create another stored procedure for this. Hence wanted to return both the results from this stored procedure.

Upvotes: 0

Views: 239

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use case to calculate/set the isDiscounted column.

with x as (
SELECT DiscountId
    ,CompanyId
    ,Discount1
    ,Discount2
    ,Discount3  
    ,case when (Discount1+Discount2+Discount3) > 0 then 'True'
     else 'False' end  as isDiscounted
FROM PriceDiscount)
, y as (select case when isDiscounted = 'True' then count(*) end as true_count,
               case when isDiscounted = 'False' then count(*) end as false_count
        from x group by isDiscounted)
select case when true_count > 0 then 'True'
            when falsecount > 0 and truecount = 0 then 'False' end
       as final_status
from y

Edit:

with x as (
SELECT DiscountId
    ,CompanyId
    ,Discount1
    ,Discount2
    ,Discount3  
    ,case when (Discount1+Discount2+Discount3) > 0 then 'True'
     else 'False' end  as isDiscounted
FROM PriceDiscount)
, y as (select sum(case when isDiscounted = 'True' then 1 else 0 end) as true_count,
               sum(case when isDiscounted = 'False' then 1 else 0 end) as false_count 
        from x)
select case when true_count > 0 then 'True'
            when false_count > 0 and true_count = 0 then 'False' end
       as final_status
from y

Upvotes: 2

Related Questions