"with <tablename> as" syntax help

I'm starting with:

dtTimeIn bStatus

1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1

I'm trying to get to:

dtTimeStart dtTimeFinish bStatus

1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1

Hi,

what is the logic with which you want to create the second table?|||

I'm trying to group the 0's and 1's but still maintain the sequence of 0's and 1's.

Ex: For the first three seconds it's off then, for two seconds it's on, then for....

|||The query below should get what you want. There are some assumptions in the query that you need to modify based on your data/requirements.

with tt_seq
as
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
)
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from tt_seq as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;

|||

Thanks for the help! Took me awhile to figure what you did.

Nicely done.

|||Well done!
I hope i would act as you one day.|||I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything.

Does anyone know of a keyword I can use to find out more about this syntax?

Thx.|||WITH is CTE syntax, new in SQL Server 2005. You can use a derived table in SQL Server 2000 to do the same.

select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
) as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;|||This is a SQL Server 2005 feature. See the following entry in SQL Server 2005 BOL for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Bullfrog@.discussions.microsoft.com> wrote in message news:2a2f39de-068c-4a7b-b71f-38961a9b5b69@.discussions.microsoft.com...I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything. Does anyone know of a keyword I can use to find out more about this syntax?Thx.