0

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:

  1. Rule 1 - The next sequential record for the id changes from one project to another or

  2. 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&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 figured it out, but its really slow. Takes about 30 seconds to run against 3000 projecthistory records, but I do get the results I'm looking for. One thing I did forget in my initial question, was the location field. – Josh Mar 11 '16 at 14:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.