[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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

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
Hankinater
Asked:
Hankinater
  • 6
  • 5
  • 3
  • +1
1 Solution
 
grishavCommented:
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
 
aelliso3Commented:
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
 
aelliso3Commented:
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 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
HankinaterAuthor Commented:
Thank you...I'll get back to ya
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
HankinaterAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
DATE is a new data type in SQL 2008, so if you are using an older version alter the code to say DATETIME.
0
 
HankinaterAuthor Commented:
oops someone snuck in an answer that last comment from me was for aelliso3
0
 
aelliso3Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
*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
 
HankinaterAuthor Commented:
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
 
HankinaterAuthor Commented:
Thanks for the help!!
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now