Challenge:Max Concurrent CalRecordings Per month

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

SQL Server 2008 Forums

Transact-SQL (2008)

Challenge:Max Concurrent CalRecordings Per month

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-09-19 : 02:14:14

Hi Friends,

I have a new requirement to find the max number of concurrent recordings happened in a month.

1. We have 2 tables Table A(P_Id --Pri Key) & Table B (F_Id --- For Key).

2. Table B stores the call records for a phone call with recordingstarttime, recordingendtime.

3. From Table A for a particular ID, lets say for ID -1, the call details will store in Table B with any number of members.

4. In Table B - For a call lets say 5 participants. A recording started at 2014-09-19 06:58:12.630 and ended at 2014-09-19 07:14:18.737.

5. 5 Participants joined in this time frame. One participant started recording the call from 2014-09-19 06:58:12.630 and 2014-09-19 06:59:12.630 using ID -1.

6. Similarly another 2nd participant started recording the call in different time frame, 2014-09-19 07:12:18.737 and 2014-09-19 07:14:18.737.

7. So finally there are 2 recordings happened in this meeting in that time frame.

8. There will be many such cases happen concurrently just like our Airtel, Vodafone. ( Max number of participants involved in call conference for a month)

9.So i have already created a table as below with time difference 5 minutes for one ID (Identity)

ID Starttime Endtime
1 2013-01-01 00:00:00.000 2013-01-01 00:05:00.000
2 2013-01-01 00:05:00.000 2013-01-01 00:10:00.000

.....................

I would require some one's help in writing a Query either comparing with this Time Diff Table and provide the max concurrent participants involved recording the phone call

(or)

With out using this Time Diff Table.

Note: Here only Year & Month parts are only to be considered from Table B recordingstarttime & recordingendtime Time stamp columns.

So finally if we pass the month & year parameters to the above,
the result should come keeping the maximun concurrent recordings for a month.

vijay1234
Starting Member

48 Posts

Posted - 2014-09-19 : 02:39:51

Just to simplify

select * from TableB where DATEPART(YYYY,Recordingstarttime) = '2014' and DATEPART(MM,Recordingstarttime) = '08' order by ID

which gives me the result as below:

ID RecordingStarttime RecordingEndtime
13 2013-10-15 07:56:41.140 2013-10-15 07:57:02.147 ---- a
13 2013-10-15 07:13:27.927 2013-10-15 07:14:18.737
14 2013-10-15 07:34:25.187 2013-10-15 07:34:36.433
14 2013-10-15 07:35:50.517 2013-10-15 07:36:42.873
14 2013-10-15 07:52:31.573 2013-10-15 07:53:34.473
14 2013-10-15 07:56:55.417 2013-10-15 07:57:21.593 ----- b
16 2013-10-15 09:49:24.300 2013-10-15 09:50:14.703
16 2013-10-15 09:55:52.597 2013-10-15 09:57:28.077
16 2013-10-15 10:09:45.717 2013-10-15 10:11:35.080
16 2013-10-15 11:14:36.953 2013-10-15 11:15:08.597
18 2013-10-15 11:52:52.147 2013-10-15 11:53:13.937
19 2013-10-16 05:13:46.037 2013-10-16 05:14:00.720
19 2013-10-16 05:19:19.323 2013-10-16 05:19:41.440
19 2013-10-16 05:20:08.007 2013-10-16 05:20:29.780
19 2013-10-16 05:22:33.033 2013-10-16 05:23:08.803
20 2013-10-16 05:59:25.260 2013-10-16 06:01:28.277
20 2013-10-16 06:06:39.447 2013-10-16 06:09:45.720
20 2013-10-16 06:24:19.990 2013-10-16 06:26:03.737
21 2013-10-16 06:48:12.097 2013-10-16 06:48:23.810

For an ID - 13 there are 2 recordings happened. Similarly for 14 there are 4 recordings happened.

So here i would like to tell you fewimportant points.
a. Form the above result set, for ID -13, for a meeting the recordingstarttime fallen at one time. I marked as a.

b. Similarly for ID -14, for a different meeting, one of the participants started recording at the same time what ID-13 meeting participant started. I marked as 'b' from the above results.

So now, i would like to know the maximum concurrent(parallel) such recordings happened for a particular month.

Here for this recordingstarttime 2013-10-15 07:56:55.417, considering the hours and minutes there are 2 such recordings happened. Their might be many such in a particular month.

Hope i'm clear with my requirement.

So now i would require a TSQL query to find the max concurrent recordings happened for a particular month.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources