April 7, 2009 at 10:29 am
Don't you know? That isn't a sarcastic question, I promise. I just thought you were asking how to easily calculate elapsed time for a six-day work week.
I'm ASSUMING it would be this, based on the column title "Workdays to Complete."
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,
__________________________________________________
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 7, 2009 at 10:49 am
Just out of curiousity, I reformatted that to make sense of the nested Case statements. They make sure that you never get a date below zero. While that is possible, it should never happen unless you have compDte's which are less than entDtes, which makes no sense to me.
The technique used here seems to be to add a day to entDte and compDte and see if it's a Sunday (datepart = 1), although this could vary depending on whether SET DATEFIRST is being used to change that.
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,
__________________________________________________
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 7, 2009 at 11:06 am
Jeff, here is the code I used to test my results against the existing code, modified to prevent negative results. Let me know how it works for you.
----------------------------- ORIGINAL -------------------------------------------------------
;with test as
(select cast('4/1/2009' as datetime) as entdte, cast('4/16/2009' as datetime) as compDte)
select entDte,compDte,
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
from test
--------------------- REVISED -----------------------------------------------------------------
;with test as
(select cast('4/1/2009' as datetime) as entdte, cast('4/16/2009' as datetime) as compDte)
select entDte,compDte,
case when entDte>compDte then 0
else datediff(dd,entDte,compDte)-(datediff(wk,0,compDte) - datediff(wk,0,entDte))
end as workdays_to_complete
from test
__________________________________________________
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 8, 2009 at 12:45 pm
Thanks Bob,
I'll give a whirl. I really, really appreciate it.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply