Process to map a transaction table to a status table I have a table, TRANSACTIONS, in SQL Server 2012 with ~100000 rows that has the following columns:

TRANSACTION_ID
TRANSACTION_DATE
PERSON_ID
FROM_STATUS_LK
TO_STATUS_LK
FROM_GROUP_ID
TO_GROUP_ID
ORGANIZATION_LK
TRANSACTION_TYPE_LK


I want to map each record to a table, PERSONS, with the following columns:

PERSON_ID
SEQNO
STATUS_LK
GROUP_ID
FROM_DATE
TO_DATE
ORGANIZATION_LK


The new table will have SEQNO's that reset after each TRANSACTION_DATE. For example, if N transactions occurred on October, 20th 2014, then there would be N records in the child table with SEQNO's from 1-N for that date. 

What SQL constructs could I use to achieve this?
The problem with a sequence is that you have to specify the top end of the range and that's unpredictable in your scenario.  But you could track it on your own without having to worry about caching. Be sure to index and check as you see fit.

CODE:

--CREATE THE JOIN TABLE
IF OBJECT_ID ('dbo.PERSONS_TRANSACTION','U') IS NULL
    CREATE TABLE PERSONS_TRANSACTION 
    (
          SEQUENCE_DATE    DATE
        , SEQNO            INT
        , PERSON_ID        INT
        , TRANSACTION_ID   INT
    );
GO

--CREATE A PROC TO PERFORM INSERTS
IF OBJECT_ID ('dbo.usp_Insert_PERSONS_TRANSACTION','P') IS NULL
    EXEC sp_executesql N'CREATE PROC dbo.usp_Insert_PERSONS_TRANSACTION AS SELECT 1 STUB;';
GO

ALTER PROC dbo.usp_Insert_PERSONS_TRANSACTION  (@personID INT,@transactionID INT)
AS
    DECLARE 
          @today  DATE
        , @maxSeq INT;

    --GET THE DATE
    SET @today = CONVERT(DATE,GETDATE());
    --PRINT @today

    --GET THE LAST SEQ NUMBER (NOT A TRUE SEQUENCE)
    SELECT @maxSeq = MAX(SEQNO)
    FROM PERSONS_TRANSACTION
    WHERE SEQUENCE_DATE = @today;
    --PRINT @maxSeq

    --IF THERE IS NO SEQENCE NUMBER IT HASN'T RESET YET, SO THE SEQUENCE NUMBER IS 1, INSERT
    IF @maxSeq IS NULL
    BEGIN
        INSERT PERSONS_TRANSACTION
        VALUES (@today,1,@personID,@transactionID);
    END
    --IF THERE IS A SEQUENCE NUMBER, ADD 1 AND INSERT
    ELSE
    BEGIN
        SET @maxSeq +=1;
        INSERT PERSONS_TRANSACTION
        VALUES (@today,@maxSeq,@personID,@transactionID);
    END;
GO

--ADD SOME TEST RECORDS
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=2;

--MOVE THE DATE BACK A DAY TO TEST RESET
UPDATE PERSONS_TRANSACTION
    SET SEQUENCE_DATE = '2017-09-24';

--ADD SOME MORE RECORDS 
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=2;

SELECT * FROM PERSONS_TRANSACTION;

--DROP TABLE PERSONS_TRANSACTION;
--DROP PROC usp_Insert_PERSONS_TRANSACTION;