Stored procedure help - SQL Server Forums

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

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-04 : 08:37:23

Hi all..
I have a stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[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 generates the following output
ID|PRODUCT_MPN|Description|Myprice|MyBottprice|BotPriceGap|LowPrice|LowMerchant|HighPrice|HighMerchant

The above output produced based on the column bottom_lineprice
I need lowprice,highprice calculate from price column based on the column value the gap must be found out..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 09:30:29

sorry cant make out what you're asking for from query. please post some sample data from tables and explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources