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.