Create Time Stamps By Rank (1 Viewer)

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!

This is the page I found the initial code:

http://stackoverflow.com/questions/...ta-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.

Vassago,

It's been a while, hope you're doing fine.

Is this still an issue for you?

In your example:


The employee is on AGNOUT from 1100 thru 1500, BUT still tries to charge other AUX_1 from 1300 thru 1315.
That is an invalid timecard.

My suggestion would be to write a traverse the data with a cursor, building a new table with a validation
flag. If the

For each date (for each employee) iterate through the items charged. If the starting time for the new
item doesn't match the ending time for the current item, the timecard is invalid for that day.

There has to be a later action to correct the invalid day's tickets.

If you want I can put together a brief sample of the cursor code.

hth,
Wayne

WayneRyan said:

Vassago,

It's been a while, hope you're doing fine.

Is this still an issue for you?

In your example:

Code:Copy to clipboard

Select 'John', 26, '20120208', [B][SIZE="3"]'2012-02-08 11:00:00.000', '2012-02-08 15:00:00.000', 'AGNOUT'[/SIZE][/B] 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',[B][SIZE="3"] '2012-02-08 13:00:00.000', '2012-02-08 13:15:00.000', 'AUX_1' [/SIZE][/B]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'

The employee is on AGNOUT from 1100 thru 1500, BUT still tries to charge other AUX_1 from 1300 thru 1315.
That is an invalid timecard.

My suggestion would be to write a traverse the data with a cursor, building a new table with a validation
flag. If the

For each date (for each employee) iterate through the items charged. If the starting time for the new
item doesn't match the ending time for the current item, the timecard is invalid for that day.

There has to be a later action to correct the invalid day's tickets.

If you want I can put together a brief sample of the cursor code.

hth,
Wayne

Click to expand...

It has been a while, good to see you're still around.

Yes, it's still an issue. I realize the overlap, which is why I need to build something that will look at the rank and decide priority accordingly. This is a table build by a third party app that takes all segments and places them into the table, regardless of overlap. I need to break it out so that I can see, based on lowest ranked number, the full picture in a flowed table. What that means is, I don't want to see that AUX_1 from 1300 to 1315 because the rank of that segment is higher numbered, i.e., less important, than the rank of the AGNOUT that takes place at the same time. Same with the AUX_WK that takes place at the same time as the AGN_OUT, it would be invisible, giving me the end result of

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

for that day.

Does that make sense? The query I posted ALMOST does this, but for some reason, it's not grabbing the 1500 for the end time, and still capturing the 1300 from the AUX_1 I don't want to see. Any suggestions?

Vass,

I'd still suggest that you add a flag for each record (DataValid).


I think that you might be able to join the table to itself and filter out the overlapping
records, but a cursor makes it easier to add logic for "maybe put a check in here for ..."

Then just ignore the Flagged records and view the data in Date/Time sequence.
But, I think you still have to address the "Flagged" records.

hth,
Wayne

Yeah, it still won't quite do what I need. I understand your logic, but unfortunately, I can't completely ignore these segments because sometimes the times won't completely overlap. For example, the AUX_2 actually starts at 14:55, but the first five minutes are beaten by the AGNOUT that ends at 15:00 in the example above.

I've almost gotten it down. I have this now:


Which does the proper ranking, but it breaks out all of the timestamps still. With this, I'm left showing the AUX_1 timestamps, but with the SegName of AGNOUT. So close!