Solved

T-SQL Video Conference Database - Determine utilization stats per day, when meetings span dates

Posted on 2010-09-21
15
366 Views
Last Modified: 2012-05-10
Experts!

I have a database that shows StartTime and EndTime for video conferences.  Since we are a global company our meetings can (for example)  start at 06/30/2010 11:15PM Eastern and end at 07/01/2010 1:30AM Eastern.  I need to be able to report on utilization per day...so in the above example, I need to to show:
  06/30   45mins
  07/01   90mins
and also be able to include these minutes when geting MTD stats

Should you need it the data looks like this - Table name is ScheduledCall

COLUIMN          VALUE
Id                      1
MeetingName    Meeting1
StartTime          2010-06-30 23:15:00.000                    
EndTime            2010-07-01 01:30:00.000              
MinutesUsed.   135
MeetingRoom    Singapore

I will be utilizing the stats in a dashboard system.

Let me know if you need further information

Much thanks!!

Hank
           
0
Comment
Question by:Hankinater
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 4

Expert Comment

by:grishav
ID: 33726870
Hi,
 you can use the following code snippet:
Hope this helps.
---user-defined function which returns month and day in a format you asked

create function month_and_day(@par datetime) returns varchar(5)

as 

begin

	return right('00' + convert(varchar,datepart(mm,@par)),2) + '/' + right('00' + convert(varchar,datepart(mm,@par)),2)

end

---user-defined function to return the beginning of some date - dropping hours and minutes

create function begin_of_day(@par datetime) returns datetime

as

begin

	return convert(datetime,convert(varchar,@par,110),110)

end



-- the query splits the records where the days are different into two different records and then summarizes it

-- if the conference can last for more than two days, you can add another splitting - hope this is not the case

---you can also rewrite it without udfs - I added it only for clarification of the idea

select dt,sum(mins) from

(

select dbo.month_and_day(StartTime) as dt,datediff(mi,StartTime,EndTime) as mins from ScheduledCall

where dbo.month_and_day(StartTime) = dbo.month_and_day(EndTime)

union

select dbo.month_and_day(StartTime),datediff(mi,StartTime,dbo.begin_of_day(EndTime)) from ScheduledCall

where dbo.month_and_day(StartTime) <> dbo.month_and_day(EndTime)

union

select dbo.month_and_day(EndTime),datediff(mi,dbo.begin_of_day(EndTime),EndTime) from ScheduledCall

where dbo.month_and_day(StartTime) <> dbo.month_and_day(EndTime)

) as a

group by dt

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33727006
Assuming that there will never be a need to go across 3 days, the below will sum it up pretty quickly.
SELECT CONVERT(DATE, StartDay) AS ScheduledDates

	, SUM(DATEDIFF(MINUTE,StartDay, DayEnd)) AS MinuteUsed

FROM (

	SELECT MeetingName

		, StartTime AS StartDay

		, CASE WHEN CONVERT(DATE, EndTime) != CONVERT(DATE, StartTime) 

					THEN CONVERT(DATETIME, CONVERT(VARCHAR(10), CONVERT(DATE, StartTime)) + ' 11:59:59.999 PM' )

					ELSE EndTime

			END AS DayEnd

	FROM ScheduledCall

	UNION

	SELECT MeetingName

		, CASE WHEN CONVERT(DATE, EndTime) != CONVERT(DATE, StartTime) 

					THEN CONVERT(DATETIME, CONVERT(VARCHAR(10), CONVERT(DATE, EndTime)) + ' 12:00:00 AM' )

					ELSE EndTime 

			END AS StartDay

		, EndTime

	FROM ScheduledCall

	) x

GROUP BY CONVERT(DATE, StartDay)

ORDER BY ScheduledDates

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33727090
A couple of small changes to the one I just posted above. Both will work and return the same results, but the union query on the one above is not as efficient:

SELECT CONVERT(DATE, StartDayTime) AS ScheduledDates

	, SUM(DATEDIFF(MINUTE,StartDayTime, EndDayTime)) AS MinuteUsed

