Help extending 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 2005 Forums

Transact-SQL (2005)

Help extending query

Author

Topic

dgburton1
Starting Member

20 Posts

Posted - 2008-06-16 : 04:51:18

Hi,

If anyone can help me extending the following query I'd be really grateful! :)

I have 4 tables, Product, RawProduct, RawProductPriceHistory and RawProductPromotionalHistory. The relationship is a Product can have multiple RawProducts (one for every retailer the product is stocked in), and the PriceHistory and PromotionalHistory tables keep track of when the RawProduct's price changes, and when it comes on or off of promotion.

I have the following SP to return prices for the given Product for the given Date.

I need to extend the SP so that it also returns details from the RawProductPromotionalHistory table if a PromotionalHistory entry occurs for the passed date (@Date). We determine whether a PromotionalHistory exists by whether the passed @Date >= RawProductPromotionalHistory.StartDateTime and @Date <= RawProductPromotionalHistory.EndDateTime. The EndDateTime can also be NULL - indicating an ongoing promotion.

Here is the schema of the RawProductPromotionalHistory table:

[url]http://www.boltfile.com/directdownload/rawproductpromotionalhistory.jpg[/url]

And here is the current SP:

ALTER PROCEDURE [dbo].[GetProductPricesForDate]
-- Add the parameters for the stored procedure here
@Date datetime,
@ProductId uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
ph.[DateTime], p.Name AS 'ProductName', ph.UnitPrice, rp.RawProductId, r.LogoFileName AS 'ShopLogoFileName', r.ShopId, r.Name as 'ShopName'
FROM
Shops r
INNER JOIN
RawProducts rp
ON
rp.[ShopId]=r.ShopId
INNER JOIN
Products p
ON
p.[ProductId] = rp.ProductId
INNER JOIN
RawProductPriceHistory ph
ON
ph.[RawProductId] = rp.RawProductId
INNER JOIN
(SELECT RawProductId,MAX([DateTime]) as maxdate
FROM
RawProductPriceHistory
WHERE
[DateTime]<=@Date
GROUP BY
RawProductId
)temp
ON
ph.RawProductId=temp.RawProductId
AND
ph.[DateTime]=temp.maxdate
WHERE
p.ProductId = @ProductId
GROUP BY
ph.[DateTime], p.Name, r.Name, ph.UnitPrice, r.ShopId, rp.RawProductId, r.LogoFileName
ORDER BY
ph.UnitPrice DESC

END

If anyone can help me extend the query I'd be really grateful!

thanks in advance,

dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:09:25

[code]ALTER PROCEDURE [dbo].[GetProductPricesForDate]
-- Add the parameters for the stored procedure here
@Date datetime,
@ProductId uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
ph.[DateTime], p.Name AS 'ProductName', ph.UnitPrice, rp.RawProductId, r.LogoFileName AS 'ShopLogoFileName', r.ShopId, r.Name as 'ShopName',rpph.PromotionalText
FROM
Shops r
INNER JOIN
RawProducts rp
ON
rp.[ShopId]=r.ShopId
INNER JOIN
Products p
ON
p.[ProductId] = rp.ProductId
INNER JOIN
RawProductPriceHistory ph
ON
ph.[RawProductId] = rp.RawProductId
INNER JOIN
(SELECT RawProductId,MAX([DateTime]) as maxdate
FROM
RawProductPriceHistory
WHERE
[DateTime]<=@Date
GROUP BY
RawProductId
)temp
ON
ph.RawProductId=temp.RawProductId
AND
ph.[DateTime]=temp.maxdate
LEFT JOIN (SELECT *
FROM RawProductPromotionalHistory
WHERE StartDateTime<=@Date
AND (EndDateTime>=@Date OR EndDateTime IS NULL))rpph
ON rpph.RawProductId=rp.RawProductIdWHERE
p.ProductId = @ProductId
GROUP BY
ph.[DateTime], p.Name, r.Name, ph.UnitPrice, r.ShopId, rp.RawProductId, r.LogoFileName
ORDER BY
ph.UnitPrice DESC

END[/code]

dgburton1
Starting Member

20 Posts

Posted - 2008-06-16 : 07:55:28

That's excellent - thank you very much! :):)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources