June 26, 2014 at 3:33 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
If you would like to test something out with my data, just let me know.
June 26, 2014 at 3:48 am
Please post table definitions, easily usable sample data and expected results, as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 3:50 am
1st for all Highlight in yellow didn't worked.
Lets focus on the issue. This is what i have understand so far. if you have and ItemID = 57 data exists in STORE AND STORE2, Which Value have priority. like you only want to Show the data with STORE or with STORE2?
June 26, 2014 at 8:00 am
Sorry for the confusion.
In the sql example that I gave you with the hardcoded programs, STORE2 was the Customer's Program and STORE was the Site's Program.
Please remember that those parameters will eventually be passed in from eCommerce pages. The Site's Program will remain the same, but each customer that is on the web store to make purchases may have their own Customer Program
Therefore, the next customer that logs in may not have a Customer Program and they would get the price for the Site's Program.
And the next customer after that may have a Customer Program called UNI or whatever.
If there is a Customer Program, it will always take priority over Site Program.
I hope I have answered your Question
June 26, 2014 at 8:25 am
Shakeelr4u (6/26/2014)
Sorry for the confusion.In the sql example that I gave you with the hardcoded programs, STORE2 was the Customer's Program and STORE was the Site's Program.
Please remember that those parameters will eventually be passed in from eCommerce pages. The Site's Program will remain the same, but each customer that is on the web store to make purchases may have their own Customer Program
Therefore, the next customer that logs in may not have a Customer Program and they would get the price for the Site's Program.
And the next customer after that may have a Customer Program called UNI or whatever.
If there is a Customer Program, it will always take priority over Site Program.
I hope I have answered your Question
Here is the problem. We can't see your screen, we have no idea what you table structures are like, we don't know anything about your application, we don't know any of the business rules. The only thing we know is what you have posted. You have not provided anywhere near enough information for anything other than a random guess. Post the details requested and we can help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 9:25 am
Deleted
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2014 at 9:57 am
Deleted
Far away is close at hand in the images of elsewhere.
Anon.
June 27, 2014 at 2:32 am
Third time lucky 🙂
WITH cte (itemID,ItemProgramPricingID,Price,Quantity,Points,ProgramCodeName,RowID)
AS (
SELECTipp.itemID,
ippv.ItemProgramPricingID,
ippv.Price,
ippv.Quantity,
ippv.Points,
p.Code,
ROW_NUMBER() OVER (
PARTITION BY ipp.itemID,ippv.ItemProgramPricingID,ippv.Price,ippv.Quantity,ippv.Points
ORDER BY CASE WHEN p.Code = 'STORE2' THEN 1 ELSE 2 END ASC)
FROMItemProgramPricing ipp
JOIN ItemProgramPricingValues as ippv on ippv.ItemProgramPricingID=ipp.id
JOIN Programs AS p ON p.id = ipp.ProgramID
WHEREipp.itemID in (60,84,57,80))
SELECTitemID,
ItemProgramPricingID,
Price,
Quantity,
Points,
ProgramCodeName
FROMcte
WHERERowID = 1
ORDER BY itemID,ItemProgramPricingID,Quantity
Far away is close at hand in the images of elsewhere.
Anon.
June 27, 2014 at 8:34 am
David Burrows (6/27/2014)
Third time lucky 🙂
WITH cte (itemID,ItemProgramPricingID,Price,Quantity,Points,ProgramCodeName,RowID)
AS (
SELECTipp.itemID,
ippv.ItemProgramPricingID,
ippv.Price,
ippv.Quantity,
ippv.Points,
p.Code,
ROW_NUMBER() OVER (
PARTITION BY ipp.itemID,ippv.ItemProgramPricingID,ippv.Price,ippv.Quantity,ippv.Points
ORDER BY CASE WHEN p.Code = 'STORE2' THEN 1 ELSE 2 END ASC)
FROMItemProgramPricing ipp
JOIN ItemProgramPricingValues as ippv on ippv.ItemProgramPricingID=ipp.id
JOIN Programs AS p ON p.id = ipp.ProgramID
WHEREipp.itemID in (60,84,57,80))
SELECTitemID,
ItemProgramPricingID,
Price,
Quantity,
Points,
ProgramCodeName
FROMcte
WHERERowID = 1
ORDER BY itemID,ItemProgramPricingID,Quantity
Is this good enough to save the OP from getting fired?
http://www.sqlservercentral.com/Forums/Topic1586861-391-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 8:38 am
Please view the attached files it contains create table script which contains data also and there is one query which I have written for you to give you a result set. I need to generate a stored proc in which I have to pass values to two input parameter
1st parameter name is @customer program
2nd parameter name is @site program
Values you can pass in these parameters are given below
@site program= STORE
@Customer program =STORE2
“Script of total data” query shows me all records in which I can see the following
ItemID, Itemprogrampricingid,price,quantity,points,Programcode
Now the logic to implement IN this store proc is that , if I have itemid exists in both STORE AND STORE2 then my query will show me all the records of STORE2 (i.e. @Customer program)ONLY
And IF the itemid does not exists in STORE AND STORE2 then my query will show the records of STORE (i.e. @Siteprogram)ONLY
But I need all the records in one data set. Please help I don’t know why I am not able to build this logic please please please help me it will be easy for any one of you. [Crying]
MANY THANKS In advance
June 27, 2014 at 8:39 am
SQLRNNR (6/27/2014)
Is this good enough to save the OP from getting fired?
:ermm: Hope the job does not ride on this solution alone :pinch:
Just something I pieced together from what I could get from the query and the result the OP wanted.
May be wide of the mark :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
June 27, 2014 at 8:41 am
Sana4u (6/27/2014)
Please view the attached files it contains create table script which contains data also and there is one query which I have written for you to give you a result set. I need to generate a stored proc in which I have to pass values to two input parameter1st parameter name is @customer program
2nd parameter name is @site program
Values you can pass in these parameters are given below
@site program= STORE
@Customer program =STORE2
“Script of total data” query shows me all records in which I can see the following
ItemID, Itemprogrampricingid,price,quantity,points,Programcode
Now the logic to implement IN this store proc is that , if I have itemid exists in both STORE AND STORE2 then my query will show me all the records of STORE2 (i.e. @Customer program)ONLY
And IF the itemid does not exists in STORE AND STORE2 then my query will show the records of STORE (i.e. @Siteprogram)ONLY
But I need all the records in one data set. Please help I don’t know why I am not able to build this logic please please please help me it will be easy for any one of you. [Crying]
MANY THANKS In advance
Did you look at the solution that David posted?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 8:49 am
At what level are you sequencing STORE2 and STORE, ItemID , ItemProgramPricingID or combination of columns?
How will UNI and UNI2 fit in this processing?
*Edited*
p.s. Please post your expected results for the data you posted
Far away is close at hand in the images of elsewhere.
Anon.
June 27, 2014 at 8:53 am
David Burrows (6/27/2014)
SQLRNNR (6/27/2014)
Is this good enough to save the OP from getting fired?:ermm: Hope the job does not ride on this solution alone :pinch:
Just something I pieced together from what I could get from the query and the result the OP wanted.
May be wide of the mark :w00t:
Yeah that would be pretty sucky to have a job hanging in the balance for 1 query posted to a free forum.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2014 at 3:16 am
Please view the below thread, I have given the database table along with some data. Please help me I need SP and detail is mention here
http://www.sqlservercentral.com/Forums/Topic1586861-391-1.aspx?Update=1
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply