JOIN problem with time involved

JOIN problem with time involved

• keenb

  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....
  
  

  
  

• Craig Laurin

  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.
  

• ThackerRobin

  Well done!
  I hope i would act as you one day.
  

• SebMouren

  Hi,
  
  what is the logic with which you want to create the second table
  
  

• Tyrven

  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;
  

  
  
  

• Steve R

  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;
  
  
  

• guzarva16

  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 Machanic
  SQL Server MVP
  http://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.

  
  

• ravithegreat

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

  
  
• JOIN problem with time involved