Re: SQL Help

Hi,

This should work:

SELECT TO_CHAR(budget_date, 'YYYY/MM') AS "Month",

          budget_amt AS "Budget"
FROM      project_budgets
WHERE     pro_project_id = 248
AND       budget_date IN
          (SELECT   MAX(budget_date)
          FROM      project_budgets
          WHERE     pro_project_id = 248
          GROUP BY TRUNC(budget_date,'DD'))

Good luck.

Zbigniew Sliwa
Oracle Programmer

Larry napisa³(a) w wiadomo¶ci: <3977ff12_at_news.hk.gin.net>...
>Hi,
>
>If my understanding to your question is correct, then you could just change
>the group by column to budget_amt.
>i haven't tried the sql because i don't have access at the moment.
>
> select to_char(max(budget_date),'YYYY/MM'), Budget_amt
> from project_budgets
> where pro_project_id = 248
> group by budget_amt;
>
>larry
>
>
>
><kskasi_at_hotmail.com> wrote in message news:396BDA52.F1C3D3AB_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 Fri Jul 21 2000 - 00:00:00 CEST