Finding mins and max time from records with different start time other that 12:00 midnight

I have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm -- 2:00 am

6:30 am -- 8:30 am

2:00 am - 3:45 am

11:00 am - 4:00pm

12:00 am - 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

In my opinion it's not totally clear exactly what you're looking for.

In the example that you supplied, what results would you expect to be returned by the query? Also, are you storing the data as DATETIME values?

Thanks
Chris

|||Sorry. Since the min begin time is 6:00 am and the maximum end time time is 5:59 am, the expected result should be 6:30 am - 3:45am.|||

Thanks for providing the extra info.

Please could you also clarify what datatype you are using to store the times as this will affect the solution.

Thanks
Chris

|||

job:

I think that I also am not sure exactly what you are trying to do. Maybe a starting point is to substract six hours from your "startTime" to establish a "work day"; something like:

declare @.timeStuff table
( shiftId integer,
startTime datetime,
endTime datetime
)

insert into @.timeStuff values ( 1, '3/4/7 22:00', '3/5/7 2:00' )
insert into @.timeStuff values ( 2, '3/5/7 06:30', '3/5/7 8:30' )
insert into @.timeStuff values ( 3, '3/5/7 02:00', '3/5/7 3:45' )
insert into @.timeStuff values ( 4, '3/5/7 11:00', '3/5/7 16:00')
insert into @.timeStuff values ( 5, '3/6/7 00:00', '3/6/7 3:40')

declare @.offset datetime set @.offset = '06:00:00.000'

select convert (varchar(8), startTime - @.offset, 101) as [workDate ],
left(convert (varchar(8), startTime, 108), 5) as startTime,
left(convert (varchar(8), endTime, 108), 5) as endTime
from @.timestuff
order by startTime

-- workDate startTime endTime
-- -
-- 03/05/20 00:00 03:40
-- 03/04/20 02:00 03:45
-- 03/05/20 06:30 08:30
-- 03/05/20 11:00 16:00
-- 03/04/20 22:00 02:00

|||

If 4 am is not legal how did it get into your db in the first place, going by what you want there shoudl be nothing less than 6.00 am in your Start column. Get the place of entry sorted out and you should fine.

for the bad data u posted above you could still use

Select min(start), Max(End)

from tablename

where

convert(use conversion to convert time to last digits in time in start)>5.59 and

and convert(use conversion to convert time to last digits in time)<

--you can figure out the where condtion

point is it can be done

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding duplicates - what on earth am I overlooking?

I have two lists of contacts. They're similar. I want a list of all the contacts whose email address occurs only in the first list.

SELECT COUNT(DISTINCT EMAIL) FROM List1
returns 13460

SELECT COUNT(DISTINCT EMAIL) FROM List2
returns 13220

SELECT EMAIL FROM List1 WHERE EMAIL NOT IN (SELECT DISTINCT EMAIL FROM List2)
returns 0 rows

How can it be returning no rows? What am I failing to take into consideration?

:confused:selecting from lis2 where not in ( list 1 )|||Looks ok to me. This will be more efficient so give it a whirl and see if it sorts out your problem anyway:
SELECT EMAIL
FROM List1
WHERE NOT EXISTS(SELECT * FROM List2 WHERE List2.Email = List1.Email)|||Yup, thanks :) I added a bit to take the nulls out, but this seems to work. At least I get rows returned. I'm still at a loss as to why my first effort didn't work though... :confused:

SELECT EMAIL FROM List1
WHERE NOT EXISTS(SELECT * FROM List2 WHERE List2.email = List1.EMAIL AND List2.email IS NOT NULL)
AND List1.EMAIL IS NOT NULL|||You shouldn't need to explicitly remove NULLS... unless you have turned off ANSI_NULLS.|||I've no idea what state my ansi_nulls are in, but both tables have records where the EMAIL field is null, and if I run the query without the nulls-removing bits, just over half of the 13,000-or-so email addresses returned are NULL. I'm just anticipating the account manager coming back to me with "Who's this NULL bloke and why is he spamming our contacts database?"|||Sorry yes - not thinking through. And good looking blocking that NULL fella ;)

