Find multiple instances of field1 where field2 does not match

[bmacbmac]

Hi,

I have the following data:

create table #temp (filename varchar(20), filedate datetime)
insert into #temp values ('D0062277.001','2005-12-15 08:57:00.000')
insert into #temp values ('D0062277.001','2005-12-15 08:57:00.000')
insert into #temp values ('D0062284.001','2005-12-19 08:31:00.000')
insert into #temp values ('D0062284.001','2013-01-20 08:31:00.000')
insert into #temp values ('D0062288.001','2002-01-25 05:08:00.000')
insert into #temp values ('D0062288.001','2002-01-25 05:08:00.000')
insert into #temp values ('D0062296.001','2005-05-04 11:27:00.000')
insert into #temp values ('D0062296.001','2005-05-04 11:27:00.000')


Each 'Filename' exists more than one time. I would like to pinpoint those files that exist more than once, but where the 'filedate' differs.

For example, 'D0062284.001' exists once with date 2005-12-19 and once with 2013-01-20.

Can anyone think of a way I can do this?

Thanks!

 

[imex]

Hi,

Try:

select filename, count(distinct filedate) as Qty
from #temp
group by filename
having count(distinct filedate) > 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[djj55]

How about something like:

;WITH cte AS (SELECT DISTINCT FileName, FileDate FROM #temp) 
SELECT FileName, COUNT(*) as cnt 
FROM cte 
group by FileName 
Having COUNT(*) > 1;

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Motlatjo]

This might work

Select filename, COUNT(fd.filename) CNT
From(
Select filename, FileDate, COUNT(fileDate) Date From temp
Group by filename, FileDate
) fd
Where fd.Date = 1
Group By fd.filename