June 26, 2014 at 3:31 am
your urgent help is highly appreciated as this is a high priority task.
Here is a little background information:
One item may have one to many Program Pricing records.
Per item, there will always be a record (or set of records if there are quantity price breaks as shown below for Itemid=57) for the Site's Program (this is what I will call the SiteProgram).
Per item, there may be a record (or set of records if there are quantity price breaks) for the Customer's Program (this is what I will call the SecondProgram).
If there is a record(s) for the Customer's Program, then that is the record(s) that needs to be returned, not the one for the SiteProgram.
Right now by doing a Union, I can get what I need. However, if an item has a SiteProgram and a SecondProgram, then I get records back for both Programs.
I can use the Program column (ProgramCodeName) and then get the record(s) that I need, but that is inefficient and Tony thinks there should be a way to get back just what I need.
Note - a particular item may have a SecondProgram but that does not mean that all items will have a SecondProgram.
This is what the query looks like right now:
Note - just to keep the results set smaller and more manageable for initial testing purposes, I have hardcoded itemID's for four items and the name of the
SiteProgram (STORE) and the SecondProgram (STORE2). The final query will pass these in as parameters but I will take care of that.
select
IPP.ITEMID,
IPPV.ItemProgramPricingID,
IPPV.Price,
IPPV.Quantity,
IPPV.Points,
p.Code as ProgramCodeName
FROM ItemProgramPricing ipp
LEFT join ItemProgramPricingValues as ippv on ippv.ItemProgramPricingID=ipp.id
LEFT JOIN Programs AS p ON p.id = ipp.ProgramID
LEFT join ItemProgramPricingWebCategories as ippwc on ippwc.ItemProgramPricingID = ipp.id
LEFT join WebCategories as wc on wc.id=ippwc.WebCategoryID
where p.Code='STORE' and ippv.ItemProgramPricingID is not null and itemID in (60,84,57,80)
Union
select
IPP.ITEMID,
IPPV.ItemProgramPricingID,
IPPV.Price,
IPPV.Quantity,
IPPV.Points,
p.Code as ProgramCodeName
FROM ItemProgramPricing ipp
LEFT join ItemProgramPricingValues as ippv on ippv.ItemProgramPricingID=ipp.id
LEFT JOIN Programs AS p ON p.id = ipp.ProgramID
LEFT join ItemProgramPricingWebCategories as ippwc on ippwc.ItemProgramPricingID = ipp.id
LEFT join WebCategories as wc on wc.id=ippwc.WebCategoryID
where p.Code='STORE2' and ippv.ItemProgramPricingID is not null and itemID in (60,84,57,80)
ORDER BY ITEMID,ItemProgramPricingID,QUANTITY
This is the results set, but I only want to have returned the records highlighted in yellow
I have put a blank line between each of the different items just to make it easier for you to read
ITEMID ItemProgramPricingID Price Quantity Points ProgramCodeName
57 23 25.5000 1 0 STORE
57 23 24.5000 15 0 STORE
57 23 23.5000 25 0 STORE
57 23 22.5000 35 0 STORE
57 23 21.5000 45 0 STORE
57 23 20.5000 55 0 STORE
57 23 19.5000 65 0 STORE
57 23 18.5000 75 0 STORE
57 2055 25.0000 1 0 STORE2
57 2055 24.0000 15 0 STORE2
57 2055 23.0000 25 0 STORE2
57 2055 22.0000 35 0 STORE2
57 2055 21.0000 45 0 STORE2
57 2055 20.0000 55 0 STORE2
57 2055 19.0000 65 0 STORE2
57 2055 18.0000 75 0 STORE2
60 6 9.9900 1 0 STORE (this one is ok as is since it does not have a record for Store2)
80 1 5.0000 1 0 STORE
80 2059 2.2200 1 0 STORE2
84 10 13.5000 1 0 STORE
84 2060 22.2200 1 0 STORE2
June 26, 2014 at 3:44 am
Duplicate post.
Original query can be found here:
http://www.sqlservercentral.com/Forums/Topic1586319-391-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply