April 6, 2009 at 11:26 am
I have some TSQL that creates a view of elapsed time for service time completion/Service Level Agreements.
I have a customer that now wants to add Saturday as a workday. How can I modify this view to include Saturdays?
ALTER VIEW [dbo].[vw_company_sla]
AS
SELECT TOP 100 PERCENT dbo.SVC00200.CALLNBR, dbo.SVC00200.USERDEF1 AS Owner,
dbo.SVC00100.NAME AS Tech,
dbo.SVC00100.TECHSTAT AS TechStatus, dbo.SVC00200.CUSTNMBR,
dbo.SVC00200.SRVSTAT AS Call_Status, dbo.SVC00913.STSDESCR,
CASE WHEN compdte = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'Closed' END AS Status,
CAST(CONVERT(varchar, dbo.SVC00200.ENTDTE, 101)
+ ' ' + CONVERT(varchar, dbo.SVC00200.ENTTME, 108) AS Datetime) AS Entered,
CAST(CONVERT(varchar, dbo.SVC00200.COMPDTE, 101)
+ ' ' + CONVERT(varchar, dbo.SVC00200.COMPTME, 108) AS Datetime) AS Completed,
DATEDIFF(day, dbo.SVC00200.ENTDTE, GETDATE()) AS Days_Active,
CASE WHEN DATEDIFF(day, entdte, compdte) - (2 * DATEDIFF(week, entdte, compdte))
- CASE WHEN DATEPART(weekday, entdte + 1)
= 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, compdte + 1) = 1
THEN 1
ELSE 0 END < 0 THEN 0 ELSE DATEDIFF(day, entdte, compdte)
- (2 * DATEDIFF(week, entdte, compdte)) - CASE WHEN DATEPART(weekday, entdte + 1) = 1
THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday,
compdte + 1) = 1 THEN 1 ELSE 0 END END AS Workdays_to_complete,
CASE WHEN DATEDIFF(week, entdte, compdte)
* 2 + CASE WHEN DATEPART(weekday, entdte) = 1 THEN 1 ELSE 0 END + CASE WHEN
DATEPART(weekday, compdte)
= 7 THEN 1 ELSE 0 END < 0 THEN 0 ELSE DATEDIFF(week, entdte, compdte) * 2 +
CASE WHEN DATEPART(weekday, entdte)
= 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(weekday, compdte) = 7
THEN 1 ELSE 0 END END AS Week_Ends
FROM dbo.SVC00200 INNER JOIN
dbo.SVC00913 ON dbo.SVC00200.SRVSTAT = dbo.SVC00913.SRVSTAT INNER JOIN
dbo.SVC00100 ON dbo.SVC00200.TECHID = dbo.SVC00100.TECHID
WHERE (dbo.SVC00200.SRVSTAT NOT IN ('00c', '90I')) AND (DATEDIFF(day, dbo.SVC00200.ENTDTE, GETDATE()) >= 30)
ORDER BY DATEDIFF(day, dbo.SVC00200.ENTDTE, GETDATE()) DESC
GO
Thanks in advance...
April 6, 2009 at 12:33 pm
Do you take holidays into account at all? If so, you might be better served by creating a calendar or holidays table. Let me know if you want to pursue that.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 12:39 pm
No, no holidays.
Thanks
April 6, 2009 at 1:07 pm
Will anything ever begin or end on a Sunday?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 1:35 pm
Hi
I would also suggest a Calendar table. It's quiet easy with this!
@bob-2: hope it's okay to bring a sample for your suggestion. Feel free to correct me! 🙂
DECLARE @sla TABLE (Id INT, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @sla
SELECT TOP(100) N, '2009-01-01', DATEADD(DAY, N, '2009-01-01')
FROM Tally
SELECT *
FROM @sla s
CROSS APPLY (SELECT COUNT(*) days
FROM Calendar
WHERE DT BETWEEN s.StartDate AND s.EndDate
AND DW != 1) t
Greets
Flo
April 6, 2009 at 1:54 pm
Be my guest Flo. 🙂 By a calendar table, I actually meant a table other than a tally table, which would list weekend days and holidays. They are very useful for "lead time" or "turn time" problems.
You might want to give him a quick CTE for a tally table, in case he doesn't already have one.
Because Jeff's requirements are simple, I was just going to calculate it for him like this:
declare @compdte datetime
,@entdte datetime
set @entDte = '4/6/2009'
set @compDte = '4/11/2009'
-- the final +1 can be deleted if both start and end dates aren't included in the elapsed_days total.
select datediff(dd,@entDte,@compDte)-(datediff(wk,0,@compDte) - datediff(wk,0,@entDte)) + 1
as elapsed_days
Jeff, let us know if you have any questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 2:08 pm
Hi Bob
A CTE, as ordered:
; WITH
t1 (N) AS (SELECT 1 UNION ALL SELECT 2),
t2 (N) AS (SELECT t.N FROM t1, t1 t),
t3 (N) AS (SELECT t.N FROM t2, t2 t),
t4 (N) AS (SELECT t.N FROM t3, t3 t),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY t.N) FROM t4, t4 t)
SELECT *
FROM Tally
Greets
Flo
April 6, 2009 at 2:10 pm
Bob Hovious (4/6/2009)
Be my guest Flo. 🙂 By a calendar table, I actually meant a table other than a tally table, which would list weekend days and holidays. They are very useful for "lead time" or "turn time" problems.
I used a Calendar table, didn't I? Maybe I used it wrong, but I think I did 😛
Because Jeff's requirements are simple, I was just going to calculate it for him like this:
declare @compdte datetime
,@entdte datetime
set @entDte = '4/6/2009'
set @compDte = '4/11/2009'
-- the final +1 can be deleted if both start and end dates aren't included in the elapsed_days total.
select datediff(dd,@entDte,@compDte)-(datediff(wk,0,@compDte) - datediff(wk,0,@entDte)) + 1
as elapsed_days
Sometimes I'm too complicated... Always nice to learn from you!
Greets
Flo
April 6, 2009 at 2:33 pm
Flo: We learn from each other. It's always a pleasure to tackle problems with you.
By the way:
Argh! I'm running around like a chicken with my head cut off today. I saw the word tally and assumed you were doing the query straight from a tally table, never even noticed the word "calendar". Do you want to go ahead and illustrate what your calendar table looks like, or shall I? (They are really useful, Jeff.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 2:46 pm
Bob Hovious (4/6/2009)
Argh.I'm running around like a chicken with my head cut off today. I saw the word tally and assumed you were doing the query straight from the tally table, never even noticed the word "calendar".
😀
I know this days. 😉
Do you want to go ahead and illustrate what your calendar table looks like, or shall I?
Sure! I always like to share mine to get possibly some corrections!
I heard often here and I already googled it:
CREATE TABLE Calendar
(
DT SMALLDATETIME NOT NULL,
IsWeekday BIT NULL,
IsHoliday BIT NULL,
Y SMALLINT NULL,
FY SMALLINT NULL,
Q TINYINT NULL,
M TINYINT NULL,
D TINYINT NULL,
DW TINYINT NULL,
MonthName VARCHAR(9) NULL,
DayName VARCHAR(9) NULL,
W TINYINT NULL,
UtcOffset TINYINT NULL,
CONSTRAINT PK_Gadgets_Calendar
PRIMARY KEY CLUSTERED (DT ASC)
)
Here is the source I got it from:
Since now I don't have all these scalar function of the editor and if I look to my table I'd have never any holidays (didn't need it since now).
If you got any improvements, tell me.
Greets
Flo
April 6, 2009 at 3:01 pm
Not really improvements, just extensions of the concept.
For example, instead of [holiday], someone might have [Holiday_US], [Holiday_Canada], and [Holiday_Mexico]for multinational support. I've also seen calendar tables used to precalculate things like first day of the month, last day of the month.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 3:11 pm
Sure only one holiday column doesn't make much sense, but a holiday column for each country seems also not to be the complete solution.
First reason:
My customer want's to maintain the holidays by it's own. They usually handle this with Excel files and I think a Calendar table would be a bit to un-handy. 😉
Second (main) reason:
I don't know anything about American holidays but in Germany we have many holidays depending the state not the country. I handle this with a origin table (OriginId, CountryId, ZipFrom, ZipTo) and a separate holiday table.
Greets
Flo
April 6, 2009 at 3:18 pm
We host systems for a number of clients, but they all have separate DBs and so we can set up their tables differently. (Or you can give them an interface to flag their own holidays or other days off.) I can see why you would join to a second table, but does that cost you much in the way of performance?
Over here, usually only national holidays are recognized as non-work days. The state of Louisiana might not get anything done during "Mardi Gras", but I can't think of any other exceptions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 3:32 pm
Bob Hovious (4/6/2009)
Misconceived, it's only one international customer with locations almost all over the world and only one IT department which is our contact. They maintain all holidays (and many other master data) for the whole corporation.
(Or you can give them an interface to flag their own holidays or other days off.) I can see why you would join to a second table, but does that cost you much in the way of performance?
I know the possible performance problem but there is no other way... If I should be plain almost nothing I'm doing here would ever be part of my business. I've currently three procedures within my database. The third is two weeks old and affected huge escalations because they all don't like procedures. They wrote a huge OR-Mapper monster which is quiet bad performing but nobody would ever admit. They are living with the dogma that SQL is evil because about ten years ago one guy wrote a really ugly project with procedures... But anyway - it won't be my last company 😉
Over here, usually only national holidays are recognized as non-work days. The state of Louisiana might not get anything done during "Mardi Gras", but I can't think of any other exceptions.
That's quiet simple. Have a look to this German holiday matrix:
http://en.wikipedia.org/wiki/Public_holidays_in_Germany
We like if it's complicated 😛
Greets
Flo
April 7, 2009 at 6:21 am
Bob Hovious (4/6/2009)
Jeff, let us know if you have any questions.
Ummm...yeah...I'd really just like to know what lines I need to change in that view to count Saturdays as workdays....
The rest of the discussion was a great read though :-D.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply