selecting specific records

[oshlonger]

querying a MS SQL 2000 database

I have a table with the following:

Id Num Date Old Val New Val

20 1 12/22 nothing something
20 2 12/23 something nothing
20 3 12/29 nothing something
20 4 01/10 something nothing


I'd like to have a query that returns the number of days that the record had the "something" value.

12/22 - 12/23 = 1 day
12/29 - 01/10 = 12 days
Total of 13 days with "something" value

I'm not really sure how to go about building this query. Does anyone have any suggestions?

 

[sunila7]

Try this

select date,dt, datediff(dd,date,dt) Diff from
(select * , 
(SELECT min(DATE) from TBLNAME t1 where t.id=t1.id and t.date<t1.date and oldVal = 'Something') dt from TBLNAME t
where NewVal= 'something'
) TBL

Sunil