Three discounts in stored procedure, problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Three discounts in stored procedure, problem

Author

Topic

Lebowski
Starting Member

17 Posts

Posted - 2014-06-25 : 07:54:57

Hello. 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, id.rabatt)) then..
					 when 
					 Isnull(ds.rabatt, Isnull(y.rabatt, id.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.

http://mnmt.no

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 08:39:37

I did not understand your logic completely (what do the aliases u, x, y etc. represent?) I would think that you can use COALESCE like this:

COALESCE(id.rabatt, dp.rabatt, ds.rabatt) as Discount

If you need to join each table twice for picking up different date ranges, then COALESCE can be extended to include additional values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources