how 2 get consective /non-consective dates?

Wel consider the table structure with data is like below

Pk(int auto) DateField
102/12/2006
202/13/2006
302/14/2006
402/15/2006
502/17/2006
602/19/2006
702/20/2006
802/22/2006
902/24/2006
1002/25/2006
1102/26/2006
1202/27/2006
1302/28/2006


wel now my means by patch is that no. of consective occurances of dates now in the above
table there are THREE PATCHES(three occurances of consective dates)
FIRST PATCH
===========
102/12/2006
202/13/2006
302/14/2006
402/15/2006

SECOND PATCH
============
602/19/2006
702/20/2006

THRID PATCH
===========
902/24/2006
1002/25/2006
1102/26/2006
1202/27/2006
1302/28/2006


Now the Required out put is

pATCHIDpATCHstartDatePATCHEndDate
102/12/200602/15/2006
202/19/200602/20/2006
302/24/200602/28/2006




also one more thing that i want also how to get non-consective dates in the above mentioned
table

How to get this
thnx in advance

This script below will output what you need. Change the items in bold to reflect the columns for the table you are selecting from. The first outputted table will give you nonconsecutive dates. The second one will give you the correct patch order.


CREATE TABLE #tblDATE
(PatchID INT,
PatchStartDate DATETIME,
PatchEndDate DATETIME)
go

DECLARE @id1 AS INT
DECLARE @date1 AS DATETIME
DECLARE @date2 AS DATETIME
DECLARE @date3 AS DATETIME
DECLARE @counter AS INT
DECLARE @fetch_date CURSOR

SET @counter = 1
SET @date1 = 0
SET @date2 = 0
SET @date3 = 0
SET @fetch_date = CURSOR SCROLL FOR SELECT id, datefield FROM tblDate ORDER BY datefield

OPEN @fetch_date

FETCH First FROM @fetch_date INTO @id1, @date1

SET @date3 = @date1
SET @date2 = @date1

WHILE (@@fetch_status = 0)
Begin
FETCH Next from @fetch_date Into @id1, @date1
IF @date2 + 1 != @date1
Begin
INSERT INTO #tblDATE VALUES (@counter, @date3, @date2);
SET @counter = @counter + 1
SET @date2 = @date1
SET @date3 = @date1
End
Else
SET @date2 = @date2 + 1

End

Close @fetch_date
Deallocate @fetch_date
GO

SELECT PatchId, PatchStartDate FROM #tblDate
go

DELETE #tblDate WHERE PatchStartDate = PatchEndDate
go

SELECT * FROM #tblDate
go

DROP TABLE #tblDATE
go


-----------------------------------------------------------
For anybody interested in modifying/enhacing the script, here is the
tbl creation/insert script I used to come up with a test scenario

CREATE TABLE tblDate
(id INT,
datefield DATETIME)
go

INSERT INTO TBLDATE VALUES ('1', '02/12/2006');
INSERT INTO TBLDATE VALUES ('2', '02/13/2006');
INSERT INTO TBLDATE VALUES ('3', '02/14/2006');
INSERT INTO TBLDATE VALUES ('4', '02/15/2006');
INSERT INTO TBLDATE VALUES ('5', '02/17/2006');
INSERT INTO TBLDATE VALUES ('6', '02/19/2006');
INSERT INTO TBLDATE VALUES ('7', '02/20/2006');
INSERT INTO TBLDATE VALUES ('8', '02/22/2006');
INSERT INTO TBLDATE VALUES ('9', '02/24/2006');
INSERT INTO TBLDATE VALUES ('10', '02/25/2006');
INSERT INTO TBLDATE VALUES ('11', '02/26/2006');
INSERT INTO TBLDATE VALUES ('12', '02/27/2006');
INSERT INTO TBLDATE VALUES ('13', '02/28/2006');

- Tahsin