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





*** Sent via Developersdex http://www.developersdex.com ***

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

x taol wrote:
Show quoteHide quote

thank you very much, but fClr has null value.
in that case, the result is wrong.




*** Sent via Developersdex http://www.developersdex.com ***

x taol wrote:
Try this (obvious) workaround:

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 ISNULL(a.fClr,'')=ISNULL(x.fClr,'')
AND a.fDate=x.LastDate

Razvan

thank you very much, Razvan !  ^_^

*** Sent via Developersdex http://www.developersdex.com ***

Combining 2 select statements
column dataType
Applying a function to a subquery
Query from a dynamic table
Case statement question