SQL Server Performance Forum – Threads Archive

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

---

]]>