stored procedure help- select row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure help- select row

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 07:17:35

Experts,

Hello !

Please bear with me for the long post !
I have a procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_products_BLprice2] @usname varchar(100)
as
begin
SELECT MobID,

MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END)-MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS BasepriceGap,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Myprice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Baseprice,
MAX(CASE WHEN My = 1 THEN mobid ELSE NULL END) AS ID,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) AS MyBottprice,
MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) - MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS BotPriceGap
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
Bottomline_Price,

ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price) AS low,
CASE Merchant
WHEN @usname THEN 1
ELSE 0
END AS My
FROM tbl_master_Merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

SELECT DISTINCT m.ID,t1.MPN_SKU as ProductMPN,
t1.Product_Name as Description,
m.Myprice,
-- m.Baseprice,
-- m.BasepriceGap,
m.MyBottprice,
m.BotPriceGap,
m.LowPrice,
m.LowPriceMerchant as LowMerchant,
m.HighPrice,
m.HighPriceMerchant as HighMerchant

FROM ##Temp AS m
INNER JOIN tbl_master_Product AS t1 ON t1.MobID = m.MobID

drop table ##temp

end

and it creates the following output

93668 44.99 0.00 0.00 44.99 waterfilterexchange 9.96 Amazon.com

The actual table contains the following values
mobid bottomline_price merchant price
93668 15.54 Amazon.com 9.96
93668 0.00 waterfilterexchange 44.99

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 07:22:30

MOBID My_price Mybottomline_price botprice_gap low_price lowprice_merchant high_price highprice_merchant
93668 44.99 0.00 0.00 44.99 waterfilterexchange 9.96 Amazon.com

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 07:27:35

My need is (If I am waterfilterexchange )
MOBID My_price Mybottomline_price botprice_gap low_price lowprice_merchant high_price highprice_merchant
93668 44.99 0.00 -15.54 9.96 Amazon.com 44.99 waterfilterexchange

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 07:43:04

Actual output generated based on the bottom_line price column the low price and high price merchant are chosen based on the bottomline_price
My need is the low&high merchants select using price column

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 07:43:57

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-06 : 10:16:59

need some help...

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 06:52:55

quote:
My need is the low&high merchants select using price column


select max(Bottomline_Price)over(partition by merchantid)as highprice,
       min(Bottomline_Price)over(partition by merchantid)as lowprice
from yourtable

PBUH

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-07 : 06:59:41

Tnx for the response...

This will select all datas generally from my table ..
I need that for a particular mobid i need to find the low price and high price in a single column named price

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-07 : 07:10:34

In my table I have like this..
For a single product I have more than two merchants.. And their details are present in my table..
Bottomline_price is one column and price is another column ...
Now i want to select that by passing the merchant name find the low & high price in price column and also that merchants bottom_line price...

Sorry for my poor communication...

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 07:13:01

Juts post some sample o/p.

PBUH

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-07 : 07:41:32

mobid...bott_price.. merchant......price
93212 0.00 PCNation.com.......131.69
93212...153.76...waterfilterexchange...134.99
93212...154.44...ReStockIt.com...138.49
93212...149.95...RadioShack.com....149.95
93212...132.99...Amazon.com.... 132.99
93212...131.98...TheNerds.net...121.99
93212...135.24...CostCentral.com...126.72
93212...158.02...EcomSF... 145.81

this is my table like this more than thousand mobid s are present..

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-07 : 08:09:50

In future provide test data in a comsumable format:


CREATE TABLE #Merchant
(
	mobid int NOT NULL
	,merchant varchar(35) NOT NULL
	,price money NOT NULL
	,bottomline_price money NOT NULL
)
INSERT INTO #Merchant
SELECT 93668, 'Amazon.com', 9.96, 15.54
UNION ALL SELECT 93668, 'waterfilterexchange', 44.99, 0.00

Also provide the result in a readable format. A select statement will do.


-- With @usname = 'waterfilterexchange'
SELECT
	93668 AS MOBID
	,44.99 AS My_price
	,0.00 AS Mybottomline_price
	,-15.54 AS botprice_gap
	,9.96 AS low_price
	,'Amazon.com' AS lowprice_merchant
	,44.99 AS high_price
	,'waterfilterexchange' AS highprice_merchant

You also state:

"Actual output generated based on the bottom_line price column the low price and high price merchant are chosen based on the bottomline_price
My need is the low&high merchants select using price column"

Given the above the obvious answer is to change the ORDER BY part of your ROW_NUMBER() functions from
Bottomline_Price to Price.

This seems to work:


DECLARE @usname varchar(35)
SET @usname = 'waterfilterexchange'

SELECT MobID
	,MAX(CASE WHEN My = 1 THEN Price END) AS Myprice
	,MAX(CASE WHEN My = 1 THEN Bottomline_Price END) AS MyBottprice
	,MAX(CASE WHEN My = 1 THEN Bottomline_Price END)
		- MAX(CASE WHEN low = 1 THEN Bottomline_Price END) AS BotPriceGap	
	,MAX(CASE WHEN low = 1 THEN Price END) AS LowPrice
	,MAX(CASE WHEN low = 1 THEN Merchant END) AS LowPriceMerchant
	,MAX(CASE WHEN high = 1 THEN Price END) AS HighPrice
	,MAX(CASE WHEN high = 1 THEN Merchant END) AS HighPriceMerchant
FROM
(
	SELECT MobID, Merchant, Price, Bottomline_Price
		,ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS high
		,ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS low
		,CASE WHEN Merchant = @usname THEN 1 ELSE 0 END AS My
	FROM #Merchant
) D
GROUP BY MobID
HAVING MAX(My) = 1

Overall your posts are very difficult to read due to:
1. the data being poorly formatted and not easily copied into Management Studio.
2. extra information confusing the issue. What have #temp and tbl_master_Product got to do with the problem?

People are more likely to answer your posts if they can easily read and test them.

PS. I have just noticed that you have posted more sample data.
What is the point if you do not provide the corresponding results.
You need to engage brain before posting!

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-07 : 10:01:24

First thanks for your advice and thanks for your kindly help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources