Select / Sub Select

[sparkbyte]

I have a table with Columns
Tracking_ID,
EmployeeID,
MachineName,
BoxNumber,
FileNumber,
TrackingDate

What I am trying to do is write a query that takes into account the fallowing.

There will be multiple FileNumbers under a TrackingNumber, but each FileNumber should be unique under that TrackingNumber. FileNumbers CAN and WILL be entered under multiple Tracking Numbers.

This is as close as I have been able to get.

SELECT    
	BoxNumber, 
	FileNumber,
	TrackingDate, 
	EmployeeID,
	MachineName
FROM         tblTrackingTable
WHERE     (TrackingDate IN
			  (SELECT     TrackingDate
				FROM          tblTrackingTable AS tblTrackingTable_1
				GROUP BY TrackingDate
				HAVING      (COUNT(BoxNumber) > 1))) AND (UPPER(FileNumber) LIKE '.box.end.')
ORDER BY BoxNumber

Thanks!!!!!!!!!

Thanks

John Fuhrman

 

[sparkbyte]

Another try at it.

SELECT    
	BoxNumber, 
	FileNumber,
	TrackingDate, 
	EmployeeID,
	MachineName
FROM         tblTrackingTable
WHERE exists
	  (SELECT top 100 percent  
			BoxNumber, 
			FileNumber,
			TrackingDate, 
			EmployeeID,
			MachineName
		FROM tblTrackingTable AS T1
		GROUP BY EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
		HAVING (COUNT(BoxNumber) > 1))
--				AND (UPPER(FileNumber) LIKE '.box.end.')
ORDER BY BoxNumber, FileNumber

Thanks

John Fuhrman

 

[SQLBill]

Can you provide sample data as it would look in the table and what results you are looking for? That would help us have a better idea of what you are looking for.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875