June 28, 2014 at 3:40 am
Anyone Please help 🙁
June 28, 2014 at 3:45 am
Please view the below thread. I have attached the database table along with data . and detail of required Sp is also mentioned below in this thread.
Please help 🙁
June 28, 2014 at 4:37 am
please help me 🙁
June 28, 2014 at 7:40 am
In real db there are only two programs STORE and STORE2 so you should forget about the other two i.e. UNI and UNI2.
as I told that I will put the values in one input parameter of sp i.e. @CustomerProgram (this value will be given by the end user)
@Siteprogram will always remain same so you can SET this parameter value to STORE and it always remains the same.
my SP will check the value of @Customerprogram parameter and then it checks all the itemids and there program code i.e. STORE and STORE2
so, the items those have both program code i.e. STORE2 AND STORE should not show me the records of STORE(@SITEPROGRAM)
and those items those have store2 PROGRAMCODE will show me only the records of STORE2(@customerProgram)
For example.
If you see the Itemid =57 then I have Two programs for this item i.e. STORE and Store2
So in the case of Itemid =57 my sp query should return me only those rows those are having STORE2 Programcode.
and in case of itemid=2 I dont have STORE2 so my SP query should give me records of STORE only.
column sequence of the query should be like this
ITEMIDItemProgramPricingIDPriceQuantityPointsProgramCodeName
plz let me know if you need anything else
June 28, 2014 at 7:42 am
SQLRNNR (6/27/2014)
David Burrows (6/27/2014)
SQLRNNR (6/27/2014)
Is this good enough to save the OP from getting fired?
In real db there are only two programs STORE and STORE2 so you should forget about the other two i.e. UNI and UNI2.
as I told that I will put the values in one input parameter of sp i.e. @CustomerProgram (this value will be given by the end user)
@Siteprogram will always remain same so you can SET this parameter value to STORE and it always remains the same.
my SP will check the value of @Customerprogram parameter and then it checks all the itemids and there program code i.e. STORE and STORE2
so, the items those have both program code i.e. STORE2 AND STORE should not show me the records of STORE(@SITEPROGRAM)
and those items those have store2 PROGRAMCODE will show me only the records of STORE2(@customerProgram)
For example.
If you see the Itemid =57 then I have Two programs for this item i.e. STORE and Store2
So in the case of Itemid =57 my sp query should return me only those rows those are having STORE2 Programcode.
and in case of itemid=2 I dont have STORE2 so my SP query should give me records of STORE only.
column sequence of the query should be like this
ITEMID ItemProgramPricingID Price Quantity Points ProgramCodeName
plz let me know if you need anything else
June 28, 2014 at 7:43 am
Have you tested the code that David Burrows posted above in this thread?
June 28, 2014 at 8:43 am
Lynn Pettis (6/28/2014)
Have you tested the code that David Burrows posted above in this thread?
yes I tested that code but its not working below is the result set I got from David burrows query. You can see that Item id 57 is showing records against STORE and STORE2 which is not correct. Itemid 57 should show the records of STORE2 only.
Work flow or logic of this query is given below.
1) pass input parameter @Customerprogram a value e.g. 'STORE2'
2) Hard code @siteprogram parameter value to 'STORE' which will always remain same
3) check all progam code of all items
4) if item is having program code = @customerprogram parameter value as well as @siteprogram parameter value
5) then show all records (itemIDItemProgramPricingIDPriceQuantityPointsProgramCodeName) of @Customerprogram code i.e. 'STORE2 in my example ( means that if both program exists give priority to customer program)
6) else show all the records of @siteprogram parameter value i.e. always be 'STORE'
7) Show have all these values in a single result set.
itemIDItemProgramPricingIDPriceQuantityPointsProgramCodeName
572325.500010STORE
572324.5000150STORE
572323.5000250STORE
572322.5000350STORE
572321.5000450STORE
572320.5000550STORE
572319.5000650STORE
572318.5000750STORE
572050150.0000115UNI
572050140.00001014UNI
572050130.00002013UNI
572050120.00003012UNI
572050110.00004011UNI
572050100.00005010UNI
57205090.0000609UNI
57205080.0000708UNI
5720541555.00001155UNI2
572055255.000010STORE2
6069.990010STORE
8015.000010STORE
80205350.000015UNI
80205340.000024UNI
80205330.000033UNI
8020592.220010STORE2
841013.500010STORE
84206022.220010STORE2
June 28, 2014 at 9:54 am
Okay, now using your sample data show us what the output should look like if the query runs correctly.
June 28, 2014 at 10:23 am
Lynn Pettis (6/28/2014)
Okay, now using your sample data show us what the output should look like if the query runs correctly.
Please view the attached file. My out put should be like that.
June 28, 2014 at 12:07 pm
Have you tried making any adjustments to the provided code to make it work for your scenario?
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:50 pm
Give this a shot:
declare @ProgamCodeIn nvarchar(10);
set @ProgamCodeIn = N'STORE2';
with baseitems as (
select
ipp.ItemID,
ipp.ProgramID,
p.Code,
rn = row_number() over (partition by ipp.ItemID order by p.ID desc)
from
ItemProgramPricing ipp
inner join Programs AS p
ON p.ID = ipp.ProgramID
where
p.Code = N'STORE' or
p.Code = @ProgamCodeIn
), selectitems as (select ItemID, Code from baseitems where rn = 1)
select
ipp.ItemID,
ippv.ItemProgramPricingID,
ippv.Price,
ippv.Quantity,
ippv.Points,
p.Code as ProgramCodeName,
p.ID
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
inner join selectitems si
on (ipp.ItemID = si.ItemID and p.Code = si.Code)
where
ippv.ItemProgramPricingID is not null
order by
ipp.ItemID,
ippv.ItemProgramPricingID;
June 29, 2014 at 11:56 pm
Lynn Pettis (6/28/2014)
Give this a shot:
declare @ProgamCodeIn nvarchar(10);
set @ProgamCodeIn = N'STORE2';
with baseitems as (
select
ipp.ItemID,
ipp.ProgramID,
p.Code,
rn = row_number() over (partition by ipp.ItemID order by p.ID desc)
from
ItemProgramPricing ipp
inner join Programs AS p
ON p.ID = ipp.ProgramID
where
p.Code = N'STORE' or
p.Code = @ProgamCodeIn
), selectitems as (select ItemID, Code from baseitems where rn = 1)
select
ipp.ItemID,
ippv.ItemProgramPricingID,
ippv.Price,
ippv.Quantity,
ippv.Points,
p.Code as ProgramCodeName,
p.ID
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
inner join selectitems si
on (ipp.ItemID = si.ItemID and p.Code = si.Code)
where
ippv.ItemProgramPricingID is not null
order by
ipp.ItemID,
ippv.ItemProgramPricingID;
O MY GOD its working absolutely as per the requirement . I AM SOOOOOOOOOO THANKFUL to you Lynn Pettis. You SAVED ME. GOD BLESS YOU Lynn Pettis. I REALLY REALLY Appreciate . Thank you soo much for the HELP. You will be always in my prayers.
June 30, 2014 at 7:38 am
Great. Now, be sure you take the time to work through the code and understand what it is doing since you have to support it. If you have questions please ask, but try to see if you can figure it out first as it will help with understanding the code.
Viewing 13 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply