SQL SERVER: View to get minimum and maximum values from a table.

Question

Hi,

I have a MSSQL Server table like this:

id (auto-increment)
amount
date
account_id

Data are inserted throughout the day. I now need a view to get the opening and closing amounts for each account for each day. My trouble is creating a fast query to access both minimum and maximum values. Creating a view to access just the minimum is fine using an in statement, however getting both minimum and maximum is tricky. I've tried using a with clause, but the query is incredibly slow.

BTW I am mapping the view to hibernate, so stored procedures and functions won't work the same way (that I know of).

Update I guess my question wasn't clear from the responses I received. I want to get the opening and closing balances for each account. Maximum and minimum referred to getting the max and min (id) when grouped by date and account_id. I want to get the amount when the id is equal to the maximum id (closing balance) and the amount when the id is equal to the minimum id (opening balance) for each account for each day.

Answer 1

SELECT account_id, date, MIN(amount), MAX(amount)
FROM <table>
GROUP BY account_id, date

There must be something missing from your question.

Answer 2

Based on John Saunders answer and Jeremy comment:

SELECT account_id, date, MIN(amount), MAX(amount)
FROM <table>
GROUP BY account_id, DatePart( Year, date ),DatePart( Month, date ), DatePart( Day, date )

Answer 3

In Oracle you can do something like that :

