SQL Server Performance

how 2 get consective /non-consective dates?

Discussion in 'General Developer Questions' started by majidbhutta, Mar 3, 2006.

  1. majidbhutta New Member

    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
  2. Tahsin New Member

    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

Share This Page