Select N rows and min

Dear All
I need your help to sove this problem.

I have a table Customers_Bal containing the following Information:

Date Balance
01/01/2007 1000
01/02/2007 700
01/03/2007 800
01/04//2007 600
01/05/2007 1200
01/06/2007 1800
01/07/2007 2600
01/08/2007 900
01/09/2007 600

I need to select the first N rows and get the min(balance) and put it a new
field (Bal_Min) in the Nth record.
after select nrows from the second row - Get the min(balnce) put it a new
field (Bal_Min) in the Nth+1 record and so on till the EOF.

Let's suppose that N=3 the result will be:

Date Balance Bal_min
01/01/2007 1000
01/02/2007 700
01/03/2007 800 700 (Min (1000,700,800))
01/04//2007 600 600 (Min(700,800,600))
01/05/2007 1200 600 (Min(800,600,1200))
01/06/2007 1800 600 (Min(600,1200,1800))
01/07/2007 2600 1200 (Min(1200,1800,2600))
01/08/2007 900 900 (Min(1800,2600,900))
01/09/2007 600 600 (Min(1200,900,600))

N Is a Variable.

Thanks for your help.

You need sliding aggregations query. Something like


SELECT O1.date,
MIN(O2.Balance) AS sliding_minimum
FROM Customers_Bal O1 JOIN Customers_Bal AS O2
ON O2.date > DATEADD(day, -3, O1.date)
AND O2.date<= O1.date
GROUP BY O1.date
ORDER BY O1.date

should be close to what you need. As you did not priovide DDL and DML
statements for the table, I did not check the query.

--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

"small brother" wrote in message

> Dear All
> I need your help to sove this problem.
>
> I have a table Customers_Bal containing the following Information:
>
> Date Balance
> 01/01/2007 1000
> 01/02/2007 700
> 01/03/2007 800
> 01/04//2007 600
> 01/05/2007 1200
> 01/06/2007 1800
> 01/07/2007 2600
> 01/08/2007 900
> 01/09/2007 600
>
> I need to select the first N rows and get the min(balance) and put it a
> new
> field (Bal_Min) in the Nth record.
> after select nrows from the second row - Get the min(balnce) put it a new
> field (Bal_Min) in the Nth+1 record and so on till the EOF.
>
> Let's suppose that N=3 the result will be:
>
> Date Balance Bal_min
> 01/01/2007 1000
> 01/02/2007 700
> 01/03/2007 800 700 (Min (1000,700,800))
> 01/04//2007 600 600 (Min(700,800,600))
> 01/05/2007 1200 600 (Min(800,600,1200))
> 01/06/2007 1800 600 (Min(600,1200,1800))
> 01/07/2007 2600 1200 (Min(1200,1800,2600))
> 01/08/2007 900 900 (Min(1800,2600,900))
> 01/09/2007 600 600 (Min(1200,900,600))
>
> N Is a Variable.
>
> Thanks for your help.