Find AVG price with Conditions

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

SQL Server 2005 Forums

Transact-SQL (2005)

Find AVG price with Conditions

Author

Topic

hakjack
Starting Member

2 Posts

Posted - 2009-10-31 : 14:16:43

I need help with Syntax.I'm trying to create a function. I'm new to SQL. Can somebody help me write this out.

Need to make sure that the price of a product can't be less than 20% off the average price at which the product was sold in the same state within the past 30 days.

Here is what I think I need to do.
I need to subtract 30 days from the current date.
I need to select on order detail and get average price with productId,state,date.

I need to multiply average price * 20%.
I need to check if price that was passed in is less than this price .

I'm thinking of passing a Boolean.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-31 : 17:29:25

SELECT CASE
WHEN AVG(Col3) * 1.2 > MAX(Col3) THEN 1
WHEN AVG(Col3) * 0.8 < MIN(Col3) THEN 1
ELSE 0
END AS PriceFluctuation
FROM Table1
WHERE Col1 >= DATEDIFF(DAY, 30, GETDATE()) -- 30 days ago
AND Col1 < DATEDIFF(DAY, -1, GETDATE()) -- Tomorrow
AND Col2 = 12345 -- ProductID

N 56°04'39.26"
E 12°55'05.63"

hakjack
Starting Member

2 Posts

Posted - 2009-10-31 : 17:42:29

Here is what I just wrote. Not sure if correct. Maybe you can help with logic and syntax.

CREATE FUNCTION ProductPriceToLow (@ProductID int, @ProductPrice Smallmoney, @State char)

RETURNS Boolean
AS
BEGIN
-- Declare the variables here
DECLARE @Date30daysAgo date;
DECLARE @AveragePrice smallmoney;
DECLARE @PriceToLow Boolean;
DECLARE @FinalPrice smallmoney;

Set @PriceToLow = False

SELECT GETDATE() - 30 AS @Date30DaysAgo

select avg(pricePerUnit) into @AveragePrice
from OrderDetail D
inner join OrderHeader H
on D.OrderId = H.OrderID

where d.ProductId = @ProductID , H.ShipToState = @state, H.OrderDate > @date30daysAgo

set @FinalPrice = @AveragePrice * .2

If @ProductPrice < @FinalPrice
Set @PriceToLow = True

-- Return the result of the function
RETURN @PriceToLow

END

Pradip
Starting Member

32 Posts

Posted - 2009-11-02 : 06:52:57

Not needed any UDF you can directly insert or convert variable concept to UDF
Let's assume Product table (Productid,Price,State,Pdate) '
Need following variables @price, @state

SELECT @price=
CASE
WHEN AVG(Col3) * 1.2 > MAX(Col3) THEN @price
WHEN AVG(Col3) * 0.8 < MIN(Col3) THEN @price
ELSE 0
END AS price
FROM Product
WHERE pdate >= DATEADD(MONTH, -1, GETDATE()) -- 30 days ago
AND STATE=@state

Insert into product (productid,price,pDate,state)
values(@PID, @price,getdate(),@state

Is this help you?

pradipjain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources