Help with query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query

Author

Topic

hprider
Starting Member

2 Posts

Posted - 2006-10-06 : 10:59:15

Hello,

I have an existing database with Product, Subcategory, and ProductSubcategory tables.
The Product table includes the following columns:
ProductId
ProductName
ProductPrice
SalePrice
BeginSaleDate
EndSaleDate

Subcategory has SubcategoryId and SubcategoryName

ProductSubcategory is a linking table with ProductId and SubcategoryId.

Every product can belong to more than one subcategory.

I am trying to pull all products for a particular subcategory sorted by price. The problem is that some products will be on sale so I cannot go purely by ProductPrice but will have to take into account the sale price of any products that have a SalePrice and the date falls between the BeginSaleDate and the EndSaleDate

Any ideas?

Thanks!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-06 : 11:20:55

[code]SELECT p.ProductName
, CASE WHEN getdate() BETWEEN p.BeginSaleDate AND p.EndSaleDate
THEN p.SalePrice
ELSE p.ProductPrice
END AS Price
FROM Product p
INNER JOIN ProductSubCategory ps ON ps.ProductID = ps.ProductID
INNER JOIN SubCategory s ON s.SubCategoryID = ps.SubCategoryID
WHERE s.SubCategoryName = 'name'[/code]

hprider
Starting Member

2 Posts

Posted - 2006-10-08 : 14:13:44

Thanks for your help. It works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources