SQL Server 2005 with the same column but with a different date and time?

Question

everyone ... I currently have a table as shown 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 

the above is an example of the table in which I am now. DATE is generated whenever new BATCHNOs appear, and BATCHNO is incremented by 1. PRODCODE is the product code in which the machine produces the product, and let it be said that the 2003P product is finished, it will automatically go to another product, such as 1259 ...

I would like to calculate this data into the following 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 

means that 12/31/2009 23.53 is the start time for the product 2003P and 01/01/2012 00.13 is the stop time for the product 2003P .., and for product 1259 it is special, since 01/01/2010 00.30 only produces product 1, before moving on to another product. Basically, I can’t use a group because it will be grouped by the whole product that is the same code. The problem is that they detect start time and stop time for a specific product code. How can this be done? and this is for SQL SERVER 2005 ...

thanks everyone ..

Answer 1

 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

Answer 2

Idea: add a column to get the ID of the next DIFFERENT starting time, and delete and request to fill it out. Then create another column, but this time save the previous DIFFERENT fragment end time. The code for such a task would be a subquery

 update table set prev=(select top 1 batchno from table x where x.prodcode!=table.prodcode and x.date<table.date order by x.date DESC)+1, next=(select top 1 batchno from table x where x.prodcode!=table.prodcode and x.date>table.date order by x.date ASC)-1 

this way you will have borders for each product. Now you just

 select prodcode, prev, next from table group by prodcode, prev, next 

and finally attach the dates from the table in the last query to prev=prodcode and next=prodcode .

Good luck :)