Complicated SQL Join

[kenny.fsw]

Hello,

Could someone please help me with this, I am trying to join several tables to formulate a customer price list. Any help or even a nudge in the right direction would be greatly appreciated.

I have the following tables

Stock Details
Stock Groups
Promotional Pricing
Special Pricing
Pricing Matrixes


All tables join back to the stock details table

From the stock details I pull the following fields
Group Code
Stock Code
Stock Description
List Price

From Stock Groups I pull the following
Stock Description
This joins the Stock Details table on the Group Code

To get a net price I need to look in promotional prices (joined on stock code), if there is no price in here I then look at the Special prices (joined on stock code)

If there is still a null value I look in the Price matrixes, in the price matrixes there are discounts that are associated with either a stock code or a product group, we look at the stock code first and then calculate the discount against the list to come up with a net price, if there is no stock code discount I then need to check for a product group discount and multiply that out, if there is no product group discount then there is a default account within the price matrixes that needs to be calculated against the list. If there is no discounts or any special or promotional pricing I then need the list price to be returned.

Here is what I have so far, I think I am pretty close, just not too sure on how to work the joins, in fact, I am not sure I am going about this the right way at all.

The customer reference is JUS100 for the customer I want, the standard discounts are held on account ref PRC001.

In the code below I used the customer reference inside the Case clause instead of the Where so that I could then query the PRC001 account.

I hope what I have said makes sense to you, because it is confusing the crap out of me

Please Login or Register  to view this content.

[ranman256]

When I run complex queries like this, I start using a 'result' table. In your case, run the 1st query and append to the result table. Then run the 'special prices' append/update.
Anything left can be updated. This way you can see the 'holes' of what is not coming across.
Is this any help?