Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query

Status
Not open for further replies.

Timmkatt

IS-IT--Management
Sep 28, 2006
71
US
SQL 2005...
We are trying to run a query to Identify a duplicate web reservation ID and the related fields in that row if there are any. I came up with this query

SELECT WRESID,
COUNT(WRESID) AS NumOccurrences
FROM res2sql.dbo.Reservations
Where serviceType = 1 AND Operation = 'N'
GROUP BY WRESID
HAVING ( COUNT(WRESID) > 1 )

which returns a list of the duplicate WRESID and the count of 2 so the query is finding the duplicates. What I need is to have it also include the PickupTOD and ReservationTOD field. If I try to add those in Select WRESID,PICKUPTOD,RESERVATIONTOD I get a Syntax error. "Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WRESID'".

Any help you can provide is greatly appreciated.

AZ874750,2
AZ1003193,2
AZ1026632,2
AZ1013481,2
AZ090324,2
AZ932285,2
AZ777151,2
AZ742403,2
AZ1007269,2
AZ1202571,2
AZ1202860,2
AZ251231,2
AZ1085117,2
AZ762948,2


 
I think this works on 2005

Code:
select WRESID
      ,PICKUPTOD
      ,RESERVATIONTOD
FROM res2sql.dbo.Reservations t1
inner join 
 (SELECT WRESID
        ,COUNT(WRESID) AS NumOccurrences
    FROM res2sql.dbo.Reservations
   Where serviceType = 1 
     AND Operation = 'N'
   GROUP BY WRESID
  HAVING ( COUNT(WRESID) > 1 )
 ) t2
on t1.wresid = t2.wresid

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top