Select N rows and min

[small brother]

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.

[Dejan Sarka]

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

Post by small brother
Dear All
I need your help to sove this problem.
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.
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.