November 30, 2015 at 1:05 pm
This is a tricky one (for me anyways). Would appreciate any ideas on how to do this most effciently.
Let's say I have a table defined as:
Create table queue (id int identity, starttime datetime, finishtime datetime)
each id represents some event and I want to find the maximum number of events which were simultaneously in progress.
for example in the data set like this:
1, Jan-1, Jan-5
2, Jan-2, Jan-4
3, Jan-3, Jan-3
4, Dec-1,Dec-2
5, Dec-1,Dec-1
6, Feb-1, Feb-1
7, Jan-6, Jan-7
The answer would be 3, as the first three events were all in progress on Jan-3.
This is a very simple example, but I would need to solve this for a huge queue table with precise dates and times.
November 30, 2015 at 1:48 pm
NJ-DBA (11/30/2015)
This is a tricky one (for me anyways). Would appreciate any ideas on how to do this most effciently.Let's say I have a table defined as:
Create table queue (id int identity, starttime datetime, finishtime datetime)
each id represents some event and I want to find the maximum number of events which were simultaneously in progress.
for example in the data set like this:
1, Jan-1, Jan-5
2, Jan-2, Jan-4
3, Jan-3, Jan-3
4, Dec-1,Dec-2
5, Dec-1,Dec-1
6, Feb-1, Feb-1
7, Jan-6, Jan-7
The answer would be 3, as the first three events were all in progress on Jan-3.
This is a very simple example, but I would need to solve this for a huge queue table with precise dates and times.
So how about helping us help you. We could use DDL for the table, sample data representative of the actual problem, and expected results based on that sample data. All code should be tested before posting so all any of us would have to do is cut/paste/execute and we would have a test environment to work in.
November 30, 2015 at 1:56 pm
Hi Lynn,
I did include the DDL for the table directly in the post... I was asking kind of a generic question about the approach to the problem, not really specific to the data, but for example sake, here you go:
Create table queue (id int identity, starttime datetime, finishtime datetime)
insert queue (starttime, finishtime) values
('1/1/2015', '1/1/2015'),
('1/2/2015', '1/4/2015'),
('1/3/2015', '1/3/2015'),
('12/1/2015', '1/2/2015'),
('12/1/2015', '12/1/2015'),
('2/1/2015', '2/1/2015'),
('1/6/2015', '1/7/2015')
the expected result would be 3.
November 30, 2015 at 2:02 pm
NJ-DBA (11/30/2015)
Hi Lynn,I did include the DDL for the table directly in the post... I was asking kind of a generic question about the approach to the problem, not really specific to the data, but for example sake, here you go:
Create table queue (id int identity, starttime datetime, finishtime datetime)
insert queue (starttime, finishtime) values
('1/1/2015', '1/1/2015'),
('1/2/2015', '1/4/2015'),
('1/3/2015', '1/3/2015'),
('12/1/2015', '1/2/2015'),
('12/1/2015', '12/1/2015'),
('2/1/2015', '2/1/2015'),
('1/6/2015', '1/7/2015')
the expected result would be 3.
Actually, the sample data will only result in an answer of 2.
November 30, 2015 at 2:07 pm
To give you a head start on me, here are a couple of articles that may help:
November 30, 2015 at 2:14 pm
yes, right- I typo'd the first date. if it were 1/5 as in my original post, the result would be 3.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply