Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-21
15
Medium Priority
?
395 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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