8
$\begingroup$

I love the bridge I'm gonna make! This is a SO post.

There are not any technicalities involved, you can ignore the code of the declare table insert ..... stuff, and just focus on the dates the OP has, and the ones they need.

I though, "who can guess this obscure fact"? And the answer was "Only the crazy geniuses at puzzling!!!"

Can anyone figure the request out?

$\endgroup$
  • 1
    $\begingroup$ ... the OP has, and the ones 'they need' ? or he needs? $\endgroup$ – user52327 Dec 4 '18 at 16:14
  • $\begingroup$ I know the accepted answer has more detail, but it appears that the first poster was just as correct with determining the request...? $\endgroup$ – El-Guest Dec 4 '18 at 16:44
  • $\begingroup$ @El-Guest That's true! $\endgroup$ – Pablo Lozano Dec 4 '18 at 16:51
9
$\begingroup$
Sample data

     start_udt              end_udt
2018-11-21 10:25:00.000 2018-11-27 17:15:18.000
2018-11-15 07:48:00.000 2018-11-17 17:15:36.000
2018-10-27 04:28:16.000 2018-11-16 12:34:30.000
2018-10-18 03:34:44.000 2018-10-22 16:10:53.000
2018-10-05 03:31:51.000 2018-10-21 02:12:58.000
2018-10-05 03:31:51.000 2018-10-20 22:20:33.000
Need output like this . Can anyone suggest me best way to do it

     start_udt              end_udt
2018-11-21 10:25:00.000 2018-11-27 17:15:18.000
2018-10-27 04:28:16.000 2018-11-17 17:15:36.000
2018-10-05 03:31:51.000 2018-10-22 16:10:53.000

It seems OP wants to

group overlapping events.

        start                     end
A 2018-11-21 10:25:00.000 2018-11-27 17:15:18.000
B 2018-11-15 07:48:00.000 2018-11-17 17:15:36.000
C 2018-10-27 04:28:16.000 2018-11-16 12:34:30.000
D 2018-10-18 03:34:44.000 2018-10-22 16:10:53.000
E 2018-10-05 03:31:51.000 2018-10-21 02:12:58.000
F 2018-10-05 03:31:51.000 2018-10-20 22:20:33.000

It you order it chronologically, row F is the first event.

Row E starts at the same time, but ends after, so

we group these two rows:(F) 2018-10-05 03:31:51.000 (E) 2018-10-21 02:12:58.000

Then D starts before E finishes,

so we group again (F) 2018-10-05 03:31:51.000 (D) 2018-10-22 16:10:53.000

Row C event starts after D is finished

but it is overlapped with B (C) 2018-10-27 04:28:16.000 (B) 2018-11-17 17:15:36.000

And finally,

Event in row A does not overlap any other event

$\endgroup$
  • $\begingroup$ I love this place, congratulations! $\endgroup$ – George Menoutis Dec 4 '18 at 16:41
  • $\begingroup$ Wow, my first answer! $\endgroup$ – Pablo Lozano Dec 4 '18 at 16:43
  • $\begingroup$ Great answer, @PabloLozano!! $\endgroup$ – El-Guest Dec 4 '18 at 18:17
3
$\begingroup$

The request seems to be: there is a table containing pairs of dates, each describing a time interval. The OP is looking for a query combining overlapping intervals, that would give him the minimal set of time intervals exactly covering the time periods that the initial table was covering.

$\endgroup$

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.