finding active users from sysprocesses

I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1You can use the system stored procedure sp_who & sp_who2 for these purposes.

--
- Anith
( Please reply to newsgroups only )|||kr (zzb26 (at) email.com) writes:
> I am trying to find a select on sysprocesses that would list all the
> active logins. An active login is a login that has a TSQL statment being
> executed on the server,
> This didnt work to well! Any ideas. Thanks in advance.
> select sp.loginame,
> -- more columns
> from master..sysprocesses sp
> where sp.status not in ('sleeping','background' )
> order by 1

In which way did it not work? In any case, you definitely want to add
"or opentrn > 0". A process which has a open transaction is active,
even if it is sleeping.

I have a lock-monitoring routine, and the condition I use is

upper(p.cmd) <> 'AWAITING COMMAND'

I also check whether sysprocesses.blocked > 0.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Finding a users is adult or minor using a simple select query

my problem is that i need to find if a user is adult or minor using a
simple select query .I have the date of birth of the user and a column
named isadult as well in the table but the isadult column also updates
only when the user enters or updates his/her details.
I even tried using a sql function bt it wont allow me to use getdate
function.

Any solution are welcome unless it makes me fetch records using a
select query in a stored procedure.

Thanx in AdvanceUdit Handa wrote:
> my problem is that i need to find if a user is adult or minor using a
> simple select query .I have the date of birth of the user and a
column
> named isadult as well in the table but the isadult column also
updates
> only when the user enters or updates his/her details.
> I even tried using a sql function bt it wont allow me to use getdate
> function.

Can you post the exact syntax for the SQL function you used?|||On 23 Mar 2005 03:58:56 -0800, Udit Handa wrote:

>my problem is that i need to find if a user is adult or minor using a
>simple select query .I have the date of birth of the user and a column
>named isadult as well in the table but the isadult column also updates
>only when the user enters or updates his/her details.
>I even tried using a sql function bt it wont allow me to use getdate
>function.
>Any solution are welcome unless it makes me fetch records using a
>select query in a stored procedure.
>Thanx in Advance

Hi Udit,

The best solution is to remove the IsAdult column, drop the function and
create a view instead:

CREATE VIEW MyView
AS
SELECT Col1, Col2, ..., ColN, BirthDate,
CASE WHEN BirthDate > DATEADD (year, -18, CURRENT_TIMESTAMP)
THEN 'Underage'
ELSE 'Adult'
END AS IsAdult
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I need to find if a user is adult or minor using a simple select
query .I have the date of birth of the user and a column named isadult
as well in the table but the isadult column also updates only when the
user enters or updates his/her details. <<

Now you have learned one of the many reasons that we do not use flags
in SQL, like we did in punch card systems. Drop the "isadult" flag; it
is a redundant computed column and create a VIEW that uses the date of
birth with the CURRENT-TIMESTAMP to determine this fact.

I also hope that you are not using the proprietary, low-level BIT data
type for this incorrect design.

find_in_set in mssql?

Hello
i try to migrate from MySQL to MSSQL
i have a query lik
SELECT [...] FIND_IN_SET(id, "1,53,82,102,435") [...
but MSSQL don't know the string function "FIND_IN_SET"
My question is now
is there a FIND_IN_SET-like function for MSSQL
for those who not know what FIND_IN_SET do
FIND_IN_SET(str, str_list
str_list is a string list consiting of substrings separated by commas
FIND_IN_SET returns the index of "str" within "str_list"
Example
SELECT FIND_IN_SET("2", "0,1,2,3,4")
Returns:
Thanks in advanc
jTry this:
Select [..] from MyTable where id in (1,53,82,102,435)
Amol.
"jg" <anonymous@.discussions.microsoft.com> wrote in message
news:7C4E7C09-5C85-44B6-ABC1-EA9F2BE20A94@.microsoft.com...
> Hello,
> i try to migrate from MySQL to MSSQL.
> i have a query like
> SELECT [...] FIND_IN_SET(id, "1,53,82,102,435") [...]
> but MSSQL don't know the string function "FIND_IN_SET".
> My question is now,
> is there a FIND_IN_SET-like function for MSSQL ?
> for those who not know what FIND_IN_SET do:
> FIND_IN_SET(str, str_list)
> str_list is a string list consiting of substrings separated by commas.
> FIND_IN_SET returns the index of "str" within "str_list".
> Example:
> SELECT FIND_IN_SET("2", "0,1,2,3,4");
> Returns: 3
> Thanks in advance
> jg
>|||Hello Amol
thank you for your reply, but in(...) is for the "where" clause
FIND_IN_SET is for SELECT
For better understanding my complete quer
--8<--8<--8<--8<--
select testtable.*,username as c_user_nam
,find_in_set(id,"4,5,6,48,71,2086,264,1711") as order_i
from testtabl
left join auth_user on c_user=auth_user.user_i
where id in(4,5,6,48,71,2086,264,1711) and (
rel_user_group.user_id='86d87875860b03862a3e4f52e754fad7
and
1=or ( (rel_user_group.group_id=perm_edit or perm_edit='') and (rel_user_group.role_id=role_edit or role_edit='')
or ( (rel_user_group.group_id=perm_child or perm_child='') and (rel_user_group.role_id=role_child or role_child='')
or rel_user_group.group_id='707b826f242b92e278803c605ef81e18
or owner_id='86d87875860b03862a3e4f52e754fad7
order by id,order_i
--8<--8<--8<--
This query sort all returning rows by order_id. order_id is generated automaticaly with the FIND_IN_SET instruction. (It contains the values 1, 2, 3, 4, ...
Thanks for all your help
j|||You can write a function that does this for you.
"jg" <anonymous@.discussions.microsoft.com> wrote in message
news:DFED7D30-FFAA-4D97-B49F-A2EC285BF762@.microsoft.com...
> Hello Amol,
> thank you for your reply, but in(...) is for the "where" clause.
> FIND_IN_SET is for SELECT.
> For better understanding my complete query
> --8<--8<--8<--8<--
> select testtable.*,username as c_user_name
> ,find_in_set(id,"4,5,6,48,71,2086,264,1711") as order_id
> from testtable
> left join auth_user on c_user=auth_user.user_id
> where id in(4,5,6,48,71,2086,264,1711) and (
> rel_user_group.user_id='86d87875860b03862a3e4f52e754fad7'
> and (
> 1=0
> or ( (rel_user_group.group_id=perm_edit or perm_edit='') and
(rel_user_group.role_id=role_edit or role_edit='') )
> or ( (rel_user_group.group_id=perm_child or perm_child='') and
(rel_user_group.role_id=role_child or role_child='') )
> or rel_user_group.group_id='707b826f242b92e278803c605ef81e18'
> or owner_id='86d87875860b03862a3e4f52e754fad7'
> )
> )
> order by id,order_id
> --8<--8<--8<--
> This query sort all returning rows by order_id. order_id is generated
automaticaly with the FIND_IN_SET instruction. (It contains the values 1, 2,
3, 4, ...)
> Thanks for all your help.
> jg
>

Find versus Select error in SQL Express.

I am trying to look up records by the first letter of the last name in SQL Express ADO recordsets.

"Select * from people where people.lastname like 'a%'"

works correctly. (a*) works also.

ptRs.movefirst

PtRs.Find "lastname LIKE 'a%'", 0, adSearchForward

Does not work, does not find anything, but if I use 'a %' ('a<space>%') it finds the first last name starting with a.

Why does a% not work. Can you not use wild characters in Find Statements in SQL Express?

Hi,

find is not a sql server statement rather than a ADO statement: http://www.devguru.com/technologies/ado/quickref/recordset_find.html

For wildcards in ADO you have to use the asteriks.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the ADO versus SQL, I did not understand that.

IT returns the exact same with asterix in the find?

Am I doing it wrong or missing something?

Find the table name corresponding to a page

I am trying to find the table name associated with page#.
This is the query executed.
select * from master..sysprocesses where cpu > 2000
and this is the result.
14:1:3068040 [WAITRESOURCE]
[select * from sysindexes where id = 3068040 does not return any data]
TIA
Manoj Kumar
try select db_name(14) for the database
and select object_name(3068040) for the table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manoj Kumar" <ManojKumar@.discussions.microsoft.com> wrote in message
news:0A0E5BE7-1969-436B-A116-D91E39D9305F@.microsoft.com...
>I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar
|||Manoj, a combination of sp_who2 and sp_lock will give you the locked
resource (or the corresponding current activity screens in EM).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Manoj, you can use DBCC PAGE to show information on page # 3068040.
In the DBCC page output - you will see an object_id that you can tie up to
sysobjects...
"Manoj Kumar" wrote:

> I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar

find the lastest date among many fields

I have seven date fields, and I need to select the lastest date among the
seven fields.
Example
table1
ID, date1, date2, date3, date4,date5, date6, date7
Result should show '10/07/2005'
Thanks,
Culam
CREATE TABLE table1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO table1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')7 date columns in a row looks suspiciously like poor normalization.
Anyway, try the following. Nulls will be ignored.
SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1
David Portas
SQL Server MVP
--|||Culam,
I'm not a design expert but I think you may need to work on normalizing this
a bit.
You could try something like:
SELECT ID,
(SELECT MAX(DATEVALUE)
FROM
(SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
SELECT DATE2 FROM TABLE1 UNION ALL
SELECT DATE3 FROM TABLE1 UNION ALL
SELECT DATE4 FROM TABLE1 UNION ALL
SELECT DATE5 FROM TABLE1 UNION ALL
SELECT DATE6 FROM TABLE1 UNION ALL
SELECT DATE7 FROM TABLE1)
AS Z)
FROM TABLE1
or use the MAX function with a temp table.
HTH
Jerry
"culam" <culam@.discussions.microsoft.com> wrote in message
news:49405690-F7F1-4E6A-BCCD-27407C0E35E7@.microsoft.com...
>I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Try,
CREATE TABLE t1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO t1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')
select [id], max(c1)
from
(
select [id], date1
from t1
union all
select [id], date2
from t1
union all
select [id], date3
from t1
union all
select [id], date4
from t1
union all
select [id], date5
from t1
union all
select [id], date6
from t1
union all
select [id], date7
from t1
) as t2([id], c1)
group by [id]
-- or
select
[id],
max(
case t2.c1
when 1 then date1
when 2 then date2
when 3 then date3
when 4 then date4
when 5 then date5
when 6 then date6
when 7 then date7
end
)
from t1 cross join (select 1 as c1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7)
as t2
group by [id]
drop table t1
go
AMB
"culam" wrote:

> I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Jerry Spivey wrote:
> SELECT ID,
> (SELECT MAX(DATEVALUE)
> FROM
> (SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
> SELECT DATE2 FROM TABLE1 UNION ALL
> SELECT DATE3 FROM TABLE1 UNION ALL
> SELECT DATE4 FROM TABLE1 UNION ALL
> SELECT DATE5 FROM TABLE1 UNION ALL
> SELECT DATE6 FROM TABLE1 UNION ALL
> SELECT DATE7 FROM TABLE1)
> AS Z)
> FROM TABLE1
>
Did you try that with more than one row of dates? If Culam wants just a
single maximum date then you may as well remove the outer part of the
query.
David Portas
SQL Server MVP
--|||Was just about to post how your code rocks and mine is would be sluggish
with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
Thanks for the follow up...always learning from you.
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
> Jerry Spivey wrote:
> Did you try that with more than one row of dates? If Culam wants just a
> single maximum date then you may as well remove the outer part of the
> query.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
Might want to change it up a bit for multiple rows to something like this:
SELECT TOP 1 X.ID, MAX(X.MAXDATE) AS 'MAX DATE'
FROM
(SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1) AS X
GROUP BY X.ID
ORDER BY MAX(X.MAXDATE)DESC
Thoughts?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23xMgPgpzFHA.2792@.tk2msftngp13.phx.gbl...
> Was just about to post how your code rocks and mine is would be sluggish
> with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
> Thanks for the follow up...always learning from you.
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
>|||Just my usualy caveats around TOP: Use an ORDER BY that's guaranteed to
be unique or specify TOP WITH TIES. Random results due to ties can be a
lot of hassle.
David Portas
SQL Server MVP
--

find the greater element in list

I have a table with 2 field Id and amount.

I want to select the maximum amount for all the Ids.

e.g.

IdAmount

1

23

1

47

2

23

250

37

The result set should be

1

47

2

50

3

7

Can any one give any sql query to

fetch the appropriate result?

select ID, max(Amount)

from your table

Group by id

|||

Did it work?

If so, can you mark answer as correct. thanks

sql

find the best way

hi,
i have a query like this:
select id from jointbs where1
union
select id from jointbs where2 and id not in (select id from jointbs where1)
is it any good way to handle this case? thanks...select id ... where1
union all
select id ... where2
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"js" <js@.someone.com> wrote in message
news:OpfdFXVRFHA.2348@.tk2msftngp13.phx.gbl...
> hi,
> i have a query like this:
> select id from jointbs where1
> union
> select id from jointbs where2 and id not in (select id from jointbs
> where1)
> is it any good way to handle this case? thanks...
>
>|||Sorry, late night fritz, I was thinking the opposite.
Can you please give better specs. See my sig for more info.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Thanks Aaron,
how about this:
do I have to use the "not in" clause?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uBXqUnVRFHA.244@.TK2MSFTNGP12.phx.gbl...
> select id ... where1
> union all
> select id ... where2
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "js" <js@.someone.com> wrote in message
> news:OpfdFXVRFHA.2348@.tk2msftngp13.phx.gbl...
>|||PLEASE post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Find string between 2 characters and insert in different column

I have data in a char column like this:
[1]
[2]
[3]
etc
[100]
[101]
etc
[1000]
etc
I want to select everything between the brackets and insert into a different
column.
Any help please.How about
SELECT REPLACE(REPLACE(YourCol,'[',''),']','')
FROM YourTable
Andrew J. Kelly SQL MVP
"Terri" <terri@.cybernets.com> wrote in message
news:d4p3vr$l0e$1@.reader2.nmix.net...
>I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a
> different
> column.
> Any help please.
>
>|||UPDATE Floob
SET new_foo =
REPLACE ( REPLACE (foobar, '[', ''), ']' '');
or you can do this in a VIEW or computed column.|||As a general approach, you can use the following expression:
SUBSTRING( @.s, CHARINDEX( '[', @.s ) + 1,
CHARINDEX( ']', @.s,
CHARINDEX( '[', @.s ) ) -
CHARINDEX( '[', @.s ) - 1 )
Anith|||Hi Terri,
Try this query...
SELECT Substring(YouCol,2,len(youCol)-2) FROM YourTable
Swami.
"Terri" wrote:

> I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a differe
nt
> column.
> Any help please.
>
>

find space (MS SQL 2005)

how can I find a space into a string ? (for MS SQL 2005)
SELECT name FROM tbNames WHERE name LIKE '% % '
is not working
thank youwhat do you mean it's not working? it should

can you give an example of a name with a space in it where it doesn't work?|||I dont understand ... now it works

thanks r937|||sounds like a heisenbug|||Usually when that happens to me it is caused by an id-10-t error.

-PatP|||... heisenbugthank you so much, what a gorgeous word, that's brilliant

:)

pat, see A Truly ID-iotic Design (http://worsethanfailure.com/Articles/A_Truly_ID-iotic_Design.aspx)

Edit: oh hai, it r down atm, plz try again later, kthxbye|||do you mean it can happen time to time with no reason ?|||it's not my creation. there are several related ones:

http://en.wikipedia.org/wiki/Heisenbug

find records from 2 situations?

Hi All, I am trying to find records when searchProductCount = 2 AND when searchProductCount < 2 BUT productID not in (select pid from TableB) ... I have the query below ... but is there any other better way to do this?

TableB has IDs: 100, 700 ...etc

eg: searchProduct ID is 50,100

-- means returns everything when we found ALL productID (50,100) from TableA, count =2

Select productName, productID from TableA where searchProductCount = 2 AND productID IN (50,100)

union all

-- when not all productID found in TableA, we only return productsID from TableA which ID found in TableB, count < 2

Select productName, productID from TableA where searchProductCount < 2 and productID IN (select pid from TableB) -- in this case, pid found in TableB from searchProductID will be 100

--------------------

it comes out there are duplicates results (when first query is valid, we union all second query, so we have duplicates records). How can we eliminate the duplicates? Or is there better way to acheive this without using union all?

>>>>Select productName, productID from TableA where searchProductCount < 2 and productID IN (select pid from TableB) -- in this case, pid found in TableB from searchProductID will be 100

Why would (select pid from TableB) be only 100? It will return every productid from the tableB. Your question is little confusing. Can you post some sample data from each of the tables and expected output..

|||

Select productName, productID from TableA where searchProductCount <2 andproductID IN (select pid from TableB)-- in this case, pid foundin TableB from searchProductID will be 100

because the search id are 50 and 100 ... in above case, we need to return records where search id is in tableB. So in this case, we are looking for :

Select productName, productID from TableA where searchProductCount <2 and productID IN (100)

|||

post some data from Tables A and B and expected output please..

Find out owner of database for SQL 2000 and 2005 databases

Hello,
As the title says I need to find out the owner of database on both sql 2000 and 2005. I found a query which works great on sql 2005..

select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'

But cannot run this in QA against a sql 2000 database -- "Invalid object name 'sys.databases'."

Anyone know how I can accomplish this?

Select * from master.dbo.sysdatabases doesn't give me any promising data either,

BI was almost there...for anyone looking to do this you need to change a couple of clauses from the 2005 query i posted. Firstly SELECT *
FROM sys.databases -> SELECT * FROM master.dbo.sysdatabases and the column owner_sid needs to be changed to its 2000 equivalent of just sid...

All in all

2005: select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'

2000: select suser_sname(sid) from master.dbo.sysdatabases where name = 'DatabaseName'

B

find out a user/account permission...

is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > is there an easy to find out an user/account's permsions within a
> database?
> > thank you
> > a list of objects and rights.
> > such as
> >
> > select delete insert exec
> > table1 x x
> > table2 x
> > stored proc x
> >
> >
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>

find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez

Find max entry in table

I would like to select the highest id in a table for each computer record.

example data:
ID Computer name
24 computer1
23 computer1
22 computer2
24 computer3

The max id is not going to be the same for every computer.

When the script below is run, I receive the highest number only. All IDs with 24 will be listed. I need all the computer names and the highest ID they are associated with. In the case above, 24 computer1 should be on the list and 23 computer1 should not.

Thanks,

Script...

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
where s.id_snapshot = (Select MAX (id_snapshot)
From servers)Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||mpereziii, your query should work too ... Do you have any issues?

Originally posted by rdjabarov
Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||With the original query mpereziii was getting only rows where id_snapshot was equal to the maximum value in the table.|||Thanks for the response.
I adjusted my script with your suggestion. I received too many rows. 424,000 rows and it searched for 18sec.

The query returned every id_snapshot for every server_name (computer name).

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid|||How about this:

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select server_name, sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid and s.server_name = maxsid.server_name|||Thank you sjp. The query now works very well. I really appreciate everyones input. Thank you.