New entries in db query

Our SQL guy is off on vacation and I have been asked to write a query that
identifies new entries to our db. We have a table in a SQL 2000 db that has
a
list of computer IDs, comments and date of entry. There can, and usually is,
multiple entries of comments for each computer.
What I have been asked to extract is if a new Computer ID is entered.
Ex. My machine name is: workstation123. It already has several entries in
the database which are already handled. If workstation124 gets an entry and
it is not already in the db - I need to be able to query that.
The table is simple and has only 4 columns:
CompName - nvarchar
TheDate - datetime
TheComment - ntext
TheID - int {Identity}
Any idea how I can extract all of the ComputerIDs {CompName} on the most
recent date {TheDate} that do not appear anywhere else in the table {all
other dates}?
ThanksHello
You can use something like this:
DECLARE @.LastDate datetime
SELECT @.LastDate=MAX(TheDate) FROM YourTable
SELECT DISTINCT CompName FROM YourTable
WHERE TheDate=@.LastDate AND CompName NOT IN (
SELECT CompName FROM YourTable WHERE TheDate<@.LastDate
)
I assume that you store only the date in TheDate (not the date and the
time). If you also store the time, the query needs to be modified to
handle it correctly.
Razvan|||Define "new". Does it mean that a new computer has one - and only one -
comment? Does it mean that only one comment occurred on a specific date?
Could you give us some sample data and pick the "new" computer?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"vm" <vm@.discussions.microsoft.com> wrote in message
news:2521A071-50C1-49FE-B35C-46F5A3A0732B@.microsoft.com...
Our SQL guy is off on vacation and I have been asked to write a query that
identifies new entries to our db. We have a table in a SQL 2000 db that has
a
list of computer IDs, comments and date of entry. There can, and usually is,
multiple entries of comments for each computer.
What I have been asked to extract is if a new Computer ID is entered.
Ex. My machine name is: workstation123. It already has several entries in
the database which are already handled. If workstation124 gets an entry and
it is not already in the db - I need to be able to query that.
The table is simple and has only 4 columns:
CompName - nvarchar
TheDate - datetime
TheComment - ntext
TheID - int {Identity}
Any idea how I can extract all of the ComputerIDs {CompName} on the most
recent date {TheDate} that do not appear anywhere else in the table {all
other dates}?
Thanks|||> Any idea how I can extract all of the ComputerIDs {CompName} on the most
> recent date {TheDate} that do not appear anywhere else in the table {all
> other dates}?
declare @.target datetime
set @.target = '20060220'
select CompName, min(TheDate) as CreatedDate
from '
group by CompName
having min(TheDate) >= @.target
This assumes that there are no rows where TheDate is in the future (in the
example, great than Feb 21 2006 00:00:00.000). This also assumes that the
TheDate column is either datetime or smalldatetime.|||Thanks Raz, your code was spot on. I am not the SQL person, but I created a
test table with some sample data like the live data. I changed the data
around for different situations we would encounter... Your code did
everything they were looking for...
Thanks again and thanks to all who gave suggestions!
vm
"vm" wrote:

> Our SQL guy is off on vacation and I have been asked to write a query that
> identifies new entries to our db. We have a table in a SQL 2000 db that ha
s a
> list of computer IDs, comments and date of entry. There can, and usually i
s,
> multiple entries of comments for each computer.
>
> What I have been asked to extract is if a new Computer ID is entered.
>
> Ex. My machine name is: workstation123. It already has several entries in
> the database which are already handled. If workstation124 gets an entry an
d
> it is not already in the db - I need to be able to query that.
>
> The table is simple and has only 4 columns:
> CompName - nvarchar
> TheDate - datetime
> TheComment - ntext
> TheID - int {Identity}
>
> Any idea how I can extract all of the ComputerIDs {CompName} on the most
> recent date {TheDate} that do not appear anywhere else in the table {all
> other dates}?
> Thanks