Subject RE: [firebird-support] How to find record ID table which contains item price with datetime and items ID
Author Svein Erling Tysvær
>What I need is to find last items price from selected period.
>
>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??

Select ti.item_ID, ti.Item_Name, tp.Price as LastPreis
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