Display rows baased on count. 1

[sparkbyte]

I am having problems figuring out the rith way to do this.

Select
	BoxNumber,
	FileNumber,
	TrackingDate,
	EmployeeID
From dbo.tblTrackingTable
Where (
Select 
	FileNumber,
	Count(FileNumber) As 'Number of duplicates'
From dbo.tblTrackingTable
WHERE
	(TrackingYear = '2011' and TrackingMonth >= '1')
AND (FileNumber <> '.BOX.END.')
AND	(EmployeeID = 'jsmith')
Group By FileNumber
	Having (Count(FileNumber) > 1)
	)

Thanks

John Fuhrman

 

[gmmastros]

try this:

Select
    BoxNumber,
    FileNumber,
    TrackingDate,
    EmployeeID,
    Duplicates.[Number of duplicates]
From dbo.tblTrackingTable
     Inner Join 
      (
        Select 
              FileNumber,
              Count(FileNumber) As [Number of duplicates]
        From  dbo.tblTrackingTable
        WHERE (TrackingYear = '2011' and TrackingMonth >= '1')
              AND (FileNumber <> '.BOX.END.')
              AND (EmployeeID = 'jsmith')
        Group By FileNumber
        Having (Count(FileNumber) > 1)
      ) As Duplicates
      On tblTrackingTable.FileNumber = Duplicates.FileNumber

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

That look right but I get

Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'FileNumber'.



Thanks

John Fuhrman

 

[sparkbyte]

This corrected the issue,

Select
    tblTrackingTable.BoxNumber,
    tblTrackingTable.FileNumber,
    tblTrackingTable.TrackingDate,
    tblTrackingTable.EmployeeID,
    Duplicates.[Number of duplicates]
From dbo.tblTrackingTable
     Join 
      (
        Select 
              FileNumber,
              Count(FileNumber) As [Number of duplicates]
        From  dbo.tblTrackingTable
        WHERE (TrackingYear = '2011' and TrackingMonth >= '1')
              AND (FileNumber <> '.BOX.END.')
              AND (EmployeeID = 'jlolson')
        Group By FileNumber
        Having (Count(FileNumber) > 1)
      ) As Duplicates
      On tblTrackingTable.FileNumber = Duplicates.FileNumber

Thanks

John Fuhrman

 

[bborissov]

You must qualify the columns which have the same name in both tables:

Select
    TableName.BoxNumber,
    TableName.FileNumber, -- Here
    TableName.TrackingDate,
    TableName.EmployeeID,
    Duplicates.[Number of duplicates]
...

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.