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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Call Duration not to include business hours or w/e

Author  Topic 

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-01 : 09:13:04
Hello,

I'm trying to add business hours to the below to remove weekends and times outside of business hours so that the duration total does not include these times...I know the code is way out but I'm trying here to get better honest :)

Is there any SQL guru out there that can help me out on this one? not sure how to go about it correctly...will be a good learning curve for me though.


ALTER VIEW [dbo].[ResolvedTime]
AS
Declare @BusinessHoursStart datetime, --start 8am weekdays only
@BusinessHoursEnd datetime, --- end 8pm weekdays only
@Sat datetime,
@Sun datetime


---need to define the correct code here...

Set @BusinessHoursStart = '2009/01/05 08:00:00' -- a Monday date??
Set @BusinessHoursEnd = '2009/01/09 20:00:00' -- a Friday date here??
Set @Weekend = '2009/01/10'



---case or if statement?

IF Time(00,00,00,@BusinessHoursStart < '08:00:00'
Begin
Set ???
End
Else
If Time(00,00,00,@BusinessHoursEnd > '20:00:00'
Begin
Set ???


IF Datename(dw,@Weekend) = 'Saturday'
Begin
Set @Weekend = DateAdd(dd, 2, @Weekend)--move to Monday
End
Else
IF Datename(dw,@Weekend) = 'Sunday'
Begin
Set @Weekend = DateAdd(dd, 1, @Weekend)--move to Monday
End

---the below code all works correctly

Select
IncidentNumber,
CreatedDateTime,
ResolutionDateAndTime,
OwnerTeam,
Priority,
Subject,
TypeofIncident,
[hours] = DATEDIFF(hh,CreatedDateTime,ResolutionDateAndTime),
[minutes] = DATEDIFF(mi,CreatedDateTime,ResolutionDateAndTime),
[seconds] = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
[display] = convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)/3600)+' hours '+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600/60),2)+' minutes '+
right('00'+convert(varchar,DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime)%3600%60),2)+' seconds '

FROM dbo.Incident
--ORDER BY CreatedDateTime

GO

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-01 : 11:26:37
not a guru here but why no create a BusinessDaysHours table? also what if your business hours change when you get bought off next month? are you going to come in here and change the code? how about weekend and holidays. what country are you in? weekend depends on what country you are in etc etc etc

this was very helpful to me a calendar table. might need to be tweaked to your locale.

if exists (select * from sysobjects where id = object_id('dbo.AuxCalendar') and sysstat & 0xf = 3)
DROP TABLE "dbo"."AuxCalendar"
GO


CREATE TABLE dbo.AuxCalendar (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE dbo.AuxCalendar
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO




SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO dbo.AuxCalendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = dbo.AuxComputus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = dbo.AuxComputus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = dbo.AuxComputus(YEAR(Date))-2) -- Good Friday
OR (d.Date = dbo.AuxComputus(YEAR(Date))) -- Easter Sunday
OR (d.Date = dbo.AuxComputus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = dbo.AuxComputus(YEAR(Date))+49) -- Pentecost
OR (d.Date = dbo.AuxComputus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
THEN 'HOLIDAY'
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = dbo.AuxComputus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO


If you don't have the passion to help people, you have no passion
Go to Top of Page

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-01 : 11:42:47
Thing is though, and i'm loathe to criticise such a thorough effort, even this will need ammending annually, also the bank holidays for boxing day, new years day and even christmas day can move if they fall on a weekend and what about the royal wedding in April if you're in the UK? If you're going to go to such extremes, you need to account for these too.

I don't think you neccessarily need to go to such lengths and you've got things in there that don't need so much code.
You can use DATEPART(WEEK,GETDATE()) to get the weeknumber for example.

I have been having a crack at this but I'm going home in 20 minutes so might not get it done, but i'll post what i've got at 5pm.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
Go to Top of Page

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-01 : 11:57:15
[code]
CREATE TABLE Incident (
IncidentNumber INT,
CreatedDateTime DATETIME,
ResolutionDateAndTime DATETIME)

INSERT INTO Incident SELECT 1,'2011-03-31 16:00:00','2011-04-01 09:30:00'
INSERT INTO Incident SELECT 2,'2011-03-29 20:00:00','2011-03-30 09:00:00'
INSERT INTO Incident SELECT 3,'2011-03-28 21:00:00','2011-03-29 09:05:00'
INSERT INTO Incident SELECT 4,'2011-03-26 13:00:00','2011-03-29 10:00:00'
INSERT INTO Incident SELECT 5,'2011-03-27 09:30:00','2011-04-04 10:00:00'
INSERT INTO Incident SELECT 6,'2011-04-01 20:01:00','2011-04-04 09:22:00'
INSERT INTO Incident SELECT 7,'2011-04-01 19:59:00','2011-04-04 08:00:00'

CREATE VIEW vw_Incident AS

SELECT
IncidentNumber
,CreatedDateTime
,ResolutionDateAndTime
,DATENAME(WEEKDAY,CreatedDateTime) as DayLogged
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN 1 END AS OutOfHours
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN
DATEADD(MINUTE,
DATEDIFF(MINUTE,DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,CreatedDateTime))),CreatedDateTime)*-1
,DATEADD(DAY,
CASE WHEN DATEPART(WEEKDAY,CreatedDateTime) = 1 THEN 1
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 7 THEN 2
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 6 THEN 3
ELSE 1 END
,CreatedDateTime))
ELSE CreatedDateTime END AS EffectiveCreatedDateTime
FROM dbo.Incident i

SELECT
CreatedDateTime
,EffectiveCreatedDateTime
,DayLogged
,ResolutionDateAndTime
,DATENAME(WEEKDAY,ResolutionDateAndTime) as DayResolved
,DATEDIFF(HOUR,EffectiveCreateddateTime,ResolutionDateAndTime) as realhours
,DATEDIFF(DAY,EffectiveCreateddateTime,ResolutionDateAndTime) as realdays
,DATEDIFF(HOUR,EffectiveCreatedDateTime,ResolutionDateAndTime) - (DATEDIFF(DAY,EffectiveCreatedDateTime,ResolutionDateAndTime) * 16)

FROM vw_Incident
[/code]

Its not quite there but its close and there's some shabby code but its Friday. I've used a view which is probably not neccessary but its just easier for me in development that way. The approach I took was to create an EffectiveCreatedDateTime which pushed the created date for out of hours incidents forward to 8am on the next weekday morning.

Then the code that looks at the view, asks if there is more than a day between creation and resolution, and if so multiplies the number of days beween creation and resolution by 16 (the number of non working hours) and subtracts this from the actual number of hours.

What this DOESN'T do is take account of minutes, or account for a task which goes over TWO weekends. there may be some further manual tweakage requried, but it could serve as a good start.

You're right. This will be a learning curve but if you're anything like me (or most of the other users of this site) then you'll learn to love challenges like this. Have a good weekend.



---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-01 : 15:02:58
i know it is overkill :) but i did say might need to be tweaked to your locale.

If you don't have the passion to help people, you have no passion
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 07:08:11
Chaps thanks for your input here, I appreciate all your help. I see you are creating a new table for this, is this necessary? all the durations times already reside in the already created Incident table. I just need to not include the hours outside 81m - 8pm and weekends, bank holidays and other dates like Christmas...can be factioned in later I guess but thought this could be done by creating a view from the existing Incident table?
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 07:14:46
quote:
Originally posted by theboyholty


CREATE TABLE Incident (
IncidentNumber INT,
CreatedDateTime DATETIME,
ResolutionDateAndTime DATETIME)

INSERT INTO Incident SELECT 1,'2011-03-31 16:00:00','2011-04-01 09:30:00'
INSERT INTO Incident SELECT 2,'2011-03-29 20:00:00','2011-03-30 09:00:00'
INSERT INTO Incident SELECT 3,'2011-03-28 21:00:00','2011-03-29 09:05:00'
INSERT INTO Incident SELECT 4,'2011-03-26 13:00:00','2011-03-29 10:00:00'
INSERT INTO Incident SELECT 5,'2011-03-27 09:30:00','2011-04-04 10:00:00'
INSERT INTO Incident SELECT 6,'2011-04-01 20:01:00','2011-04-04 09:22:00'
INSERT INTO Incident SELECT 7,'2011-04-01 19:59:00','2011-04-04 08:00:00'

CREATE VIEW vw_Incident AS

SELECT
IncidentNumber
,CreatedDateTime
,ResolutionDateAndTime
,DATENAME(WEEKDAY,CreatedDateTime) as DayLogged
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN 1 END AS OutOfHours
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN
DATEADD(MINUTE,
DATEDIFF(MINUTE,DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,CreatedDateTime))),CreatedDateTime)*-1
,DATEADD(DAY,
CASE WHEN DATEPART(WEEKDAY,CreatedDateTime) = 1 THEN 1
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 7 THEN 2
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 6 THEN 3
ELSE 1 END
,CreatedDateTime))
ELSE CreatedDateTime END AS EffectiveCreatedDateTime
FROM dbo.Incident i

SELECT
CreatedDateTime
,EffectiveCreatedDateTime
,DayLogged
,ResolutionDateAndTime
,DATENAME(WEEKDAY,ResolutionDateAndTime) as DayResolved
,DATEDIFF(HOUR,EffectiveCreateddateTime,ResolutionDateAndTime) as realhours
,DATEDIFF(DAY,EffectiveCreateddateTime,ResolutionDateAndTime) as realdays
,DATEDIFF(HOUR,EffectiveCreatedDateTime,ResolutionDateAndTime) - (DATEDIFF(DAY,EffectiveCreatedDateTime,ResolutionDateAndTime) * 16)

FROM vw_Incident


Its not quite there but its close and there's some shabby code but its Friday. I've used a view which is probably not neccessary but its just easier for me in development that way. The approach I took was to create an EffectiveCreatedDateTime which pushed the created date for out of hours incidents forward to 8am on the next weekday morning.

Then the code that looks at the view, asks if there is more than a day between creation and resolution, and if so multiplies the number of days beween creation and resolution by 16 (the number of non working hours) and subtracts this from the actual number of hours.

What this DOESN'T do is take account of minutes, or account for a task which goes over TWO weekends. there may be some further manual tweakage requried, but it could serve as a good start.

You're right. This will be a learning curve but if you're anything like me (or most of the other users of this site) then you'll learn to love challenges like this. Have a good weekend.

Can this code be used on the existing Incident table, so...SELECT
IncidentNumber
,CreatedDateTime
,ResolutionDateAndTime
,DATENAME(WEEKDAY,CreatedDateTime) as DayLogged
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN 1 END AS OutOfHours
,CASE
WHEN DATEPART(WEEKDAY,CreatedDateTime) IN (1,7)
OR DATEPART(HOUR,CreatedDateTime) IN (20,21,22,23,00,01,02,03,04,05,06,07)
THEN
DATEADD(MINUTE,
DATEDIFF(MINUTE,DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,CreatedDateTime))),CreatedDateTime)*-1
,DATEADD(DAY,
CASE WHEN DATEPART(WEEKDAY,CreatedDateTime) = 1 THEN 1
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 7 THEN 2
WHEN DATEPART(WEEKDAY,CreatedDateTime) = 6 THEN 3
ELSE 1 END
,CreatedDateTime))
ELSE CreatedDateTime END AS EffectiveCreatedDateTime
FROM dbo.Incident i

SELECT
CreatedDateTime
,EffectiveCreatedDateTime
,DayLogged
,ResolutionDateAndTime
,DATENAME(WEEKDAY,ResolutionDateAndTime) as DayResolved
,DATEDIFF(HOUR,EffectiveCreateddateTime,ResolutionDateAndTime) as realhours
,DATEDIFF(DAY,EffectiveCreateddateTime,ResolutionDateAndTime) as realdays
,DATEDIFF(HOUR,EffectiveCreatedDateTime,ResolutionDateAndTime) - (DATEDIFF(DAY,EffectiveCreatedDateTime,ResolutionDateAndTime) * 16)

FROM vw_Incident










---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 09:16:11
Here is a version configurable by day... with multipart days allowed. It should handle multiple weeks and whatnot...

It does NOT do holidays.



/*
Declare @t table (
CreatedDateTime datetime,
ResolutionDateAndTime datetime
)

Insert Into @t Select '4/04/2011 11:37:12.154', '4/04/2011 15:07:20.643'
Insert Into @t Select '4/04/2011 11:37:12.154', '4/05/2011 15:07:20.643'
Insert Into @t Select '4/04/2011 11:37:12.154', '4/12/2011 15:07:20.643'
Insert Into @t Select '4/04/2011 11:37:12.154', '4/19/2011 15:07:20.643'
*/

Declare @workDays table (
dw int,
workBegin datetime,
workEnd datetime
)

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 17:00:00.000' --Monday 8a-5p
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5p
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 12:00:00.000' --Wednesday 8a-12p,
Insert Into @workDays Select 4, '4/13/2011 18:00:00.000', '4/13/2011 22:00:00.000' -- 6p-10p
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5p
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12p


Select
CreatedDateTime,
ResolutionDateAndTime,
seconds,
[display] = convert(varchar,seconds/3600)+' hours '+
right('00'+convert(varchar,seconds%3600/60),2)+' minutes '+
right('00'+convert(varchar,seconds%3600%60),2)+' seconds ',
-- Sum up the various work day sub ranges
sum(actualSeconds),
[actualDisplay] = convert(varchar,sum(actualSeconds)/3600)+' hours '+
right('00'+convert(varchar,sum(actualSeconds)%3600/60),2)+' minutes '+
right('00'+convert(varchar,sum(actualSeconds)%3600%60),2)+' seconds '
From
(
Select
CreatedDateTime,
ResolutionDateAndTime,
seconds = DATEDIFF(ss,CreatedDateTime,ResolutionDateAndTime),
-- Determine actual seconds worked during WorkDay Range
actualSeconds = DATEDIFF(ss,ActualBegin,ActualEnd)
From
(
Select
CreatedDateTime,
ResolutionDateAndTime,
WorkDay = (dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),
WorkBegin,
WorkEnd,
-- Determine the actual begin date and time. The earliest of CreatedDateTime and 'WorkDay Begin'
ActualBegin = case
when CreatedDateTime < dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkBegin),WorkBegin)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkBegin),WorkBegin)
when CreatedDateTime > dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkEnd),WorkEnd)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkEnd),WorkEnd)
else CreatedDateTime end,
-- Determine the actual end date and time. The earliest of ResolutionDateTime and 'WorkDay End'
ActualEnd = case
when ResolutionDateAndTime < dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkBegin),WorkBegin)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkBegin),WorkBegin)
when ResolutionDateAndTime > dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkEnd),WorkEnd)
then dateadd(dy,-datediff(dy,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n),WorkEnd),WorkEnd)
else ResolutionDateAndTime end
--From @t A
From dbo.Incidents A
Inner Join
(
-- Get a number array (spt_values: type 'P' provides 0-2048)
Select
n = Number
From master..spt_values
where type = 'P'
) B
On (dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n) between dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) and dateadd(dy,datediff(dy,0,A.ResolutionDateAndTime),0)
Inner Join @workDays C
On datepart(dw,(dateadd(dy,datediff(dy,0,A.CreatedDateTime),0) + b.n)) = C.dw
) Z
) A
Group By CreatedDateTime, ResolutionDateAndTime, seconds


Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 10:01:13
Hi Corey

I see the times can be changed from the days below to reflect only 8am - 8pm.

Maybe I'm thick but how does the script implement all this? where does it read the records from the Incident table?
Also can you explain why it uses the master db?

Thanks

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 17:00:00.000' --Monday 8a-5p
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5p
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 12:00:00.000' --Wednesday 8a-12p,
Insert Into @workDays Select 4, '4/13/2011 18:00:00.000', '4/13/2011 22:00:00.000' -- 6p-10p
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5p
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12p

Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 10:11:38
Let's see...

- You found the area to change the WorkDay hours

- My script doesn't actually pull from the incidents table, I was using a temp table so I could test easily and demonstrate the code. All you will need to do is swap out "From @t A" with "From dbo.Incident A"

- I'm using the master..spt_values to get a series of numbers (0-2048). I use this list of numbers to generate a record for each WeekDay that is overlapped by the Incident Range. There are many ways to get a list of numbers, but these already exist and are handy

- How it works:

1) Find all the days that overlap the range of the Incident
2) Determine Join those days against table of WorkDays/Time
3) Determine range of time the incident includes on each workday
4) Sum it all up
5) display.

If you want to see the progression, run the various subqueries 1 at a time...

quote:
Originally posted by sz1

Hi Corey

I see the times can be changed from the days below to reflect only 8am - 8pm.

Maybe I'm thick but how does the script implement all this? where does it read the records from the Incident table?
Also can you explain why it uses the master db?

Thanks

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 17:00:00.000' --Monday 8a-5p
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5p
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 12:00:00.000' --Wednesday 8a-12p,
Insert Into @workDays Select 4, '4/13/2011 18:00:00.000', '4/13/2011 22:00:00.000' -- 6p-10p
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5p
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12p



Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 10:27:34
Thanks for breaking down...

"From @t A" with "From dbo.Incident A"

whats the "A" for?

Can weekends be easily put into this?

1) Find all the days that overlap the range of the Incident
2) Determine Join those days against table of WorkDays/Time ---to use to check actual records in incident table later on...
3) Determine range of time the incident includes on each workday --ok so do a check against the incident table with values set here...

4) Sum it all up
5) display.
You are helping honest...

Is that a boxer you have there, we have one too :)

Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 10:30:25
"A" is an alias. Just means we can reference the table by A.ColumnName instead of Incident.ColumnName.

"Can weekends be easily put into this?"
- What do you mean by this?

quote:
Originally posted by sz1

Thanks for breaking down...

"From @t A" with "From dbo.Incident A"

whats the "A" for?

Can weekends be easily put into this?

1) Find all the days that overlap the range of the Incident
2) Determine Join those days against table of WorkDays/Time ---to use to check actual records in incident table later on...
3) Determine range of time the incident includes on each workday --ok so do a check against the incident table with values set here...

4) Sum it all up
5) display.
You are helping honest...

Is that a boxer you have there, we have one too :)





Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 10:41:22
Ok so we need to reference it in this way rather than just "from dbo.incident" is this because you've mentioned this in the case part...so, dy,0,A.CreatedDateTime), will select the CreatedDateTime column hence it needs stating "FROM dbo.Incident A"

Can we exclude weekends...Sat and Sun?
It seems to be weekdays only?

Also will this create/store the output data to the "t" table, so I can read from as we're not creating a view or SP. Or can I just add a Create SP or View at the top of the statement to return the data.

Thanks
s
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 11:16:24
quote:
Originally posted by sz1

Ok so we need to reference it in this way rather than just "from dbo.incident" is this because you've mentioned this in the case part...so, dy,0,A.CreatedDateTime), will select the CreatedDateTime column hence it needs stating "FROM dbo.Incident A" - Yes.

Can we exclude weekends...Sat and Sun? - Do the math, weekends are exlcuded...
It seems to be weekdays only?

Also will this create/store the output data to the "t" table, so I can read from as we're not creating a view or SP. Or can I just add a Create SP or View at the top of the statement to return the data.
- This does not store any data, and you don't even need the @t stuff, that was just sample data so I could test.

Thanks
s



P.S. - I updated my earlier post with a couple comments...

P.P.S - Yes, I have a boxer... just not that one anymore.

Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 11:41:26
Agh nice one, that makes it easier...I'll put it together as a SP...and test.

Yep it will break us when we lose ours ...she's ace...

Again thanks for all your help...

If any problems will post...

Thanks
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 12:03:52
Bet your fed up with me...whats the b.n and C.dw for on the dayweek part. Also after that there is a Z then A...

Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 12:18:18
B, C, Z, A... all aliases. You have to name subQueries. I generally switch back and forth between a,b,c,... and z,y,x,... at each level. I'm too lazy (and too OCD) to think of descriptive names.

Basically, each level would be a new From Clause:

From --Level 1 (a,b,c...)
From --Level 2 (z,y,x...)
From -- Level 3 (a,b,c...)


B.n is the number value from master..spt_values (using it to add n days to the Incident CreatedDate)
C.dw is the day of the week that is configured. (Using it to match up to the day of the week of the calculated dates 'CreatedDate + n days')

Query breaks down like this:




--Whole Thing:
Select blah, blah
From QueryOuter A
...
Group By blah, blah


--QueryOuter:
Select blah, blah
From QueryInner Z
...

--QueryInner:
Select blah, blah
From Incidents A
Inner Join QueryNumbers B
...

--QueryNumbers:
Select blah
From master..spt_values
...


--All together now:

Select blah, blah
From
(
--QueryOuter
Select blah, blah
From
(
--QueryInner
Select blah, blah
From Incidents A
Inner Join
(
--QueryNumbers
Select blah
From master..spt_values
...
) B
...
) Z
...
) A
...
Group By blah, blah


Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-11 : 12:27:36
Nice and thanks...think this is one of the best ways to learn...think I need to redo a transact course though...

Also, teh times must fall into whats set here, if not will not be calculated, this includes weekends because there is no line for this...

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 20:00:00.000' --Monday 8a-5p
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8a-5p
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 20:00:00.000' --Wednesday 8a-12p,
Insert Into @workDays Select 4, '4/13/2011 18:00:00.000', '4/13/2011 20:00:00.000' -- 6p-10p
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8a-5p
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8a-12p
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-12 : 10:14:38
quote:
Originally posted by sz1

Nice and thanks...think this is one of the best ways to learn...think I need to redo a transact course though...

Also, teh times must fall into whats set here, if not will not be calculated, this includes weekends because there is no line for this...


Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 20:00:00.000' --Monday 8a-8p
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8a-8p
--Careful with these records though... if the ranges overlap, the total time will be wrong
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 20:00:00.000' --Wednesday 8a-8p,
Insert Into @workDays Select 4, '4/13/2011 18:00:00.000', '4/13/2011 20:00:00.000' --Wednesday 6p-10p

Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8a-8p
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8a-8p





Corey

I Has Returned!!
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-04-12 : 10:31:05
Yeah had not updadted that yet, this should do it:

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 20:00:00.000' --Monday 8am-8pm
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8am-8pm
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 20:00:00.000' --Wednesday 8am-8pm
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8am-8pm
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8am-8pm
---weekends will be excluded here also...only the times set will be calculated, Am I correct in saying that?
If later I wanted to include weekends I could just add 2lines to the above, select 1, select7...
Thanks
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-12 : 11:00:20
quote:
Originally posted by sz1

Yeah had not updadted that yet, this should do it:

Insert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 20:00:00.000' --Monday 8am-8pm
Insert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8am-8pm
Insert Into @workDays Select 4, '4/13/2011 08:00:00.000', '4/13/2011 20:00:00.000' --Wednesday 8am-8pm
Insert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8am-8pm
Insert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8am-8pm
---weekends will be excluded here also...only the times set will be calculated, Am I correct in saying that?yes.
If later I wanted to include weekends I could just add 2lines to the above, select 1, select7... yes. 1 would be Sunday (atleast for me) and 7 would be Saturday.

Thanks



Corey

I Has Returned!!
Go to Top of Page
    Next Page

- Advertisement -