Solved

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

Posted on 2010-09-21
15
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 2

Author Comment

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

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 60

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
 
LVL 60

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 60

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 60

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 60

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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