SQLTeam.com | Weblogs | Forums

Returning only datetimes that occur more than once


#1

I've a query that returns, among other things, a column for the date and a column for the time.

CONVERT(VARCHAR(10), DateAttribute, 101) + ' ' + RIGHT(CONVERT(VARCHAR(20), DateAttribute, 100), 7) AS 'Date/Time Viewed',
12/08/2016 12:48PM

I'd like to return only those results whose date and time occur more than once and am having a problem doing this.

All assistance is appreciated.
Thank you!


#2

This might get you started (since you didn't provide table definition and sample data, I went ahead and made some myself):

with yourtable(id,dateattribute)
  as (select id
            ,dateattribute
        from (values( 1,cast('2016-12-21 13:20:45' as datetime))
                   ,( 2,cast('2016-12-21 13:20:57' as datetime))
                   ,( 3,cast('2016-12-21 13:21:03' as datetime))
                   ,( 4,cast('2016-12-21 13:24:14' as datetime))
                   ,( 5,cast('2016-12-21 13:24:27' as datetime))
                   ,( 6,cast('2016-12-22 13:20:45' as datetime))
                   ,( 7,cast('2016-12-22 13:20:57' as datetime))
                   ,( 8,cast('2016-12-22 13:21:03' as datetime))
                   ,( 9,cast('2016-12-22 13:24:14' as datetime))
                   ,(10,cast('2016-12-22 13:24:27' as datetime))
                   ,(11,cast('2016-12-23 13:20:45' as datetime))
                   ,(12,cast('2016-12-23 13:20:57' as datetime))
                   ,(13,cast('2016-12-23 13:21:03' as datetime))
                   ,(14,cast('2016-12-23 13:24:14' as datetime))
                   ,(15,cast('2016-12-23 13:24:27' as datetime))
             ) as yourtable(id,dateattribute)
     )
select a.id
      ,a.dateattribute
      ,convert(varchar(10),a.dateattribute,101)
      +' '
      +right(convert(varchar(20),a.dateattribute,100),7)
       as [Date/Time Viewed]
  from yourtable as a
 where dateattribute>=cast('2016-12-22' as datetime) /* make datatype the same as in your table */
   and dateattribute< cast('2016-12-23' as datetime) /* make datatype the same as in your table */
   and exists(select 1
                from yourtable as b
               where b.dateattribute>=dateadd(minute,datediff(minute,0,a.dateattribute),0)
                 and b.dateattribute<dateadd(minute,datediff(minute,0,a.dateattribute)+1,0)
               having count(*)>1
             )
;

#3
SET STATISTICS TIME,IO ON;

IF Object_id('tempdb.dbo.#abc', 'U') IS NOT NULL 
  DROP TABLE #abc; 

CREATE TABLE #abc 
  ( 
     sampledate DATETIME NULL 
  ) 

INSERT INTO #abc 
SELECT '2016-12-25 14:54:46.160'  UNION ALL 
SELECT '2016-12-25 14:54:46.160'  UNION ALL 
SELECT '2016-12-24 11:54:46.160'  UNION ALL 
SELECT '2016-12-23 10:54:46.160'  UNION ALL 
SELECT '2016-12-22 09:54:46.160'  UNION ALL 
SELECT '2016-12-25 06:54:46.160'  

SELECT #abc.sampledate 
FROM   #abc 
       JOIN (SELECT sampledate, 
                    Count(CONVERT(VARCHAR(10), sampledate, 101) + ' ' 
                          + RIGHT(CONVERT(VARCHAR(20), sampledate, 100), 7)) AS 
                    count 
             FROM   #abc 
             GROUP  BY sampledate, 
                       CONVERT(VARCHAR(10), sampledate, 101) + ' ' 
                       + RIGHT(CONVERT(VARCHAR(20), sampledate, 100), 7)) #def 
         ON #def.count >= 2 
            AND #abc.sampledate = #def.sampledate 

SET STATISTICS TIME,IO OFF;