Create Time Stamps By Rank - SQL Server Forums

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 Time Stamps By Rank

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2012-03-20 : 15:16:38

Hello all,

I am pulling my hair out as I've gotten this VERY close. I have some data I need to separate out. Here is an example of my original data:

Create Table #TempData (
	EmpName varchar(10),
	SegRank Int,
	DateData Int,
	StartTime DateTime,
	EndTime DateTime,
	SegName varchar(20)
	)
	
Insert Into #TempData
Select 'John', 26, '20120206', '2012-02-06 11:00:00.000', '2012-02-06 20:00:00.000', 'AGNOUT' union all
Select 'John', 63, '20120207', '2012-02-07 11:00:00.000', '2012-02-07 20:00:00.000', 'AUX_WK' union all
Select 'John', 95, '20120207', '2012-02-07 11:00:00.000', '2012-02-07 20:00:00.000', 'AUX_WK' union all
Select 'John', 32, '20120207', '2012-02-07 13:00:00.000', '2012-02-07 13:15:00.000', 'AUX_1' union all
Select 'John', 31, '20120207', '2012-02-07 14:55:00.000', '2012-02-07 15:55:00.000', 'AUX_2' union all
Select 'John', 33, '20120207', '2012-02-07 17:30:00.000', '2012-02-07 17:45:00.000', 'AUX_1' union all
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' union all
Select 'Mary', 63, '20120204', '2012-02-04 08:00:00.000', '2012-02-04 12:00:00.000', 'AUX_WK' union all
Select 'Mary', 92, '20120204', '2012-02-04 08:00:00.000', '2012-02-04 12:00:00.000', 'AUX_WK' union all
Select 'Mary', 32, '20120204', '2012-02-04 10:00:00.000', '2012-02-04 10:15:00.000', 'AUX_1' union all
Select 'Mary', 63, '20120206', '2012-02-06 12:00:00.000', '2012-02-06 21:00:00.000', 'AUX_WK' union all
Select 'Mary', 95, '20120206', '2012-02-06 12:00:00.000', '2012-02-06 21:00:00.000', 'AUX_WK' union all
Select 'Mary', 32, '20120206', '2012-02-06 14:33:00.000', '2012-02-06 14:48:00.000', 'AUX_1' union all
Select 'Mary', 31, '20120206', '2012-02-06 16:05:00.000', '2012-02-06 17:05:00.000', 'AUX_2' union all
Select 'Mary', 33, '20120206', '2012-02-06 19:30:00.000', '2012-02-06 19:45:00.000', 'AUX_1' union all
Select 'Mary', 63, '20120207', '2012-02-07 12:00:00.000', '2012-02-07 21:00:00.000', 'AUX_WK' union all
Select 'Mary', 95, '20120207', '2012-02-07 12:00:00.000', '2012-02-07 21:00:00.000', 'AUX_WK' union all
Select 'Mary', 32, '20120207', '2012-02-07 14:31:00.000', '2012-02-07 14:46:00.000', 'AUX_1' union all
Select 'Mary', 42, '20120207', '2012-02-07 14:46:00.000', '2012-02-07 15:30:00.000', 'AUX_3' union all
Select 'Mary', 31, '20120207', '2012-02-07 15:55:00.000', '2012-02-07 16:55:00.000', 'AUX_2' union all
Select 'Mary', 26, '20120207', '2012-02-07 17:00:00.000', '2012-02-07 21:00:00.000', 'AGNOUT' union all
Select 'Mary', 33, '20120207', '2012-02-07 18:50:00.000', '2012-02-07 19:05:00.000', 'AUX_1' union all
Select 'Mary', 95, '20120208', '2012-02-08 08:00:00.000', '2012-02-08 12:00:00.000', 'AUX_WK' union all
Select 'Mary', 32, '20120208', '2012-02-08 10:00:00.000', '2012-02-08 10:15:00.000', 'AUX_1' union all
Select 'Mary', 63, '20120208', '2012-02-08 12:00:00.000', '2012-02-08 21:00:00.000', 'AUX_WK' union all
Select 'Mary', 31, '20120208', '2012-02-08 15:55:00.000', '2012-02-08 16:55:00.000', 'AUX_2' union all
Select 'Mary', 33, '20120208', '2012-02-08 18:50:00.000', '2012-02-08 19:05:00.000', 'AUX_1'

As you can see, many segments overlap. This is ok, because the rank determines which is most important. I need to turn this sample data into this, eseentially inserting "lower" rank segments into the higher rank longer ones, breaking up the longer segments into individual records using the begin times and end times of the lower ranked segments.:


