Re: SQL Help

hello kasi,

a sub-query might be the answer here, try:

select

    *
from

    project_budget b1
where

    b1.budget_date = (select

                          max(budget_date)
                      from
                          project_budget b2
                      where
                         (to_char(b1.budget_date, 'mm') =
                         to_char(b2.budget_date, 'mm')));

the idea here is to use a correlated inner query that returns the max date for the month selected in the outer query.

hope that helps.

:) ATTA In article <396BDA70.EA5FB8C7_at_hotmail.com>,   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
>
>

--

getting the meanin' of data...


Sent via Deja.com http://www.deja.com/
Before you buy.