How do I go through a table with a timestamp column and determine the accumulated time of certain records

Question

How do I go through a table with a timestamp column and determine the accumulated time of certain records

Bullfrog

SSC Eights!

Points: 895
More actions
February 11, 2006 at 9:03 am

#189234

We have a table with a timestamp column and a reason code (RC) column that monitors a cash register. 'GOOD' means the user is tending properly, '????' means we are in a state of unknown and awaiting the user to enter a reason code. There are multiple reason codes, RC01, RC02, RC03, etc... that the user types in to track their difficulties.
8:23:00 GOOD
8:23:00 GOOD
8:23:00 GOOD
8:23:00 ????
8:23:00 ????
8:23:00 ????
8:23:00 RC02
8:23:00 RC02
8:23:00 RC02
8:23:00 GOOD
8:23:00 GOOD
8:23:00 ????
8:23:00 ????
8:23:00 RC03
8:23:00 RC03
8:23:00 RC03
8:23:00 RC04
8:23:00 RC04
8:23:00 GOOD
I need to re-calculate the 8:23:03, 8:23:04, and 8:23:05 as 'RC02' because that was the next known reason code at 8:23:06 (they went on break)
I need to re-calculate the 8:23:11, 8:23:12 as 'RC03' because that was the next known reason code at 8:23:13 (they went to get coins)
In this, they immediately had a paper jam (RC04) right after they got coins.
Then, I want to tally the results
GOOD 00:00:07
RC02 00:00:06
RC03 00:00:05
RC04 00:00:02

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

Bullfrog,

I'm thinking that your times are a bit screwed up in your example data... they're all 8:23:00. But, I'm thinking we get the idea...

The hard part is, I can figure out what you want for the "tally" of the results... what are the times based on?

Other than that, you're description of the problem is great.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeffB Hall of Fame Points: 3541 More actions · Answer 2

If by tally, you mean the total number of seconds that each type of event takes then the following should work:

declare @events table (EventTime datetime, Event char(4))

insert @Events values ('8:23:00','GOOD')

insert @Events values ('8:23:01','GOOD')

insert @Events values ('8:23:02','GOOD')

insert @Events values ('8:23:03','????')

insert @Events values ('8:23:04','????')

insert @Events values ('8:23:05','????')

insert @Events values ('8:23:06','RC02')

insert @Events values ('8:23:07','RC02')

insert @Events values ('8:23:08','RC02')

insert @Events values ('8:23:09','GOOD')

insert @Events values ('8:23:10','GOOD')

insert @Events values ('8:23:11','????')

insert @Events values ('8:23:12','????')

insert @Events values ('8:23:13','RC03')

insert @Events values ('8:23:14','RC03')

insert @Events values ('8:23:15','RC03')

insert @Events values ('8:23:16','RC04')

insert @Events values ('8:23:17','RC04')

insert @Events values ('8:23:18','GOOD')

--USE THIS BLOCK OF CODE IF YOU WANT TO UPDATE THE EVENTS TABLE A

WHILE exists (SELECT * FROM @events ce inner join @events ne on ne.EventTime > ce.EventTime and ce.Event = '????' and ne.Event <> '????')

BEGIN

UPDATE ce

SET ce.Event = ne.Event

FROM @Events ce

INNER JOIN @Events ne

on ne.EventTime = dateadd(ss, 1, ce.EventTime)

WHERE ce.Event = '????' and ne.Event <> '????'

END

select ce.Event, sum(datediff(ss, ce.EventTime, ne.EventTime)) duration

from @events ce

inner join @events NE

on ne.EventTime = dateadd(ss, 1, ce.EventTime)

group by ce.Event

--END BLOCK

--USE THIS BLOCK OF CODE IF YOU NEED TO KEEP THE ???? RECORDS

select

isnull(ae.Event, ce.Event) Event

,sum(datediff(ss, ce.EventTime, ne.EventTime)) Duration

from @Events ce

left outer join (

--GET THE EVENT DECRIPTION FOR THE NEXT KNOWN EVENT

select ae1.EventTime, e1.Event

from @Events e1

inner join (

--GET THE SET OF EVENTS THAT ARE ???? AND THE TIME OF THE

--NEXT KNOWN EVENT

select ce2.EventTime, min(ne2.EventTime) ActualEventTime

from @Events ce2

inner join @Events ne2

on ne2.EventTime > ce2.EventTime and ce2.Event <> ne2.Event

where ce2.Event = '????'

group by ce2.EventTime

) ae1

on e1.EventTime = ae1.ActualEventTime

) ae

on ce.EventTime = ae.EventTime

inner join @events ne

on ne.EventTime = dateadd(ss, 1, ce.EventTime)

group by isnull(ae.Event, ce.Event)

--END BLOCK

Bullfrog SSC Eights! Points: 895 More actions · Answer 3

Thank you both for your replies. I did mess mess up the timestamp but you both figured out what I meant. I'm learning tons more about SQL than I thought it was capable of. Thank you!