SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Your SQL Query is required


Your SQL Query is required

Author
Message
Sana4u
Sana4u
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 68
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99505 Visits: 13321
Duplicate post.
Original query can be found here:

http://www.sqlservercentral.com/Forums/Topic1586319-391-1.aspx


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search