FROM (

	SELECT MeetingName

		, StartTime AS StartDayTime

		, CASE WHEN CONVERT(DATE, EndTime) != CONVERT(DATE, StartTime) 

					THEN CONVERT(DATETIME, CONVERT(VARCHAR(10), CONVERT(DATE, StartTime)) + ' 11:59:59.999 PM' )

					ELSE EndTime

			END AS EndDayTime

	FROM #ScheduledCall

	UNION

	SELECT MeetingName

		, CASE WHEN CONVERT(DATE, EndTime) != CONVERT(DATE, StartTime) 

					THEN CONVERT(DATETIME, CONVERT(VARCHAR(10), CONVERT(DATE, EndTime)) + ' 12:00:00 AM' )

					ELSE StartTime 

			END AS StartDayTime

		, EndTime AS EndDayTime

	FROM #ScheduledCall

	) x

GROUP BY CONVERT(DATE, StartDayTime)

ORDER BY ScheduledDates

Open in new window

0
 
LVL 2

Author Comment

by:Hankinater
ID: 33727204
Thank you...I'll get back to ya
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33727269
See if something like this is helpful as an alternative.  It looks like you have some solutions already above, so please check those first:
;with cte as (

   select *

        , dateadd(dd, spt.[number], datediff(dd, 0, StartTime)) as dy

   from VideoConferences vc

   join master..spt_values spt on spt.[type]='P'

      and EndTime >= dateadd(dd, spt.[number], datediff(dd, 0, StartTime))

)

select dy as ScheduledDay

     , case

          when dy < StartTime then datediff(mi, StartTime, dy+1)

          when dy <= EndTime and datediff(dd, dy, EndTime) = 0 then datediff(mi, dy, EndTime)

          else datediff(mi, dy, dy+1)

       end as MinutesUsed

from cte

;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33727283
You can add MeetingName and any of the other columns you would like to the final select by the way.

For explanation of the master..spt_values bit, please see Mark's article here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html
0
 
LVL 2

Author Comment

by:Hankinater
ID: 33727433
the above query gives the following error:
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33727473
DATE is a new data type in SQL 2008, so if you are using an older version alter the code to say DATETIME.
0
 
LVL 2

Author Comment

by:Hankinater
ID: 33727483
oops someone snuck in an answer that last comment from me was for aelliso3
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33727612
So what your saying is ... when the zone says SQL Server 2005 that the user might be using SQL Server 2005. Hmmm, what a great concept ... One of these days I'll learn to read ... :-)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33727739
*laughing* I wasn't going to say that ... :) Easy mistake to make, especially when you are being good and actually testing your solution as it appeared you were doing -- I often do that and then paste the code from my test and get myself in trouble.

@Hankinater: hopefully you understood that I meant aelliso3's code in my last comment.  To fix the issue, just replace all occurrences of DATE with DATETIME as it is simply a case of SQL 2008 data type versus SQL 2005.
0
 
LVL 2

Author Comment

by:Hankinater
ID: 33727818
mwvisa1 - yes I understood.  You're solution is the one that I'm going with...it's working well for me.

I appreciate everyone's responses!
0
 
LVL 2

Author Closing Comment

by:Hankinater
ID: 33727828
Thanks for the help!!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33727974
For my solution -- http:#a33727269 -- my apologies as I just noticed you gave the table name as ScheduledCall, so please replace my made up table VideoConferences accordingly.

By the way, I tried my suggestion with 6/30 - 7/4 and got this:

2010-06-30 00:00:00.000      45
2010-07-01 00:00:00.000      1440
2010-07-02 00:00:00.000      1440
2010-07-03 00:00:00.000      1440
2010-07-04 00:00:00.000      90

Is that the right output you are looking for, or should it be just:

2010-06-30 00:00:00.000      45
2010-07-04 00:00:00.000      90

Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33727996
Guess that answers my last question which I got side tracked in the middle of posting and didn't see your last few comments.
Glad that helped!

Best regards and happy coding,

Kevin
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now