January 11, 2011 at 11:26 am
Hi all,
i have a table with Id, StartTime, NoOfHours and FinishTime
and another table with breakTimings.
now i have to find the FinishingTime which is nothing but StartTime plus NoOfHours. But if the finishing time clashes with the break timings then i have to split the records
following is the table structure
ID StartTime ProcessTime FinishTime
19 01/11/2011 12:30:00 02 :30
18 01/11/2011 16:40:00 01:20
BreakID BreakName Break StartTime Break EndTime
3 B1 13:00 14:00
4 B2 17:00 17:15
expected result should be
ID StartTime ProcessTime FinishTime
19 01/11/2011 12:30:00 00:30 01/11/2011 13:00:00
19 01/11/2011 14:00:00 02 :00 01/11/2011 16:00:00
18 01/11/2011 16:40:00 00:20 01/11/2011 17:00:00
18 01/11/2011 17:15:00 01:00 01/11/2011 18:15:00
without using cursors, by a sql query is it possible to do it?
DECLARE @Tbl TABLE (
id INT,
StartTime DATETIME,
NoOfHoursVARCHAR(5),
FinishingTimeDATETIME
)
INSERT INTO @Tbl
SELECT 19,'01/11/2011 12:30:00 ','02:30',''
UNION ALL
SELECT 18,'01/11/2011 16:40:00 ','01:20',''
SELECT * FROM @Tbl
Thanks,
Ami
January 11, 2011 at 2:46 pm
Does this work for you?
DECLARE @Tbl TABLE (
id INT,
StartTime DATETIME,
NoOfHours VARCHAR(5),
FinishingTime DATETIME
)
INSERT INTO @Tbl
SELECT 19,'01/11/2011 12:30:00 ','02:30',''
UNION ALL
SELECT 18,'01/11/2011 16:40:00 ','01:20',''
UNION ALL
SELECT 17, '01/01/2011 14:30:00', '02:00', ''
UNION ALL
SELECT 16, '01/01/2011 11:15:00', '04:00', ''
;WITH Breaks(BreakID, BreakName, BreakStartTime, BreakEndTime) AS
(
-- the breaks weren't in a table, so provide them here.
SELECT 3, 'B1', '13:00', '14:00' UNION ALL
SELECT 4, 'B2', '17:00', '17:15'
),Breaks2 AS
(
-- get the number of minutes for each break.
SELECT BreakID, BreakName, BreakStartTime, BreakEndTime,
BreakMinutes = DateDiff(minute, BreakStartTime, BreakEndTime)
FROM Breaks
), CTE AS
(
-- get the number of minutes that the number of hours is for.
SELECT id, StartTime, NoOfHours, FinishingTime,
NoOfMinutes = (CONVERT(int, LEFT(NoOfHours, CharIndex(':', NoOfHours)-1)) * 60) +
CONVERT(int, SubString(NoOfHours, CharIndex(':', NoOfHours)+1, 2))
FROM @Tbl
), CTE1 AS
(
-- (left) join the data to the breaks.
SELECT *
FROM CTE
LEFT JOIN Breaks2
ON DateAdd(day, DateDiff(day, 0, CTE.StartTime), 0) + Breaks2.BreakStartTime BETWEEN CTE.StartTime and DATEADD(minute, CTE.NoOfMinutes, CTE.StartTime)
), CTE2 AS
(
-- first, get the data where a break is not in the hours worked.
SELECT id,
StartTime,
ProcessTime = NoOfHours,
FinishTime = DATEADD(Minute, NoOfMinutes, StartTime),
BreakMinutes,
NoOfMinutes
FROM CTE1
WHERE BreakID IS NULL
UNION ALL
-- next, get the time worked up until that break
SELECT id,
StartTime,
NULL,
DateAdd(day, DateDiff(day, 0, StartTime), 0) + BreakStartTime,
BreakMinutes,
NoOfMinutes
FROM CTE1
WHERE BreakID IS NOT NULL
UNION ALL
-- finally, get the time worked from the end of the break for the remainder of their time.
SELECT id,
DateAdd(day, DateDiff(day, 0, StartTime), 0) + BreakEndTime,
NULL,
FinishingTime = DateAdd(minute, NoOfMinutes-DATEDIFF(minute, StartTime, (DateAdd(day, DateDiff(day, 0, StartTime), 0) + BreakStartTime)),
(DateAdd(day, DateDiff(day, 0, StartTime), 0) + BreakEndTime))
,BreakMinutes,
NoOfMinutes
FROM CTE1
WHERE BreakID IS NOT NULL
), CTE3 AS
(
-- get the number of minutes for this period
SELECT id,
StartTime,
ProcessTime,
FinishTime,
PeriodMinutes = DATEDIFF(minute, StartTime, FinishTime)
FROM CTE2
)
-- finally, return the results, building the process time.
SELECT id,
StartTime,
ProcessTime = IsNull(ProcessTime, RIGHT('00' + CONVERT(varchar(2), PeriodMinutes/60),2) + ':' +
RIGHT('00' + CONVERT(varchar(2), PeriodMinutes%60),2)),
FinishTime
FROM CTE3;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 12, 2011 at 5:41 am
Hi WayneS,
Thanks a lot, it is working
-Ami
January 14, 2011 at 2:39 am
Hi WayneS,
one more doubt,
if i have multiple break timings with in one task then it is clashing for example consider this
INSERT INTO @Tbl
SELECT 19,'01/11/2011 08:00:00 ','06:30',''
UNION ALL
SELECT 18,'01/11/2011 08:40:00 ','08:20',''
;WITH Breaks(BreakID, BreakName, BreakStartTime, BreakEndTime) AS
(
-- the breaks weren't in a table, so provide them here.
SELECT 3, 'B1', '10:00', '10:15' UNION ALL
SELECT 4, 'B2', '13:00', '14:00' UNION ALL
SELECT 5, 'B3', '16:00', '16:15' UNION ALL
SELECT 6, 'B4', '17:00', '17:30'
)
here it takes two start timings as 8:00 and not continuing with next break time.
how to resolve this?
Thanks in Advance
Ami
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply