August 13, 2013 at 10:21 am
Table looks like this....
employeeId, Activity, StartDateTime, EndDateTime
12345 Break 2013-08-06 09:00:00 2013-08-06 09:10:00
12345 Lunch 2013-08-06 13:00:00 2013-08-06 14:00:00
what i need to do is add a record in between that will take the last endDateTime from the first record and the first record from the startDateTime.. should look like this below
12345 Break 2013-08-06 09:00:00 2013-08-06 09:10:00
12345 Open 2013-08-06 09:10:00 2013-08-06 13:00:00
12345 Lunch 2013-08-06 13:00:00 2013-08-06 14:00:00
Any Help would be greatly appreciated
August 13, 2013 at 11:03 am
Let me add the code and the resultset so it will be easier and quicker to possibly help me out.
DECLARE @testing AS TABLE (
AgentID INT,
ExcepCodeDetailName VARCHAR(10),
Detail_Start_Time DATETIME,
Detail_End_Time DATETIME
)
INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Break', '2013-08-06 09:00:00', '2013-08-06 09:10:00')
INSERT INTO @testing (AgentID, ExcepCodeDetailName, Detail_Start_Time, Detail_End_Time) VALUES (12345, 'Lunch', '2013-08-06 13:00:00', '2013-08-06 14:00:00')
SELECT * FROM @testing
-------------------Results
12345Break2013-08-06 09:00:00.0002013-08-06 09:10:00.000
12345Lunch2013-08-06 13:00:00.0002013-08-06 14:00:00.000
-----------Results needed
12345Break2013-08-06 09:00:00.0002013-08-06 09:10:00.000
12345 Open 2013-08-06 09:10:00.0002013-08-06 13:00:00.000
12345Lunch2013-08-06 13:00:00.0002013-08-06 14:00:00.000
August 13, 2013 at 11:25 am
Given that you are on SQL 2012, here is one solution that uses the new Windowing Functions:
;WITH a AS (
SELECT agentid, 'Open' AS ExceptCodeDetailName,
Detail_End_Time AS Detail_Start_Time,
LEAD(Detail_Start_Time,1,NULL) OVER (order by AgentID, Detail_Start_Time) AS Detail_End_Time
FROM @testing)
SELECT *
FROM a
WHERE Detail_End_Time IS NOT NULL
I note that you may have to put in Partitioning when you have more agentIDs in your sample data. There could be other conditions that cannot be known at this time with a sample size of 2. Test rigorously!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 13, 2013 at 11:32 am
Kevin,
Thank you for the reply and help on this matter. Now for the funny part, the db is on 2008R2 and i posted in the wrong section. Sorry about that. Any thoughts if it was 2008R2? I will repost in that forum as well 🙁
August 13, 2013 at 4:01 pm
Something like this, although again you have provided very limited data.
;WITH a AS (
SELECT agentid, 'Open' AS ExceptCodeDetailName, Detail_Start_Time,
Detail_End_Time, ROW_NUMBER() OVER (order by AgentID, Detail_End_Time, Detail_Start_Time) AS rownum
FROM @testing)
SELECT a1.agentid, 'Open', a1.Detail_End_Time AS Detail_Start_Time, a2.Detail_Start_Time AS Detail_End_Time
FROM a a1
INNER JOIN a a2
ON a1.agentid = a2.agentid
AND a1.rownum = a2.rownum -1
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 13, 2013 at 6:47 pm
My suggestion is to treat your base records as islands and calculate from them the intervening gaps:
DECLARE @EmpData TABLE
(
employeeId INT
,Activity VARCHAR(10)
,StartDateTime DATETIME
,EndDateTime DATETIME
);
INSERT INTO @EmpData
SELECT 12345,'Break','2013-08-06 09:00:00','2013-08-06 09:10:00'
UNION ALL SELECT 12345,'Lunch','2013-08-06 13:00:00','2013-08-06 14:00:00'
SELECT employeeID, Activity='Open'
,StartDateTime=MIN([DateTime]), EndDateTime=MAX([DateTime])
FROM (
SELECT employeeID, Activity='Open', [DateTime]
,rn=ROW_NUMBER() OVER (PARTITION BY employeeId ORDER BY StartDateTime)/2
FROM @EmpData
CROSS APPLY (
VALUES(StartDateTime),(EndDateTime)) b([DateTime])
) a
GROUP BY employeeID, rn
HAVING COUNT(*) = 2
UNION ALL
SELECT employeeID, Activity, StartDateTime, EndDateTime
FROM @EmpData
ORDER BY StartDateTime;
This technique is explained in this article: The SQL of Gaps and Islands in Sequences[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply