Hi
I am stuck!

I have the following table:

CREATE TABLE HISTORY
(
    PRODUCT_CODE    int NOT NULL,
    PRODUCT_QTY    int NOT NULL,
    SALE_TIMESTAMP    datetime NOT NULL,
    CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go

I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:

CREATE PROCEDURE sp_get_sales @product_code int, @sale_timestamp
datetime
AS
BEGIN
    SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
        WHERE (PRODUCT_CODE = @product_code) AND (SALE_TIMESTAMP <=
@sale_timestamp)
        GROUP BY PRODUCT_CODE
END
go

However, this fails to create with:

Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.

How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?

thanks,
Neil

Assuming that if two sales occur at exactly the same, you want just  one of
them:

CREATE PROCEDURE sp_get_sales @product_code int, @sale_timestamp
datetime
AS
BEGIN
SELECT TOP 1
    PRODUCT_CODE, PRODUCT_QTY, SALE_TIMESTAMP
FROM
    HISTORY
WHERE
        (PRODUCT_CODE = @product_code)
AND (SALE_TIMESTAMP <= @sale_timestamp)
ORDER BY
    SALE_TIMESTAMP  DESC
END
go


I am stuck!

I have the following table:

CREATE TABLE HISTORY
(
PRODUCT_CODE int NOT NULL,
PRODUCT_QTY int NOT NULL,
SALE_TIMESTAMP datetime NOT NULL,
CONSTRAINT H1 PRIMARY KEY (PRODUCT_CODE, REC_TIMESTAMP)
)
go

I want to create the following procedure to list the most recent sale
of each product, before a particular timestamp:

CREATE PROCEDURE sp_get_sales @product_code int, @sale_timestamp
datetime
AS
BEGIN
SELECT PRODUCT_CODE, PRODUCT_QTY, MAX(SALE_TIMESTAMP) FROM HISTORY
WHERE (PRODUCT_CODE = @product_code) AND (SALE_TIMESTAMP <=
@sale_timestamp)
GROUP BY PRODUCT_CODE
END
go

However, this fails to create with:

Msg 8120 Column 'PRODUCT_QTY' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.

How can I write a proc to do this, but still include the PRODUCT_QTY
column in the output?

thanks,
Neil

Sp_send_mail - @file_attachment problem
Config SQL Server properties to use Fixed Memory
SQL Cluster - Automated Failover triggered by Latency or Abnormal CPU Usage
SP to get spec of remote server
Creating a test copy of a database?
SSRS (SQL server Reporting Services) - can it work with sql server 2000?
Migration from 2000 ro 2005 Cluster
Index defragmentation
trace dbcc statements
Replication Distribution Agent cpu continues to grow