Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Your SQL Query is required Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:28 PM
Points: 27, Visits: 50
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
Post #1586317
Posted Thursday, June 26, 2014 3:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1586327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse