Complex Query - subtract two event times

[Scott Critchley]

Hiya guys,

I am trying to formulate a complex query to get some good info straight from SQL, rather than post-processing in XL.

I have a table as follows:
EventID Integer (key)
ActionID Integer, 1 = event on, 2 = event off
dtStamp Date

I want to get the duration of each Event.

I.e. I need, for each Event:
dtStamp(actionID = 2) - dtStamp(actionID = 1)

I was going to try:

>create view EventOnTime as select EventID, dtStamp as EventOnDtStamp from EventLog where ActionID = 1;

>create view EventOffTime as select EventID, dtStamp as EventOffDtStamp from EventLog where ActionID = 2;

>select eventOnTime.EventID, eventOnTime.EventOnDtStamp - eventOffTime.EventOffDtStamp as EventDuration where eventOnTime.EventID = eventOffTime.EventID;

... but I can't create views with this version of SQL (server version: 4.0.20-standard-log)


Thanks!