Subject | RE: [firebird-support] How to find record ID table which contains item price with datetime and items ID |
---|---|
Author | Svein Erling Tysvær |
Post date | 2012-03-01T13:40:04Z |
>What I need is to find last items price from selected period.Select ti.item_ID, ti.Item_Name, tp.Price as LastPreis
>
>Here's how i do it now:
>
>select
>tblItems.item_ID,
>tblItems.Item_Name,
>(Select tblPrices.Price from tblPrices where tblPrices.DateTime =
> (select max(tblPrices.DateTime) from tblPrices
> where tblPrices.Item_id = tblItems.item_ID and tblPrices.DateTime
> between '2011-01-01' and '2011-06-30')
> and tblPrices.Item_id = tblItems.item_ID) as LastPreis
>from tblItems
>order by tblItems.Item_Name
>
>and of course it works.
>But is there any easiest way??
from tblItems ti
left join tblPrices tp
on ti.Item_id = tp.item_ID
and tp.DateTime between '2011-01-01' and '2011-06-30'
left join tblPrices tp2
on tp.Item_id = tp2.item_ID
and tp.DateTime < tp2.DateTime
and tp2.DateTime between '2011-01-01' and '2011-06-30'
where tp2.Item_id is null
order by ti.Item_Name
Another alternative is to use NOT EXISTS for tp2 (I'm not showing an example of that one) and yet another is to use a CTE:
WITH LastPriceDate (Item_ID, LastDate) as
(select Item_ID, max(DateTime) from tblPrices
where DateTime between '2011-01-01' and '2011-06-30'
group by Item_ID)
SELECT ti.Item_ID, ti.Item_Name, tp.Price as LastPreis
from tblItems ti
left join LastPriceDate lpd on ti.Item_ID = lpd.Item_ID
left join tblPrices tp on ti.Item_ID = tp.Item_ID and lpd.LastDate = tp.DateTime
order by ti.Item_Name
All three alternatives ought to work pretty well, choose the one you find most intuitive.
HTH,
Set