New Query

[Harish]

hi
i have three columns

account_code current_balance balance_date
1000000 -5.00 31-dec-2000
1000001 10.00 25-dec-2000
1000001 100.52 01-dec-2000
1000002 199.00 01-aug-2000
1000003 200.00 01-dec-2000
1000003 250.00 11-sep-2000
1000003 -25.00 15-dec-2000
1000003 500.00 31-dec-2000

Q1. I want the maximum of current balance for the month of
the December 2000 of each account.

Q2. Find out the current balance on th latest date of the
December 2000 of each account.

Thanks,
Harish

[Anastasia]

Q1.
select max(current_balance) [max balance], account_code
from Table1
where datepart(month,convert(datetime, balance_Date)) = 12
group by account_code
Q2.
select max(convert(datetime, balance_date)) [latest date], account_code
from Table1
where datepart(month,convert(datetime, balance_Date)) = 12
group by account_code

you can remove the "Convert" function if the current_balance column's datatype is "datetime"

Hope that works for you,
Anastasia.


------------
Harish at 9/12/01 11:49:23 AM

hi
i have three columns

account_code current_balance balance_date
1000000 -5.00 31-dec-2000
1000001 10.00 25-dec-2000
1000001 100.52 01-dec-2000
1000002 199.00 01-aug-2000
1000003 200.00 01-dec-2000
1000003 250.00 11-sep-2000
1000003 -25.00 15-dec-2000
1000003 500.00 31-dec-2000

Q1. I want the maximum of current balance for the month of
the December 2000 of each account.

Q2. Find out the current balance on th latest date of the
December 2000 of each account.

Thanks,
Harish

[Juergen Leis]

SELECT account_code, MAX( current_balance )
FROM ...
WHERE balance_date BETWEEN '20001201' AND '20001231 23:59:59.999'
/* balance_date >= &#39;20001201&#39; AND balance_date < &#39;20010101&#39;
GROUP BY account_code

SELECT a1.account_code, a1.current_balance
FROM accountentries a1
WHERE a1.balance_date =
( SELECT MAX( a2.balance_date )
FROM accountentries a2
WHERE a2.account_code = a1.account_code
AND a2.balance_date BETWEEN &#39;20001201&#39; AND &#39;20001231 23:59:59.999&#39;
)
/* rest speeds up the query if non december 2000 entries are in the table:
*/
AND a1.balance_date BETWEEN &#39;20001201&#39; AND &#39;20001231 23:59:59.999&#39;



------------
Harish at 9/12/01 11:49:23 AM

hi
i have three columns

account_code current_balance balance_date
1000000 -5.00 31-dec-2000
1000001 10.00 25-dec-2000
1000001 100.52 01-dec-2000
1000002 199.00 01-aug-2000
1000003 200.00 01-dec-2000
1000003 250.00 11-sep-2000
1000003 -25.00 15-dec-2000
1000003 500.00 31-dec-2000

Q1. I want the maximum of current balance for the month of
the December 2000 of each account.

Q2. Find out the current balance on th latest date of the
December 2000 of each account.

Thanks,
Harish