select to_char(a.budget_date,'YYYYMM'), a.budget_amt
from (select max(budget_date) budget_date
from project_budgets
group by trunc(budget_date,'MM')) b,
budget_project a
where a.budget_date = b.budget_date
/
--
Have a nice day
Michel
<kskasi_at_hotmail.com> a écrit dans le message : 396BDA8C.65B5C7A_at_hotmail.com...
> Hello everyone
>
> Iam trying to write an SQL to achieve the following
>
> I have a table with dates and Budget. All I want to do is to find the
> budget for every month. But the tricky bit is, 1 month might have
> multiple budgets against it on different dates. Under that
> circumstances, the SQL should return the budget for the max(date) of
> that month
> eg Budget_date Budget_amt
> 01/01/2000 $1000
> 02/02/2000 $2000
> 25/02/2000 $1500
> 03/03/2000 $3000
> 25/03/2000 $5000
>
> The output for the above data should look like the following
>
> Month Budget
> 200001 $1000
> 200002 $1500
> 200003 $5000
>
> I tried the following SQL
>
> select max(budget_date), Budget_amt
> from project_budgets
> where pro_project_id = 248
> group by to_char(budget_date, 'YYYYMM');
>
> It ofcourse dosen't work because Budget_amt is not a group by expression
>
> Thanks...Kasi
>
>
Received on Wed Jul 12 2000 - 00:00:00 CDT
