Re: SQL Help
Date: 2000/07/12
Message-ID: <396C0887.C32E2252_at_home.com>#1/1
hi,
a solution without performance consideration:
SQLWKS> create table budget
2> (budget_date date,
3> amt number)
4> /
Statement processed.
SQLWKS> insert into budget values(to_date('01/01/2000', 'DD/MM/YYYY'), 1000);
1 row processed.
SQLWKS> insert into budget values(to_date('02/02/2000', 'DD/MM/YYYY'), 2000);
1 row processed.
SQLWKS> insert into budget values(to_date('25/02/2000', 'DD/MM/YYYY'), 1500);
1 row processed.
SQLWKS> insert into budget values(to_date('03/03/2000', 'DD/MM/YYYY'), 3000);
1 row processed.
SQLWKS> insert into budget values(to_date('25/03/2000', 'DD/MM/YYYY'), 5000);
1 row processed.
SQLWKS> commit;
Statement processed.
SQLWKS> select budget_date, to_char(budget_date, 'YYYYMM'), amt from budget where budget_date in
2> (select max(budget_date) from budget group by to_char(budget_date, 'YYYYMM'))
3> /
BUDGET_DATE
TO_CHAR(BUDGET_DATE,'YYYYMM')
AMT
--------------------
---------------------------------------------------------------------------
01/01/2000 00:00:00
200001 1000
02/25/2000 00:00:00
200002 1500
03/25/2000 00:00:00
200003 5000
3 rows selected.
clean code to test:
drop table budget
/
create table budget
(budget_date date,
amt number)
/
insert into budget values(to_date('01/01/2000', 'DD/MM/YYYY'), 1000);
insert into budget values(to_date('02/02/2000', 'DD/MM/YYYY'), 2000);
insert into budget values(to_date('25/02/2000', 'DD/MM/YYYY'), 1500);
insert into budget values(to_date('03/03/2000', 'DD/MM/YYYY'), 3000);
insert into budget values(to_date('25/03/2000', 'DD/MM/YYYY'), 5000);
commit;
select budget_date, to_char(budget_date, 'YYYYMM'), amt from budget where budget_date in
(select max(budget_date) from budget group by to_char(budget_date, 'YYYYMM'))
/
Hakan
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 CEST