sql server programming:
fDate fProduct fClr fIn fOut fStock
03/06/2006 aa xx1 3 2 1
03/08/2006 aa xx1 7 4 4
03/18/2006 aa xx1 6 4 6
03/09/2006 bb xx2 3 2 1
03/11/2006 bb xx2 9 2 8

due : until 03/09/2006
all grouping fProduct,fClr

the answer table is below.
how to make query?


fProduct fClr fStock
aa xx1 4
bb xx2 1

Please post DDL (as "CREATE TABLE" statements, including PK-s, FK-s and
other constraints) and sample data (as "INSERT INTO ... VALUES (...)"
statements). See: http://www.aspfaq.com/etiquette.asp?id=5006

I am assuming that the primary key in your table is fDate+fProduct:

CREATE TABLE YourTable (
fDate smalldatetime NOT NULL,
fProduct varchar(50) NOT NULL,
fClr varchar(20) NOT NULL,
fIn int NOT NULL,
fOut int NOT NULL,
fStock int NOT NULL,
PRIMARY KEY (fDate,fProduct)
)

INSERT INTO YourTable VALUES ('20060306','aa','xx1',3,2,1)
INSERT INTO YourTable VALUES ('20060308','aa','xx1',7,4,4)
INSERT INTO YourTable VALUES ('20060318','aa','xx1',6,4,6)
INSERT INTO YourTable VALUES ('20060309','bb','xx2',3,2,1)
INSERT INTO YourTable VALUES ('20060311','bb','xx2',9,2,8)

The following query provides the expected result:

DECLARE @DueUntil smalldatetime
SET @DueUntil='20060309'

SELECT a.fProduct, a.fClr, a.fStock
FROM YourTable a
INNER JOIN (
SELECT b.fProduct, b.fClr, MAX(b.fDate) as LastDate
FROM YourTable b WHERE b.fDate<=@DueUntil
GROUP BY b.fProduct, b.fClr
) x ON a.fProduct=x.fProduct AND a.fClr=x.fClr
AND a.fDate=x.LastDate

Razvan

[quoted text, click to view]