Create TimeLine From TimeStamps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create TimeLine From TimeStamps

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2012-03-23 : 13:45:58

This is blowing my mind. I think my last question was overcomplicated. I need to turn this:


EmpName	Rank	DateData	StartTime	EndTime
	SegName
John	26	20120208	2012-02-08 11:00:00.000	2012-02-08 15:00:00.000	AGNOUT
John	63	20120208	2012-02-08 11:00:00.000	2012-02-08 20:00:00.000	AUX_WK
John	95	20120208	2012-02-08 11:00:00.000	2012-02-08 20:00:00.000	AUX_WK
John	32	20120208	2012-02-08 13:00:00.000	2012-02-08 13:15:00.000	AUX_1
John	31	20120208	2012-02-08 14:55:00.000	2012-02-08 15:55:00.000	AUX_2
John	33	20120208	2012-02-08 17:30:00.000	2012-02-08 17:45:00.000	AUX_1

into this:

EmpName	DateData	StartTime	EndTime	SegName	Duration
John	20120208	2012-02-08 11:00:00.000	2012-02-08 15:00:00.000	AGNOUT	7200
John	20120208	2012-02-08 15:00:00.000	2012-02-08 15:55:00.000	AUX_2	3300
John	20120208	2012-02-08 15:55:00.000	2012-02-08 17:30:00.000	AUX_WK	5700
John	20120208	2012-02-08 17:30:00.000	2012-02-08 17:45:00.000	AUX_1	900
John	20120208	2012-02-08 17:45:00.000	2012-02-08 20:00:00.000	AUX_WK	8100

Where the lower rank of the records is counted and the higher ranks are ignored, basically combining the different timestamps scheduled into a flowed timeline of their day. I'm not sure how to accomplish this. Can someone assist?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-23 : 13:53:30

Having a nice trip?

Window Pane?

Blotter?

So you want to make up data?

Note sure what your rules are here

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vassago
Starting Member

33 Posts

Posted - 2012-03-23 : 14:00:20

Sorry if I'm not clear, it's very hard to explain which is why it's so frustrating for me lol

Basically, since the AGNOUT starts at 11:00 and ends at 15:00 and has a rank of 26, it beats out any of the other segments that might be in that timeframe, so AUX_WK during this time and AUX_1 from 13:00 to 13:15 do now show in final results. When this ends at 15:00, the next lowest rank is AUX_2 from 14:55 to 15:55, so the first five minutes are beat by AGNOUT and the rest of the 55 minutes counts as AUX_2. The only thing that has any time between 15:55 and 17:30 is AUX_WK, is it takes it be default. Does this make sense? I need a timeline of a full day for an employee based on their schedule.

Vassago
Starting Member

33 Posts

Posted - 2012-03-23 : 15:02:04

Create Table #TempData (
EmpName varchar(10),
SegRank Int,
DateData Int,
StartTime DateTime,
EndTime DateTime,
SegName varchar(20)
)
Insert Into #TempData
Select 'John', 26, '20120208', '2012-02-08 11:00:00.000', '2012-02-08 15:00:00.000', 'AGNOUT' union all
Select 'John', 63, '20120208', '2012-02-08 11:00:00.000', '2012-02-08 20:00:00.000', 'AUX_WK' union all
Select 'John', 95, '20120208', '2012-02-08 11:00:00.000', '2012-02-08 20:00:00.000', 'AUX_WK' union all
Select 'John', 32, '20120208', '2012-02-08 13:00:00.000', '2012-02-08 13:15:00.000', 'AUX_1' union all
Select 'John', 31, '20120208', '2012-02-08 14:55:00.000', '2012-02-08 15:55:00.000', 'AUX_2' union all
Select 'John', 33, '20120208', '2012-02-08 17:30:00.000', '2012-02-08 17:45:00.000', 'AUX_1'

Sample Table

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-23 : 20:40:54

May be this to start with? But it does not exactly match up with your output data, so may be this is not it, but I thought I will post it anyway since I spent time writing it:

;WITH cte1 AS
(
	SELECT
		a.*,
		b.SegRank AS nextSegRank,
		b.DateData AS nextDateData,
		b.StartTime AS nextStartTime,
		b.EndTime AS nextEndTime,
		ROW_NUMBER() OVER (PARTITION BY a.EmpName ORDER BY a.StartTime,a.SegRank) AS RN
	FROM
		#TempData a
		OUTER apply 
		(
			SELECT TOP 1 * FROM #TempData b
			WHERE b.startTime <= a.EndTime
			AND b.EmpName = a.EmpName
			AND b.endTime > a.EndTime
			ORDER BY b.SegRank
		) b 
),
cte2 AS
(
	SELECT *, DATEDIFF(ss,StartTime,EndTime) AS Duration FROM cte1 WHERE RN = 1
	UNION ALL
	SELECT c1.*,DATEDIFF(ss,c2.EndTime,c1.EndTime) FROM cte1 c1 INNER JOIN cte2 c2 
		ON c1.EmpName = c2.EmpName AND c1.segRank = c2.nextSegRank
)
SELECT EmpName,DateData,StartTime,SegName,Duration FROM cte2;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources