RETURN ONLY DUPLICATES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

RETURN ONLY DUPLICATES

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-29 : 12:49:24

Hello All,

I have this following Table1:
ID StartTime StopTime
8 2005-10-04 22:35:09.330 2005-10-05 07:00:46.497
9 2005-10-11 22:39:56.900 2005-10-12 07:31:51.373
10 2005-10-11 21:33:57.470 2005-10-12 07:00:27.597
11 2005-10-18 17:03:29.790 2005-10-19 07:33:18.790
12 2005-11-15 23:06:41.350 2005-11-16 07:19:14.510
13 2005-11-15 22:12:53.930 2005-11-16 07:01:58.367
14 2005-11-01 22:26:31.100 2005-11-02 07:00:44.570
15 2005-11-08 21:12:10.170 2005-11-09 07:08:47.340
16 2005-11-09 21:33:54.780 2005-11-10 07:01:40.070
and it goes on for awhile.

I know how to find all the different possible start times but running a DISTINCT statement.

I am curious as to how to ONLY return all duplicate start times BASED ON THE SAME DAY, MONTH, YR not the time.

Result:
9 2005-10-11 22:39:56.900 2005-10-12 07:31:51.373
10 2005-10-11 21:33:57.470 2005-10-12 07:00:27.597
12 2005-11-15 23:06:41.350 2005-11-16 07:19:14.510
13 2005-11-15 22:12:53.930 2005-11-16 07:01:58.367

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:09:45

[code]
SELECT ID, StartTime, StopTime
FROM
(
SELECT COUNT(ID) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,StartTime),0) AS cnt,ID, StartTime, StopTime
)t
WHERE cnt>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-29 : 13:16:02

Hello Visakh,

Thanks for the reply. One followup: Where does the table name "Table1" go?

I ran this:
SELECT ID, StartTime, StopTime
FROM
(
SELECT COUNT(ID) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,StartTime),0) AS cnt,ID, StartTime, StopTime
)Table1
WHERE cnt>1

and it gave me this error message:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:23:34

[code]
SELECT ID, StartTime, StopTime
FROM
(
SELECT COUNT(ID) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,StartTime),0) AS cnt,ID, StartTime, StopTime
FROM Table1
)t
WHERE cnt>1
[/code]

t is just a alias for the inner query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-29 : 13:52:00

I am using SQL Server 2005.

When I run this:
SELECT ID, StartTime, StopTime
FROM
(
SELECT COUNT(ID) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,StartTime),0) AS cnt,ID, StartTime, StopTime
FROM Tablename
)t
WHERE cnt>1

I still get this error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.

Whats wrong?

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:57:26

missed a braces


SELECT ID, StartTime, StopTime
FROM
(
SELECT COUNT(ID) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,StartTime),0)) AS cnt,ID, StartTime, StopTime
FROM Tablename
)t
WHERE cnt>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources