Xrhrft

Hey Microsoft SQL Gurus I need your help, please. This is my
first post, and I have a table called projecthistory,
it’s actually a view so I could take advantage of the Rank()function to get a unique record number. From this view I need to create a temp table that has a uniqueid field which would be calculated bases on these rules. Rule 1 - The next sequential record for the id changes from one project to another or Rule 2 - If the id and project match for the next sequential record , but the days between the end date of the current record and start date of the next record for the is more than 7 days. It would also consolidate the start and end dates appropriately for each group. I believe this would be best done in a stored procedure that can call the data when needed, but I'm fairly new to stored procedures. Here is an example of the table data, as well as the desired output.

Projecthistory

╔════════╦══════╦═════════╦═══════════╦═══════════╗

║ Record ║  Id  ║ Project ║   Start   ║    End    ║

╠════════╬══════╬═════════╬═══════════╬═══════════╣

║      1 ║ 1111 ║ A       ║ 1/1/2016  ║ 1/7/2016  ║

║      2 ║ 1111 ║ A       ║ 1/10/2016 ║ 1/20/2016 ║

║      3 ║ 1111 ║ B       ║ 1/21/2016 ║ 1/25/2016 ║

║      4 ║ 1111 ║ A       ║ 1/26/2016 ║ NULL      ║

║      5 ║ 2222 ║ A       ║ 1/1/2016  ║ 2/1/2016  ║

║      6 ║ 2222 ║ B       ║ 2/2/2016  ║ NULL      ║

║      7 ║ 3333 ║ D       ║ 1/15/2016 ║ 2/1/2016  ║

║      8 ║ 4444 ║ B       ║ 2/10/2016 ║ NULL      ║

║      9 ║ 5555 ║ A       ║ 2/1/2016  ║ 2/5/2016  ║

║     10 ║ 5555 ║ A       ║ 2/20/2016 ║ 2/27/2016 ║

║     11 ║ 5555 ║ A       ║ 2/28/2016 ║ 3/10/2016 ║

║     12 ║ 5555 ║ A       ║ 3/11/2016 ║ NULL      ║

╚════════╩══════╩═════════╩═══════════╩═══════════╝

Desired output

╔════════╦══════╦═════════╦══════════════╦════════════╗

║ unique ║  Id  ║ Project ║ ProjectStart ║ ProjectEnd ║

╠════════╬══════╬═════════╬══════════════╬════════════╣

║ 1111A1 ║ 1111 ║ A       ║ 1/1/2016     ║ 1/20/2016  ║

║ 1111B1 ║ 1111 ║ B       ║ 1/21/2016    ║ 1/25/2016  ║

║ 1111A2 ║ 1111 ║ A       ║ 1/26/2016    ║ NULL       ║

║ 2222A1 ║ 2222 ║ A       ║ 1/1/2016     ║ 2/1/2016   ║

║ 2222B1 ║ 2222 ║ B       ║ 2/2/2016     ║ NULL       ║

║ 3333D1 ║ 3333 ║ D       ║ 1/15/2016    ║ 2/1/2016   ║

║ 4444B1 ║ 4444 ║ B       ║ 2/10/2016    ║ NULL       ║

║ 5555A1 ║ 5555 ║ A       ║ 2/1/2016     ║ 2/5/2016   ║

║ 5555A2 ║ 5555 ║ A       ║ 2/20/2016    ║ Null       ║

╚════════╩══════╩═════════╩══════════════╩════════════╝

Here are some notes about the desired output and some of
more difficult scenario’s in this data.

Take note that records 1-2 became 1111A1, record 3 becomes 1111b1,
record 4 became the second instance of that project for id 1111 so its uniqueid became 1111A2, be careful this doesn’t get lumped
in with records 1&2. Record 9 become 55555A1 and because of the date gap
between records 9&10, record 10-12 became uniqueid
5555A2

I look forward to any solutions, feedback or questions. I’m
truly grateful in advance.

ok I think I got it. I was able to get the results I was looking for using the following code, but it is slow when executing it to get the results. One thing I forget, was the location column, so I was sure to account for this in my code. I did end up breaking the uid and instance into two separate fields, but I know I could have merged them into one. I figured this would give me greater flexibility when pulling data against it in the future. Feel free to improve, I know I would appreciate it. I'm also happy to be posting my first answer. Thanks!

ALTER PROCEDURE [dbo].[ProjectTracking]

    AS

    BEGIN

        -- Insert statements for procedure here

    SET NOCOUNT ON;

    DECLARE @max int

    DECLARE @i int 

    Declare @tinstance int

    Declare @tuid varchar(20)

    DECLARE @puid varchar(20)

    Declare @pinstance int

    DECLARE @pid int

    DECLARE @pproject varchar(10)

    DECLARE @plocation varchar(10)

    DECLARE @pstart datetime

    DECLARE @pend datetime

    DECLARE @cuid varchar(20)

    Declare @crecord int

    DECLARE @cid int

    DECLARE @cproject varchar(10)

    DECLARE @clocation varchar(10)

    DECLARE @cstart datetime

    DECLARE @cend datetime



    select @max = count([Record])+1 from [ProjetHistory] 

    print @max



    -- Insert the first record so there will be a previous record for the first cycle of while                  

    SELECT * INTO #PTEMP

    FROM (SELECT CONVERT(varchar(15),[id])+[project]+rtrim([location]) as Uid,1 as instance, [id],[project],

                        [location],[Start],[End]

      FROM [ProjectHistory]

      WHERE [RECORD] = 1) As x



    select @tuid = [Uid],@tinstance = [instance] from #PTEMP where [instance] = 1

    set @i = 1



    While @i < @max 

    BEGIN --Begin while

        SELECT @puid=[uid],@pinstance = [instance], @pid = [id],@pproject = [project],

                        @plocation = [location], @pstart=[ProjectStart], @pend = [ProjectEnd] FROM #ptemp

        Where [instance] = @tinstance and [uid]=@tuid

        Select @crecord =[record], @cid = [id], @clocation = [location], @cproject = [project], @cstart = [Start], @cend=[End]

        from [ProjectHistory] where [record] = @i

            SET @cuid = CONVERT(varchar(15), @cid)+@cproject+@clocation

        IF @pid = @cid

        Begin --First check if its the project instance

            IF(@cuid = @puid and DATEDIFF(day,@cstart,@pend)<=7)

            Begin --this happens if its the same project period

                update #PTEMP set [ProjectStart] = @cstart, [ProjectEnd] = @cend where [Uid] = @puid and [instance] = @pinstance

                set @i=@i+1

                set @tinstance = @pinstance

                set @tuid = @puid

            END -- done with updating existing project instance

            ELSE

            BEGIN  -- Since it didnt match pervious record must be new project instance

                select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid

                insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)

                values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)

                set @i=@i+1 

                set @tuid = @cuid

            End -- Done with the new project instance for same id

        END -- Done with same id

        ELSE -- this is a new id so insert uid for id project combo

        begin -- new id so need to insert first record

            select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid

            insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)

            values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)   

            set @i=@i+1

            set @tuid = @cuid 

        END -- Done inserting new record for next id

    End -- End While    



    select * from #ptemp



    drop table #PTEMP



    END