with t as
(
     select 23 as account,1 as amount,to_date('01/01/2009 09:00:00', 'DD/MM/YYYY HH24:MI:SS') as datetime from dual
     union select 23,2,to_date('01/01/2009 10:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,3,to_date('01/01/2009 11:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,4,to_date('01/01/2009 12:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,5,to_date('01/01/2009 13:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,1,to_date('01/02/2009 10:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,2,to_date('01/02/2009 11:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,2,to_date('01/02/2009 12:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
     union select 23,1,to_date('01/02/2009 13:00:00', 'DD/MM/YYYY HH24:MI:SS')from dual
)
select   account, trunc(datetime),
         min(amount) keep (dense_rank first order by datetime asc) as opening_amount,
         max(amount) keep (dense_rank first order by datetime desc) as closing_amount
from     t
group by account, trunc(datetime)

Returns

ID  Date  opening_Amount closing_amount
23  01/01/2009  1             5
23  01/02/2009  1          1

But the keep analytic function doesn't seem to be in SQLServer... So my post is useless :o)

Edit 1: Ok, first try to simulate the keep function in SQL Server 2005, don't be too hard..

with t as
(
select 23 as account_id,1 as amount,cast('01/01/2009 10:00:00' as datetime) as date
union all select 23,2,cast('01/01/2009 11:00:00' as datetime)
union all select 23,3,cast('01/01/2009 12:00:00' as datetime)
union all select 23,1,cast('01/02/2009 10:00:00' as datetime)
union all select 23,2,cast('01/02/2009 11:00:00' as datetime)
union all select 23,3,cast('01/02/2009 12:00:00' as datetime)
union all select 23,2,cast('01/02/2009 13:00:00' as datetime)
union all select 23,1,cast('01/02/2009 14:00:00' as datetime)
)
select *
from
(
    select  account_id, 
      date, 
      amount,
      min(date) over (partition by account_id, convert(varchar(8), date, 112) ) as open_date,
      max(date) over (partition by account_id, convert(varchar(8), date, 112) ) as close_date
    from     t
) a
where open_date = date or close_date = date

A little ugly, but give the good result I think.

Answer 4

Essentially I need the following query, but the with statement causes it to run slowly:

with x as (
 select 
  MAX(ab.id) as maxId, MIN(ab.id) as minId
 from Balance ab
 group by ab.account_id, dbo.Get_PeriodDateFromDatetime(ab.StatementDate)
)
select 
  ab.Amount as openingBalance, ab2.Amount as closingBalance
from Balance ab, Balance ab2, x
where ab.id = x.maxId and ab2.id = x.minId

Answer 5

I don't know if this improves any, but the query you posted looks to be missing some parts, like account_id in the "with" query and joins on account_id in the main part:

with x as (
 select 
  ab.account_id, MAX(ab.id) as closeId, MIN(ab.id) as openId
 from Balance ab
 group by ab.account_id, dbo.Get_PeriodDateFromDatetime(ab.StatementDate)
)
select 
  opbal.account_id, opbal.StatementDate,
  opbal.Amount as openingBalance, clsbal.Amount as closingBalance
from Balance opbal, Balance clbal, x
where clsbal.id = x.closeId 
and clsbal.ccount_id = x.account_id
and opbal.id = x.openId
and op.account_id = x.account_id

I'm a little concerned about the call to dbo.Get_PeriodDateFromDatetime(ab.StatementDate): if you have an index on account_id and StatementDate (you do have that index, don't you? It looks like a good candidate for a clustered index, too) then it's maybe not too bad, unless the table is massive.

How slow is "slow", by the way?

Answer 6

This does the work, don't have enough data to evaluate performance:

create table #accounts
   (
   id           integer identity,
   account_id   integer,
   amount       decimal(18,3),
   tran_date    datetime
   )
go

insert into #accounts values (1,124.56,'06/01/2009 09:34:56');
insert into #accounts values (1,125.56,'06/01/2009 10:34:56');
insert into #accounts values (1,126.56,'06/01/2009 11:34:56');

insert into #accounts values (2,124.56,'06/01/2009 09:34:56');
insert into #accounts values (2,125.56,'06/01/2009 10:34:56');
insert into #accounts values (2,126.56,'06/01/2009 11:34:56');

insert into #accounts values (3,124.56,'06/01/2009 09:34:56');
insert into #accounts values (3,125.56,'06/01/2009 10:34:56');
insert into #accounts values (3,126.56,'06/01/2009 11:34:56');

insert into #accounts values (4,124.56,'06/01/2009 09:34:56');
insert into #accounts values (4,125.56,'06/01/2009 10:34:56');
insert into #accounts values (4,126.56,'06/01/2009 11:34:56');

insert into #accounts values (1,124.56,'06/02/2009 09:34:56');
insert into #accounts values (1,125.56,'06/02/2009 10:34:56');
insert into #accounts values (1,126.56,'06/02/2009 11:34:56');

insert into #accounts values (2,124.56,'06/02/2009 09:34:56');
insert into #accounts values (2,125.56,'06/02/2009 10:34:56');
insert into #accounts values (2,126.56,'06/02/2009 11:34:56');

insert into #accounts values (3,124.56,'06/02/2009 09:34:56');
insert into #accounts values (3,125.56,'06/02/2009 10:34:56');
insert into #accounts values (3,126.56,'06/02/2009 11:34:56');

insert into #accounts values (4,124.56,'06/02/2009 09:34:56');
insert into #accounts values (4,125.56,'06/02/2009 10:34:56');
insert into #accounts values (4,126.56,'06/02/2009 11:34:56');
go

select
   ranges.tran_day      transaction_day,
   ranges.account_id    account_id,
   bod.amount           bod_bal,
   eod.amount           eod_bal
from
   -- Subquery to define min/max records per account per day
   (
   select
      account_id,
      cast(convert(varchar(10),tran_date,101) as datetime) tran_day,
      max(id) max_id,
      min(id) min_id
   from
      #accounts
   group by
      account_id,
      cast(convert(varchar(10),tran_date,101) as datetime)
   ) ranges

   -- Beginning of day balance
   JOIN #accounts bod
      on (bod.id = ranges.min_id)

   -- End of day balance
   JOIN #accounts eod
      on (eod.id = ranges.max_id)
go

If you need better performance, store the subquery to a temp table first and put an index on it for the joins ... that might speed it up a bit.

Ron