Select N rows and get Minimum

[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.

 

[Marshall Barton]

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.

I don't see how to not put something in the first two
records, but I think this will get the rest of them:

SELECT T.date, T.balance,
Min((SELECT TOP 3 X.balance
FROM Customers_Bal As X
WHERE X.date <= T.date
ORDER BY X.date DESC)) As RunMin
FROM Customers_Bal As T

If you really have to have the 3 as a variable, then use
code to construct the SQL statement before using the query.

 

[giorgio rancati]

Hi,
this solution needs 3 samall queries

#1 Query
----
SELECT B1.Date, B1.Balance,
B2.Date AS DateCross, B2.Balance AS BalanceCross
FROM Customers_Bal AS B1, Customers_Bal AS B2
WHERE B1.Date>=B2.Date;
----
save it as qryBalance1

#2 Query
----
SELECT B1.Date, B1.Balance, B1.DateCross,
B1.BalanceCross, Count(*) AS Rank
FROM qryBalance1 AS B1, qryBalance1 AS B2
WHERE B1.Date=B2.Date AND B1.DateCross<=B2.DateCross
GROUP BY B1.Date, B1.Balance, B1.DateCross, B1.BalanceCross;
----
save it as qryBalance2

#3 The final query
----
PARAMETERS N Long;
SELECT Date, Balance,
IIf(Max(Rank)=N,Min(BalanceCross),Null) AS Bal_Min
FROM qryBalance2
WHERE Rank<=N
GROUP BY Date, Balance
ORDER BY Date;

 

[Gary Walter]

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.

I know this is probably wrong to bring up, but *IF*
your dates are all perfectly sequential
with none missing...the problem being it is
not a "perfect world."

(I changed "Date" to "BalDate" in my test table)

SELECT
CB.BalDate,
CB.Balance,
(SELECT
MIN(t.Balance)
FROM Customers_Bal AS t
WHERE
t.BalDate <= CB.BalDate AND t.BalDate > CB.BalDate - [N]
) AS Bal_min
FROM Customers_Bal AS CB;

it will give you values for the earliest dates though...

good luck,

gary

 

[small brother]

Thanks a lot Gary for the effort made But the result gived by Giorgio is
exactly what i'm searching for (the first n records have to be null for the
minimum).
Giorgio thanks a lot for your help.

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.

I know this is probably wrong to bring up, but *IF*
your dates are all perfectly sequential
with none missing...the problem being it is
not a "perfect world."

(I changed "Date" to "BalDate" in my test table)

SELECT
CB.BalDate,
CB.Balance,
(SELECT
MIN(t.Balance)
FROM Customers_Bal AS t
WHERE
t.BalDate <= CB.BalDate AND t.BalDate > CB.BalDate - [N]
) AS Bal_min
FROM Customers_Bal AS CB;

it will give you values for the earliest dates though...

good luck,

gary