Solved

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

Posted on 2010-09-21
15
361 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you...I'll get back to ya
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
oops someone snuck in an answer that last comment from me was for aelliso3
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
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
Comment Utility
*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
Comment Utility
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
Comment Utility
Thanks for the help!!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

17 Experts available now in Live!

Get 1:1 Help Now