Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record I have a table called projecthistory,
it is 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 scenarios 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&amp;2. 

Record 9 become 55555A1 and because of the date gap
between records 9&amp;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 &lt; @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)&lt;=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