Re: master/detail

On 2012-05-21 1:17 AM, Jan Bakuwel wrote:
> Hi,
>
> I'm trying to get my head around the following question. As an example
> take a table with products:
>
> productid (pk)
> name
>
> and productprice
>
> productpriceid (pk)
> productid (fk)
> pricedate
> price
>
> There are multiple records in productprice for each product as prices
> (often) go up and (less often) go down.
>
> I'm looking for a query that returns the following:
>
> productid, name, pricedate, current_price, difference
>
> current_price is the latest (ie. most recent date) price of the product
> and difference is the difference in price between the latest price and
> the price before the latest.
>
> Any suggestions how to do this with SQL only? I can make it work with a
> function (probably less efficient) but think this should be possible
> with SQL too...
>
> cheers,
> Jan
>
>
With windowing functions:

select FRS.name
,FRS.productprice as current_price
,FRS.productprice- NXT.productprice
from
(
SELECT PRC.productid
,PRC.productprice
,ROW_NUMBER() OVER(PARTITION BY PRC.productid ORDER BY
PRC.pricedate desc) rank_nr
FROM productprice PRC
,products PRD
WHERE 1 = 1
AND PRD.productid = PRC.productid
) FRS
LEFT JOIN
(
SELECT productid
,productprice
,ROW_NUMBER() OVER(PARTITION BY productid ORDER BY pricedate
desc) rank_nr
FROM productprice
) NXT
ON FRS.productid = NXT.productid
AND NXT.rank_nr < 3
AND FRS.rank_nr <> NXT.rank_nr
WHERE 1 = 1
AND FRS.rank_nr = 1

Without windowing functions (slightly complicated, it's merely a max-mix
issue and assuming that the date includes the time)

SELECT CUR.name
,CUR.productid
,CUR.current_price
,CUR.current_price - PRV.previous_price price_difference
FROM ( --LATEST PRICE
SELECT PRD.name
,PRC.productid
,PRC.productprice current_price
FROM productprice PRC
,products PRD
,(--LATEST PRICEDATE
SELECT productid
,MAX(pricedate) pricedate
FROM productprice
GROUP BY productid
) MMX
WHERE 1 = 1
AND PRC.productid = PRD.productid
AND PRC.productid = MMX.productid
AND PRC.pricedate = MMX.pricedate
)CUR
-- IN CASE THERE'S NO PREVIOUS PRICE
LEFT JOIN
( -- PREVIOUS PRICE
SELECT PRC.productid
,PRC.productprice previous_price
FROM productprice PRC
,(--PREVIOUS PRICEDATE
SELECT PRC.productid
,MAX(PRC.pricedate) pricedate
FROM productprice PRC
,(
SELECT productid
,MAX(pricedate) pricedate
FROM productprice
GROUP BY productid
) MMX
WHERE 1 = 1
AND PRC.productid = MMX.productid
AND PRC.pricedate < MMX.pricedate
) PRV
WHERE 1 = 1
AND PRC.productid = PRV.productid
AND PRC.pricedate = PRV.pricedate
) LST
ON CUR.productid = LST.productid

cheers

Mario