[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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