CODEWITHSUNDEEP
sql-server-2008stored-procedurescalculated-columnsdiscount
Lebowski
Lebowski

Reputation: 121

Advanced discount system in stored procedure

I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..

Im having big trouble designing the WHEN part of the procedure. Please take a look and help me on the way if you have time: 

                     CASE (
                 when 
                Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then.. 
                when 
                 Isnull(dp.rabatt, Isnull(x.rabatt, dp.rabatt)) then..
                 when 
                 Isnull(ds.rabatt, Isnull(y.rabatt, ds.rabatt)) then..
                 end)
                 AS 'Discount',

The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here: 

           LEFT OUTER JOIN discount AS id 
                    ON id.identifiers = isa.identifiers 
                       AND id.store = BV.name 
                       AND id.from_date <= isa.sales_date 
                       AND id.to_date >= isa.sales_date 
                       AND id.to_date < '2999-01-01' 
       LEFT OUTER JOIN discount AS u 
                    ON u.identifiers = isa.identifiers 
                       AND u.to_date = '2999-01-01'

The two others tables are designed in similar ways.

Upvotes: 0

Views: 192

Answers (1)

George Mastros
George Mastros

Reputation: 24498

You can use the coalesce function in a similar way that you are using the IsNull function. There are some subtle differences between IsNull and Coalesce, but the significant difference that would benefit your code is that you can have multiple parameters without needing to nest it.

Your code: Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt))

Is the same as: Coalesce(id.rabatt, u.rabatt, id.rabatt)

Next... there are 2 general forms for case/when.

Case (Some Condition)
     When (Value 1) Then ...
     When (Value 2) Then ...
     Else (Default Value)
     End

Or

Case When (SomeCondition = Value1) Then ...
     When (SomeCondition = Value2) Then ...
     Else DefaultValue
     End

Looking at your code snippet, it appears as though you are using the second form, but you don't have a comparison operator in the when part. I think you want something like this...

CASE When Coalesce(id.rabatt, u.rabatt, id.rabatt) Is Not NULL then.. 
     When Coalesce(dp.rabatt, x.rabatt, id.rabatt) Is Not NULL then..
     When Coalesce(ds.rabatt, y.rabatt, id.rabatt) Is Not NULL then..
     Else (Put a default value here)
     end AS [Discount]

Upvotes: 1

Related Questions