Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help

Author  Topic 

tlug
Starting Member

21 Posts
Posted - 2012-01-26 : 19:25:32
Hello All,

I am trying to write a query to get the number of minutes between a certain time. So for example I need a sum of the minutes between 10:00 and 14:00..

The problem I am having is the start and end time can be anywhere from 6am to midnight. A sample of my data can be found at the link below.. I need a query telling me all the minutes between a timeframe, such as 10:00 and 14:00.. I just can't figure out how to nest the if statements or whatever I need to do..

[url]http://www.box.com/s/42u8h6zmejay8oo2jpzt[/url] 

SELECT vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop

, CAST(SUM(
                            CASE WHEN DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) < '0'
                            THEN '1440' + DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) 
                            ELSE DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop)
                            END
                       ) AS REAL)  totMins

, CAST(SUM(
                            CASE WHEN DATEDIFF(minute, '14:00', vc_stop) > '0'
                            THEN DATEDIFF(minute, '14:00', vc_stop) 
                            ELSE DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop)
                            END
                       ) AS REAL)  minusMins

FROM [IEX_Data_Store].[dbo].[IEX_TX_AgentSchedule]
Where vc_schedDate = '20120125'
and int_muID = '3500'
--and vc_start <= '10:00' or vc_stop >= '14:00'
and vc_exception in ('Open Time', 'Coaching', 'Customer Call Back', 'Extended Hours', 'Floor Support', 'Performance Coach', 'Spec Asgnmnt NoPremi', 'System Down Minacs')
group by vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
order by vc_exception, vc_start


Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-26 : 19:53:33
see an example function to get time difference between two values. you can either use it directly or use logic inline in query

http://visakhm.blogspot.com/2010/03/time-difference-function.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 00:03:49
That page just confuses me even more.. There has to be an easy way to do this.. Suming the time is easy, but getting it to only sum the minutes between a time period is where I am not able to figure out..

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 08:26:29
quote:
Originally posted by tlug

That page just confuses me even more.. There has to be an easy way to do this.. Suming the time is easy, but getting it to only sum the minutes between a time period is where I am not able to figure out..

Thanks!

its also easy

sum(datediff(mi,timestart,timeend))

etc and you're done

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 08:32:20
That part is easy, I can get the difference between the start and end time.. what I need is slightly different... for example my agent works 9am to 3pm and I want it to tell me how many minutes the agent worked between 10am and 2pm... the formula tells me that it is 6 hours when the answer I need is 4hours, just as example..

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 08:38:31
ok..then use this

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 09:24:13
Ok that looks interesting.. I am not used to using functions in SQL, kind of a new concept to me. So I would insert your code at the top of my SQL statement? And then how would I add it into the rest of the sql statment..

Would I swap out the date and times below with the column names from the database?

So something like
SELECT dbo.BusinessHours(vc_date,vc_date,vc_start,vc_stop)

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 09:34:19
yes exactly. you can call it inline in your select statement as posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 09:47:40
ok so does the sql statement below look correct with your function? Where would I tell it that I only want to know the number of minutes between 10:00 and 14:00? Do I change that in the function, or in the select statement??

So if agent A is working 9am to 3pm and I just want to know how many minutes he worked between 10 and 2.

SELECT vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
SELECT dbo.BusinessHours(vc_schedDate,vc_schedDate,vc_start,vc_stop)

, CAST(SUM(
                            CASE WHEN DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) < '0'
                            THEN '1440' + DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) 
                            ELSE DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop)
                            END
                       ) AS REAL)  totMins

FROM [IEX_Data_Store].[dbo].[IEX_TX_AgentSchedule]
Where vc_schedDate = '20120125'
and int_muID = '3500'
and vc_exception in ('Open Time', 'Coaching', 'Customer Call Back', 'Extended Hours', 'Floor Support', 'Performance Coach', 'Spec Asgnmnt NoPremi', 'System Down Minacs')
group by vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
order by vc_exception, vc_start


Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 10:46:34
quote:
Originally posted by tlug

ok so does the sql statement below look correct with your function? Where would I tell it that I only want to know the number of minutes between 10:00 and 14:00? Do I change that in the function, or in the select statement??

So if agent A is working 9am to 3pm and I just want to know how many minutes he worked between 10 and 2.

SELECT vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
SELECT dbo.BusinessHours(vc_schedDate,vc_schedDate,vc_start,vc_stop)

, CAST(SUM(
                            CASE WHEN DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) < '0'
                            THEN '1440' + DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) 
                            ELSE DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop)
                            END
                       ) AS REAL)  totMins

FROM [IEX_Data_Store].[dbo].[IEX_TX_AgentSchedule]
Where vc_schedDate = '20120125'
and int_muID = '3500'
and vc_exception in ('Open Time', 'Coaching', 'Customer Call Back', 'Extended Hours', 'Floor Support', 'Performance Coach', 'Spec Asgnmnt NoPremi', 'System Down Minacs')
group by vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
order by vc_exception, vc_start


Thanks!

you dont need additional select
also i assume you've created the function beforehand

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 11:54:53
Im missing something somewhere, probably between the rest of my query and your function. I get the error:

Msg 156, Level 15, State 1, Procedure BusinessHours, Line 81
Incorrect syntax near the keyword 'SELECT'.


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, --start of period of consideration for calculation of business hours
@EndDate datetime, --end of period of consideration for calculation of business hours
@BusinessStart datetime, --start of business hours
@BusinessEnd datetime --end of business hours
)
RETURNS int
AS
BEGIN
DECLARE @TotalHours int


IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
	IF @BusinessStart>@BusinessEnd
	BEGIN
		SET @TotalHours= -1
	END
	ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
		SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
						THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
 SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
  ELSE @StartDate
  END,
 @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
  ELSE @EndDate
  END 




 ;With Calendar_CTE (Date,Day,WeekDay)
 AS
 (
 SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END


 UNION ALL


 SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
 FROM Calendar_CTE
 WHERE DATEADD(dd,1,Date) <= @EndDate
 )




 SELECT @TotalHours=SUM(
  CASE WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
  END
  )/60
 FROM Calendar_CTE c
 LEFT JOIN Holiday h
 ON h.[date]= c.Date
 WHERE WeekDay=1
 AND h.[date] IS NULL
 OPTION (MAXRECURSION 0)
END


RETURN @TotalHours

SELECT vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop, dbo.BusinessHours(vc_schedDate,vc_schedDate,vc_start,vc_stop)

, CAST(SUM(
                            CASE WHEN DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) < '0'
                            THEN '1440' + DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop) 
                            ELSE DATEDIFF(minute, dt_actualTZ_start, dt_actualTZ_stop)
                            END
                       ) AS REAL)  totMins

FROM [IEX_Data_Store].[dbo].[IEX_TX_AgentSchedule]
Where vc_schedDate = '20120125'
and int_muID = '3500'
and vc_exception in ('Open Time', 'Coaching', 'Customer Call Back', 'Extended Hours', 'Floor Support', 'Performance Coach', 'Spec Asgnmnt NoPremi', 'System Down Minacs')
group by vc_schedDate, int_muID, vc_agentName, vc_exception, vc_start, vc_stop
order by vc_exception, vc_start


Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 13:38:34
you need a GO between function definition and final select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tlug
Starting Member

21 Posts
Posted - 2012-01-27 : 14:05:18
Gonna have to find a different way I guess, I don't have access to create a function on the database.

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-01-27 : 14:16:34
ah...then another way is to apply the logic inline in your select statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site