query help

I have a table with multiple records for an identical person. It is a rolling history of the applications a user has submitted. How can I grab the most current application by the date most closest to todays date. Obviously one of my fields is an applied date.
I need help in creating a query.select * from table t where datefield = (select max(datefield) from table where userid = t.userid)

Nick

query help

i have a table that stores employee records. I then have a history employee table that stores changes to each particular employee (one to many ). Everytime a changes is made to an employee I record their old record in a history table. I want to be able to query against the history table and pull up the "last entry". How would I write a query for this, I am recording a timestamp.You haven't shared any DDL, so I'm guessing on keys and column names. But you should get the idea from this...
SELECT *
FROM EmployeeHistory eh
WHERE eh.theDate =
(SELECT MAX(eh2.theDate)
FROM EmployeeHistory eh2
WHERE eh2.EmployeeId = eh.EmployeeId)

|||

You might want to match on a identity field instead. It will be more reliable then a date field. The date field could always have multiple records with the same date.
Nick

|||Depends on the business rules. If there are multiple rows thatwere updated at exactly the same time, which one is most recent? Or should both be displayed?

Query Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

Query execution history?

Any chance to get this feature in SSMS?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124530

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125147

Also nice blog post about it: http://sqlblogcasts.com/blogs/jonsayce/archive/2007/09/01/recent-queries.aspx

hi!

try this add-in for SSMS that provides the functionality

http://weblogs.sqlteam.com/mladenp/archive/2007/09/20/SSMS-Tools-Packan-add-in-for-SQL-Management-Studio.aspx

Query execution history?

Any chance to get this feature in SSMS?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124530

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125147

Also nice blog post about it: http://sqlblogcasts.com/blogs/jonsayce/archive/2007/09/01/recent-queries.aspx

hi!

try this add-in for SSMS that provides the functionality

http://weblogs.sqlteam.com/mladenp/archive/2007/09/20/SSMS-Tools-Packan-add-in-for-SQL-Management-Studio.aspx

|||

As for the drop-down with a list of columns in a table, that is being added as part of T-SQL Intellisense feature. First part of this functionality will be available in the upcoming CTP.

Regards,

Maciek Sarnowicz

Query and GROUP BY

Hi
I am stuck!
I have the following table:
CREATE TABLE HISTORY
(
PRODUCT_CODEint NOT NULL,
PRODUCT_QTYint NOT NULL,
SALE_TIMESTAMPdatetime 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
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegr oups.com...
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

Query and GROUP BY

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,
NeilAssuming 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
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
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

Query and GROUP BY

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,
NeilAssuming 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
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172787732.437425.198610@.n33g2000cwc.googlegroups.com...
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