Don't know where to start

SQL Apprentice Question
I'm still a novice in Integration Services, and i need to calculate the
time that our tickets are in a Pending Status. The problem that i'm
having is that the Pending Start time, and the Pending End time, are
not in the same record. The table that need to query is like the
following

Ticket__ DateTime Summary
cs00001 1/1/2006 13:00 Ticket entered Pending status
cs00002 1/1/2006 13:03 Some other ticket activity
cs00001 1/1/2006 13:20 Some other activity
cs00001 1/1/2006 13:30 Ticket exited Pending status


I think i need to do this as a SSIS job as we already have one that
imports all the data from our oracle database to SQL (for reporting),
and i would like to run this as a 2nd step in that existing job - but i
just have no idea where to start.


Any help would be appreciated.


Celko Answers
>> I need to calculate the time that our tickets are in a Pending Status. The problem that I'm having is that the Pending Start time, and the Pending End time, are not in the same record [sic] <<


Your error is bedrock, fundamental and deadly. Rows are not records.
Nothing alike. NOTHING. NADA!! A record is a unit of contigous
physical storage in a file system. A row (no matter how it is
physically implemented; which does not have to be contigous storage) is
a single **fact** about a member of a set.

What is the fact in your example; the whole fact and nothing but the
fact?? The fact is that "ticket # XXXX was pending from start time
tttt until finish time ffff" with the constraints that (start time <
finish time), etc. from the DDL (aka "Universe of Discourse").


You mimicked a paper sign-in-sign-out sheet and not a whole fact. Let
(finish-time IS NULL) mean that the process is on-going and not yet
complete.



>> The table that need to query is like the following <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

if you had proper netiquette, you might have posted (with the ISO-8601
temporal formats, please!!)


CREATE TABLE TicketHistory
(ticket_nbr CHAR (7) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME, -- null is current
status_code CHAR(3) DEFAULT 'new' NOT NULL
CHECK (status_code IN (..) ),
PRIMARY KEY ()ticket_nbr, start_date));