December 9, 2011 at 12:09 am
Hi guys, can you help me with my homework?
Let say I have @startTime and @endTime both datetime variable and
I have a following lookup table for breaktimes:
BreakTimeFrom BreakTimeTo
------------- -----------
11:30 AM____|_12:30 PM
04:30 PM____|_04:45 PM
06:30 PM____|_07:00 PM
11:30 PM____|_12:30 AM
how can I get the consumed time in minutes from @startTime to @endTime minus
the breaktimes covered from @startTime to @endTime? 🙂
"Often speak with code not with word,
A simple solution for a simple question"
December 9, 2011 at 12:15 am
Sure. What all you tried for it? Where are you stuck?
December 9, 2011 at 12:37 am
Im stuck with the condition if the @startTime or @endTime is in between the BreakTimeFrom and BreakTimeTo.
"Often speak with code not with word,
A simple solution for a simple question"
December 9, 2011 at 12:47 am
Please post your query here.
December 9, 2011 at 1:02 am
What is the code that you are using? If we see the approach you are taking, then maybe we can help you adjust it. If we give our approaches (and I'm sure there will be more than one <grin>) then it may not help you with further developments.
December 9, 2011 at 1:27 am
I don't have code yet, that is what I am thinking of, so I it is open to all approaches just to arrive with the solution.
Thanks for your cooperation! 🙂
"Often speak with code not with word,
A simple solution for a simple question"
December 9, 2011 at 1:32 am
mhike2hale (12/9/2011)
I don't have code yet, that is what I am thinking of, so I it is open to all approaches just to arrive with the solution.Thanks for your cooperation! 🙂
Then please rephrase your initial statement... You don’t want help for your homework; you want us to do your homework... Sorry to say, you are knocking a wrong door.
December 9, 2011 at 1:48 am
ok my apology, here is what i've made so far
declare @startTime datetime = '2011-12-09 07:31 AM'
declare @endTime datetime = '2011-12-09 4:31 PM'--540 mins
select datediff(mi, @startTime, @endTime) -
sum
(
case when (BreakTimeFrom > @startTime and BreakTimeTo < @endTime)
then datediff(mi, BreakTimeFrom, BreakTimeTo)
else 0
end
)
from HR_R_BreakTime
but it is giving me wrong result
"Often speak with code not with word,
A simple solution for a simple question"
December 9, 2011 at 2:37 am
A good way is to see how you would answer this manually. For example - what would be the answer if you started at 00:01 and ended at 23:59? Go through the steps you would make in working this out by hand, then code it. Once you have the answer, you can then think of quicker ways.
December 9, 2011 at 4:27 am
Feeling a bit kind.
I've left a deliberate flaw in the design for you to work out.
BEGIN TRAN
CREATE TABLE #breaks (
breakID INT IDENTITY PRIMARY KEY,
breakTimeFrom TIME,
breakTimeTo TIME)
INSERT INTO #breaks
SELECT breakTimeFrom, breakTimeTo
FROM (SELECT '11:30 AM', '12:30 PM'
UNION ALL SELECT '04:30 PM', '04:45 PM'
UNION ALL SELECT '06:30 PM', '07:00 PM'
UNION ALL SELECT '11:30 PM', '12:30 AM') a(breakTimeFrom, breakTimeTo)
DECLARE @startTime DATETIME = '2011-12-09 10:30 AM'
DECLARE @endTime DATETIME = '2011-12-09 12:30 PM'
--This is a tally table
--Please read http://www.sqlservercentral.com/articles/Tally+Table/70735/
-- http://www.sqlservercentral.com/articles/Tally+Table/70738/
-- http://www.sqlservercentral.com/articles/Tally+Table/72993/
--to find out more.
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT 0 AS n UNION ALL
SELECT TOP 9999 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t4 x, t4 y),
--Once we have our tally table created, we want to grab all of the minutes in the day
--from when the start-time variable begins to the end-time
all_minutes AS (SELECT DATEADD(mi,n,DATEADD(dd, DATEDIFF(dd, 0, @startTime), 0)) AS theTime, 1 AS minuteCount
FROM tally
WHERE DATEADD(mi,n,DATEADD(dd, DATEDIFF(dd, 0, @startTime), 0)) >= @startTime AND
DATEADD(mi,n,DATEADD(dd, DATEDIFF(dd, 0, @startTime), 0)) <= @endTime),
--Now we do the same with the break-times
break_minutes AS (SELECT CONVERT(TIME,DATEADD(mi,n,CONVERT(DATETIME,0))) AS breaks
FROM tally
CROSS APPLY #breaks
WHERE CONVERT(TIME,DATEADD(mi,n,CONVERT(DATETIME,0))) >= breakTimeFrom AND
CONVERT(TIME,DATEADD(mi,n,CONVERT(DATETIME,0))) <= breakTimeTo)
--We join our two CTEs on the TIME value. This has a major draw-back when the
--startTime and endTime roll over a day. I've left this flaw in deliberately, the
--solution I've provided can be adapted to solve the issue
SELECT SUM(minuteCount) AS minutesWorked
FROM all_minutes
LEFT OUTER JOIN break_minutes ON CONVERT(TIME,theTime) = breaks
WHERE breaks IS NULL
ROLLBACK
December 13, 2011 at 2:54 am
Hi Cadavre!
Thank you very much for your response I just read it by now and tried to run all the possible scenarios specially where I was stuck, like if the @startTime or @endTime is in between the the breaktime is should compute perfectly and your script amazingly does it. Hope I could also help you in the future. Thanks!:-)
"Often speak with code not with word,
A simple solution for a simple question"
December 13, 2011 at 4:32 am
mhike2hale (12/13/2011)
Hi Cadavre!Thank you very much for your response I just read it by now and tried to run all the possible scenarios specially where I was stuck, like if the @startTime or @endTime is in between the the breaktime is should compute perfectly and your script amazingly does it. Hope I could also help you in the future. Thanks!:-)
Next time, remember to post DDL and sample data (read this article for how[/url]).
Also make sure you show what you've tried.
Did you manage to fix the deliberate flaw?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply