sql script puzzle

[HobbyMan]

I have data in a table as this:

NUM State Transition_Date
---- ----- ---------------
1430 0 2001-10-09 10:57:52.000
1430 1 2001-10-13 09:42:52.000

I have more than just this record with states 0 and 1.
What I want to come up with is this: The AVG difference
in hours between State 0 and State 1. I know the 'datediff'
function exists but I am having trouble coming up with
the SQL to get my results.

Lance

 

[tlbroadbent]

This should get you started. Create two queries - one to slect state=0 records and the other to select state=1. Join the queries on NUM. Use Datediff to calculate the difference in hours. You can then use this query to create a view or wrap another query around it to calculate the average.

Select q0.Num, Datediff(h, Date0, Date1) As DiffHrs
(Select NUM, Transition_Date As Date0
From Table_Name
Where State=0) As q0
Inner Join
(Select NUM, Transition_Date As Date1
From Table_Name
Where State=1) As q1
On q0.Num=q1.Num Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[HobbyMan]

Here is my query per your advice:


Select q0.SrNo, Datediff(hour, Date0, Date1) As DiffHrs
(Select SrNo, Transition_Date As Date0
From SrCr_State_History
Where StateId=0
AND Transition_Date BETWEEN '10/07/2001'
AND '10/13/2001') As q0
Inner Join
(Select SrNo, Transition_Date As Date1
From SrCr_State_History
Where StateId=1
AND Transition_Date BETWEEN '10/07/2001'
AND '10/13/2001') As q1
On q0.SrNo=q1.SrNo


But when I try to run this I get these errors:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'As'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'As'.


Sorry, I being a pain.

lance

 

[tlbroadbent]

Whoops! I left off a from clause.

Select q0.SrNo, Datediff(hour, Date0, Date1) As DiffHrs
From (Select SrNo, Transition_Date As Date0
.
.
. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[HobbyMan]

That was exactly what I needed.

THANK YOU VERY MUCH.

I don't think I would have come up with that on my own.
That starts me down the road to more advanced queries.

lance