SQL server 2005 group by with same column but different date and time?

grouping, sql, sql-server, sql-server-2005

Solution

select U.[DATE], U.PRODCODE
from
  (
    select min(T.[DATE]) as StartDate,
           max(T.[DATE]) as EndDate,
           T.PRODCODE
    from 
      (
        select [DATE],
               PRODCODE,
               row_number() over(order by [DATE]) as rn1,
               row_number() over(order by PRODCODE, [DATE]) as rn2
        from YourTable
      ) T
    group by T.PRODCODE, T.rn2-T.rn1
  ) T
unpivot
  (
    [DATE] for D in (StartDate, EndDate)
  ) U
order by U.[DATE]

SE-Data

Problem

everyone ...currently i have a table as below .. ``` DATE BATCHNO PRODCODE ---------------------------------------------- 31/12/2009 23.53 10859 2003P 01/01/2010 00.04 10860 2003P 01/01/2010 00.06 10861 2003P 01/01/2010 00.13 10862 2003P 01/01/2010 00.30 10863 1259 01/01/2010 03.02 10864 639B 01/01/2010 03.13 10865 639B 01/01/2010 03.20 10866 639B 01/01/2010 04.13 10867 2003P 01/01/2010 04.20 10868 2003P 01/01/2010 04.30 10863 2003P ``` above is the example of the table i have now.. the DATE is generated whenever there are new BATCHNO and the BATCHNO is increase by 1 .. the PRODCODE is the code of the product where machine manufacture the product and let say product 2003P finished manufactured, it will automatically go for another product such as 1259... i would like to compute those data into the below desired result: ``` DATE PRODCODE ---------------------------------------------- 31/12/2009 23.53 2003P 01/01/2010 00.13 2003P 01/01/2010 00.30 1259 01/01/2010 00.30 1259 01/01/2010 03.02 639B 01/01/2010 03.20 639B 01/01/2010 04.13 2003P 01/01/2010 04.30 2003P ``` meaning that 31/12/2009 23.53 is the start time for the product 2003P and 01/01/2012 00.13 is the stop time for product 2003P.. and for product 1259 is special because 01/01/2010 00.30 only produce 1 product before move to other product..basically i cant use group by because it will group by all the product which are the same code .. the problem is that detect the start time and stop time for the particular product code. . how can this be accomplished? and this is for SQL SERVER 2005 ... thanks everyone ..

Original source