Re: SQL Help

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/07/12
Message-ID: <msuoms8150q28a66kefqkv7hbn9ptqssot@4ax.com>#1/1

Try this:

select budget_date, budget_amount
from project_budgets pb1
where budget_date in (

   select max(budget_date)
   from project_budgets pb2
   group by trunc(budget_date,'mm')
   );

I don't have your data, so I can't test this to be sure I got it exactly right. First, try the subquery independently to be sure that it returns the greatest budget date for each month. I think it should. Once you have the subquery working, add in the main query.

regards,

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Wed, 12 Jul 2000 12:09:45 +0930, kskasi_at_hotmail.com wrote:

>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