CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Ghita
Ghita

Reputation: 4505

SQL - group by issue

I have a table that has records in the form(simplified): ID (int), startTime(DateTime), endTime(DateTime)

I want to be able to group records that "overlap" time duration by minute. Ex: 

1 - 12.00.AM - 12.10.AM ( duration here is 10 min)
2 - 12.05.AM - 12.07.AM (duration here is 2 minutes but is overlapping with record ID = 1 in minutes 05, 06, 07)

The result of such a query should be 
minute 12.00 - record 1, 
minute 12.01 - record 1, 
... 
minute 12.05 - record 1 + record 2, 
minute 12.06 - record 1 + record 2, 
minute 12.07 - record 1 + record 2 
... 
minute 12.10 - record 1

Note I use sql server (2005 uppwards)

Upvotes: 0

Views: 121

Answers (1)

John Doyle
John Doyle

Reputation: 7793

This is one way to do it in Oracle (11g Release 2 as it includes the LISTAGG function):

with CTE as
       (    select STRT + (rownum - 1) / 24 / 60 as TIMES
              from (select min(STARTTIME) as STRT from FORM1)
        connect by level <=
                     (select (max(ENDTIME) - min(STARTTIME)) * 24 * 60
                        from FORM1))
  select to_char(CTE.TIMES, 'hh24:mi') as MINUTE
        ,LISTAGG(ID, ',') within group (order by ID) as IDS
    from   CTE
         join
           FORM1
         on CTE.TIMES <= FORM1.ENDTIME and CTE.TIMES >= FORM1.STARTTIME
group by to_char(CTE.TIMES, 'hh24:mi')
order by to_char(CTE.TIMES, 'hh24:mi')

The test data I used was:

create table FORM1
(
  ID          number
 ,STARTTIME   date
 ,ENDTIME     date
);

insert into FORM1
     values (
              1
             ,to_date('26/01/2012 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
             ,to_date('26/01/2012 00:10:00', 'dd/mm/yyyy hh24:mi:ss'));

insert into FORM1
     values (
              2
             ,to_date('26/01/2012 00:05:00', 'dd/mm/yyyy hh24:mi:ss')
             ,to_date('26/01/2012 00:07:00', 'dd/mm/yyyy hh24:mi:ss'));

And I get the following result:

Minute  IDs
00:00   1
00:01   1
00:02   1
00:03   1
00:04   1
00:05   1,2
00:06   1,2
00:07   1,2
00:08   1
00:09   1

Upvotes: 2

Related Questions