EmpName	DateData	StartTime	EndTime	SegName	Duration
John	20120206	2012-02-06 11:00:00.000	2012-02-06 20:00:00.000	PTO	32400
John	20120207	2012-02-07 11:00:00.000	2012-02-07 13:00:00.000	AUX_WK	7200
John	20120207	2012-02-07 13:00:00.000	2012-02-07 13:15:00.000	AUX_1	900
John	20120207	2012-02-07 13:15:00.000	2012-02-07 14:55:00.000	AUX_WK	6000
John	20120207	2012-02-07 14:55:00.000	2012-02-07 15:55:00.000	AUX_2	3600
John	20120207	2012-02-07 15:55:00.000	2012-02-07 17:30:00.000	AUX_WK	5700
John	20120207	2012-02-07 17:30:00.000	2012-02-07 17:45:00.000	AUX_1	900
John	20120207	2012-02-07 17:45:00.000	2012-02-07 20:00:00.000	AUX_WK	8100
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
Mary	20120204	2012-02-04 08:00:00.000	2012-02-04 10:00:00.000	AUX_WK	7200
Mary	20120204	2012-02-04 10:00:00.000	2012-02-04 10:15:00.000	AUX_1	900
Mary	20120204	2012-02-04 10:15:00.000	2012-02-04 12:00:00.000	AUX_WK	6300
Mary	20120206	2012-02-06 12:00:00.000	2012-02-06 14:33:00.000	AUX_WK	9180
Mary	20120206	2012-02-06 14:33:00.000	2012-02-06 14:48:00.000	AUX_1	900
Mary	20120206	2012-02-06 14:48:00.000	2012-02-06 16:05:00.000	AUX_WK	4620
Mary	20120206	2012-02-06 16:05:00.000	2012-02-06 17:05:00.000	AUX_2	3600
Mary	20120206	2012-02-06 17:05:00.000	2012-02-06 19:30:00.000	AUX_WK	8700
Mary	20120206	2012-02-06 19:30:00.000	2012-02-06 19:45:00.000	AUX_1	900
Mary	20120206	2012-02-06 19:45:00.000	2012-02-06 21:00:00.000	AUX_WK	4500
Mary	20120207	2012-02-07 12:00:00.000	2012-02-07 14:31:00.000	AUX_WK	9060
Mary	20120207	2012-02-07 14:31:00.000	2012-02-07 14:46:00.000	AUX_1	900
Mary	20120207	2012-02-07 14:46:00.000	2012-02-07 15:30:00.000	AUX_3	2640
Mary	20120207	2012-02-07 15:30:00.000	2012-02-07 15:55:00.000	AUX_WK	1500
Mary	20120207	2012-02-07 15:55:00.000	2012-02-07 16:55:00.000	AUX_2	3600
Mary	20120207	2012-02-07 16:55:00.000	2012-02-07 17:00:00.000	AUX_WK	300
Mary	20120207	2012-02-07 17:00:00.000	*2012-02-07 21:00:00.000	AGNOUT	6600
Mary	20120208	2012-02-08 08:00:00.000	2012-02-08 10:00:00.000	AUX_WK	7200
Mary	20120208	2012-02-08 10:00:00.000	2012-02-08 10:15:00.000	AUX_1	900
Mary	20120208	2012-02-08 10:15:00.000	2012-02-08 12:00:00.000	AUX_WK	6300
Mary	20120208	2012-02-08 12:00:00.000	2012-02-08 15:55:00.000	AUX_WK	14100
Mary	20120208	2012-02-08 15:55:00.000	2012-02-08 16:55:00.000	AUX_2	3600
Mary	20120208	2012-02-08 16:55:00.000	2012-02-08 18:50:00.000	AUX_WK	6900
Mary	20120208	2012-02-08 18:50:00.000	2012-02-08 19:05:00.000	AUX_1	900
Mary	20120208	2012-02-08 19:05:00.000	2012-02-08 21:00:00.000	AUX_WK	6900

But I can't seem to get it to work perfectly. I'm running into issues with some of the overlap on some lower rank segments where it's not taking the correct timestamps, specifically in this sample data where the SegName is AGNOUT, but not for the whole day, I end up missing time. I placed a * above next to the fields that are not working for me. Here is the query I have so far based on searching online and tweaking slightly:


select * 
into #aa
from
(Select
	EmpName, 
	SegRank, 
	DateData,
	StartTime, 
	'b' action, 
	SegName
from #TempData
union all 
select 
	EmpName, 
	SegRank, 
	DateData,
	EndTime, 
	'e' action, 
	SegName
from #TempData) AA



select distinct
	EmpName,
	DateData,
	StartTime,
	EndTime,
	d.SegName,
	datediff(minute, StartTime, EndTime) * 60 as Duration
into
	#FullSchedule
from (
	select 
		EmpName,
		DateData,
		StartTime,   
		(select min(StartTime) from #aa where StartTime > a.StartTime and a.EmpName = EmpName and a.DateData = DateData) EndTime,  
		(select distinct SegName from (
				select 
					rank() OVER (ORDER BY DateData, SegRank) as SegRank, 
					SegName 	
				from 
					#TempData 
				where 
					dateadd(second, 1, a.StartTime) between StartTime and EndTime 
					and EmpName = a.EmpName
					and a.DateData = DateData
					) c 
	where 
		SegRank = 1) SegName 
	from #aa a 
where 
	not exists 
	(select distinct 1 from #TempData where a.StartTime between StartTime and EndTime and a.SegRank > SegRank and a.EmpName = EmpName and a.DateData = DateData) 
	and exists 
	(select distinct 1 from #TempData where a.StartTime between StartTime and EndTime and a.EmpName = EmpName and a.DateData = DateData) 
	) d 
where
	d.SegName is not null  
order by 
	DateData,
	StartTime  
	
	Select * from #FullSchedule order by EmpName, DateData, StartTime

Does anyone have any suggestions on either how to fix my query or something new entirely to get from my original data to my goal data where the timestamps are separated by rank?

Please and thanks in advance before I end up without any hair left!

V

Vassago
Starting Member

33 Posts

Posted - 2012-03-20 : 17:30:15

This is the page I found the initial code:

http://stackoverflow.com/questions/6424560/how-to-turn-time-ranking-data-into-a-timeline-with-t-sql/9795255#9795255

If I can help clarify what I'm looking for, please let me know. I'm really at a loss on this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources