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.
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]ASDeclare @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 ???EndElseIf 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 EndElseIF Datename(dw,@Weekend) = 'Sunday' Begin Set @Weekend = DateAdd(dd, 1, @Weekend)--move to Monday End---the below code all works correctlySelect 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 CreatedDateTimeGO |
|
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 etcthis 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"GOCREATE 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]))GOALTER 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')))GOSET 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 mondayWITH Dates(Date)-- A recursive CTE that produce all dates between 1999 and 2020-12-31AS(SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspxSELECT (Date + 1) AS DateFROM DatesWHEREDate < 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 dateAS(SELECTDate,CASE DATEPART(weekday,Date)WHEN 1 THEN Date + 3WHEN 2 THEN Date + 2WHEN 3 THEN Date + 1WHEN 4 THEN DateWHEN 5 THEN Date - 1WHEN 6 THEN Date - 2WHEN 7 THEN Date - 3END AS ThursdayFROM Dates),Weeks(Week, Thursday)-- Now we produce the weeknumers for the thursdays-- ROW_NUMBER is new to SQL Server 2005AS(SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, ThursdayFROM DatesAndThursdayInWeekWHERE DATEPART(weekday,Date) = 4)INSERT INTO dbo.AuxCalendarSELECTd.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_yearYEAR(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_countryWHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's DayOR (d.Date = dbo.AuxComputus(YEAR(Date))-7) -- Palm SundayOR (d.Date = dbo.AuxComputus(YEAR(Date))-3) -- Maundy ThursdayOR (d.Date = dbo.AuxComputus(YEAR(Date))-2) -- Good FridayOR (d.Date = dbo.AuxComputus(YEAR(Date))) -- Easter SundayOR (d.Date = dbo.AuxComputus(YEAR(Date))+39) -- Ascension DayOR (d.Date = dbo.AuxComputus(YEAR(Date))+49) -- PentecostOR (d.Date = dbo.AuxComputus(YEAR(Date))+50) -- WhitmondayOR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour dayOR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution dayOR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas dayOR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing dayTHEN '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 NorwayWHEN (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 DescriptionFROM DatesAndThursdayInWeek d-- This join is for getting the week into the result setinner join Weeks won d.Thursday = w.ThursdayOPTION(MAXRECURSION 0)GO If you don't have the passion to help people, you have no passion |
|
|
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 |
|
|
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 EffectiveCreatedDateTimeFROM dbo.Incident iSELECT 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 |
|
|
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 |
|
|
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? |
|
|
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 EffectiveCreatedDateTimeFROM dbo.Incident iSELECT 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 EffectiveCreatedDateTimeFROM dbo.Incident iSELECT 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
|
|
|
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-5pInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5pInsert 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-10pInsert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5pInsert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12pSelect 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 ) AGroup By CreatedDateTime, ResolutionDateAndTime, seconds CoreyI Has Returned!! |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2011-04-11 : 10:01:13
|
Hi CoreyI 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?ThanksInsert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 17:00:00.000' --Monday 8a-5pInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5pInsert 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-10pInsert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5pInsert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12p |
|
|
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 Incident2) Determine Join those days against table of WorkDays/Time3) Determine range of time the incident includes on each workday4) Sum it all up5) display.If you want to see the progression, run the various subqueries 1 at a time...quote: Originally posted by sz1 Hi CoreyI 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?ThanksInsert Into @workDays Select 2, '4/11/2011 08:00:00.000', '4/11/2011 17:00:00.000' --Monday 8a-5pInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 17:00:00.000' --Tuesday 8a-5pInsert 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-10pInsert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 17:00:00.000' --Thurdays 8a-5pInsert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 12:00:00.000' --Friday 8a-12p
CoreyI Has Returned!! |
|
|
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 Incident2) 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 up5) display. You are helping honest...Is that a boxer you have there, we have one too :) |
|
|
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 Incident2) 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 up5) display. You are helping honest...Is that a boxer you have there, we have one too :)
CoreyI Has Returned!! |
|
|
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.Thankss |
|
|
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.Thankss
P.S. - I updated my earlier post with a couple comments...P.P.S - Yes, I have a boxer... just not that one anymore. CoreyI Has Returned!! |
|
|
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 |
|
|
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... |
|
|
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, blahFrom QueryOuter A...Group By blah, blah--QueryOuter:Select blah, blahFrom QueryInner Z...--QueryInner:Select blah, blahFrom Incidents AInner Join QueryNumbers B...--QueryNumbers:Select blahFrom master..spt_values...--All together now:Select blah, blahFrom ( --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 CoreyI Has Returned!! |
|
|
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-5pInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8a-5pInsert 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-10pInsert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8a-5pInsert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8a-12p |
|
|
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-8pInsert 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 wrongInsert 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-10pInsert Into @workDays Select 5, '4/14/2011 08:00:00.000', '4/14/2011 20:00:00.000' --Thurdays 8a-8pInsert Into @workDays Select 6, '4/15/2011 08:00:00.000', '4/15/2011 20:00:00.000' --Friday 8a-8p
CoreyI Has Returned!! |
|
|
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-8pmInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8am-8pmInsert 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-8pmInsert 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 |
|
|
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-8pmInsert Into @workDays Select 3, '4/12/2011 08:00:00.000', '4/12/2011 20:00:00.000' --Tuesday 8am-8pmInsert 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-8pmInsert 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
CoreyI Has Returned!! |
|
|
Next Page
|
|
|
|
|