So here is my final code - not perfect but it gets what I needed, along with some extra null rows. I just dump the output into excel and remove the null decision rows using a macro. Again thanks to Eirikur for assisting me with this by providing the initial code that I was able to tweak to get the needed data.

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TABLE_A TABLE

(

memberID INT NOT NULL

,EventSeq INT NOT NULL

,EventRefSeq INT NOT NULL

,[Procedure] INT NOT NULL

,RefStartDate DATE NOT NULL

,[RefEnd Date] DATE NOT NULL

,[RefRequest Date] DATETIME NOT NULL

)

INSERT INTO @TABLE_A(memberID,EventSeq,EventRefSeq,[Procedure],RefStartDate,[RefEnd Date],[RefRequest Date])

VALUES

(1234567,100000,1,99000,'2014-01-06','2014-02-06','2014-01-02 17:47:00.000')

,(1234567,100000,2,99000,'2014-01-06','2014-07-06','2014-02-18 08:56:00.000')

,(1234567,100000,3,99000,'2014-07-06','2015-01-06','2014-07-07 15:05:00.000')

;

DECLARE @TABLE_B TABLE

--

(

memberID INT NOT NULL

,EventSeq INT NOT NULL

,columnName VARCHAR(50) NOT NULL

,ModificationDate DATETIME NOT NULL

,Modifiedby TINYINT NOT NULL

,UpdatedValue SMALLDATETIME NULL

);

INSERT INTO @TABLE_B(memberID,EventSeq,columnName,ModificationDate,Modifiedby,UpdatedValue)

VALUES

(1234567,100000,'Authorization_Date', '2014-01-21 07:57:50.343',72,'Jan 21 2014 12:00AM')

,(1234567,100000,'Authorization_Date', '2014-02-20 08:59:32.867',71,Null)

,(1234567,100000,'Authorization_Date', '2014-02-21 09:45:20.043',78,'Feb 21 2014 12:00AM')

,(1234567,100000,'Authorization_Date', '2014-07-08 15:06:41.773',70,Null)

,(1234567,100000,'Authorization_Date', '2014-07-21 08:05:07.243',72,'Jul 21 2014 12:00AM')

,(1234567,100000,'decision_sent_date', '2014-01-07 17:08:19.870',72,'Jan 7 2014 5:08PM')

,(1234567,100000,'decision_sent_date', '2014-01-21 07:57:50.343',72,Null)

,(1234567,100000,'decision_sent_date', '2014-01-21 08:21:40.970',78,'Jan 21 2014 08:21AM')

,(1234567,100000,'decision_sent_date', '2014-02-20 08:59:32.867',71,Null)

,(1234567,100000,'decision_sent_date', '2014-02-21 09:46:36.860',78,'Feb 21 2014 9:46AM')

,(1234567,100000,'decision_sent_date', '2014-07-08 15:06:41.773',70,Null)

,(1234567,100000,'decision_sent_date', '2014-07-08 15:21:03.123',70,'Jul 8 2014 3:21PM')

,(1234567,100000,'decision_sent_date', '2014-07-21 08:05:07.243',72,Null)

,(1234567,100000,'Referral_request_date', '2014-01-06 17:53:48.693',72,'Jan 2 2014 5:47PM')

,(1234567,100000,'Referral_request_date', '2014-02-20 08:59:32.867',71,'Feb 18 2014 5:47PM')

,(1234567,100000,'Referral_request_date', '2014-07-08 15:06:41.773',70,'Jul 7 2014 5:47PM')

;

DECLARE @TABLE_C TABLE

(

memberID INT NOT NULL

,EventSeq INT NOT NULL

,authorizationNumber BIGINT NOT NULL

);

INSERT INTO @TABLE_C(memberID,EventSeq,authorizationNumber)

VALUES (1234567,100000,9999999999999)

;

;WITH BASE_DATA AS

(

SELECT

TB.memberID

,TB.EventSeq

,ROW_NUMBER() OVER

(

PARTITION BY TB.columnName

ORDER BY (SELECT TB.ModificationDate)

) AS TB_RID

,TB.columnName

,TB.ModificationDate

,TB.Modifiedby

,TB.UpdatedValue

FROM @TABLE_B TB

WHERE TB.UpdatedValue IS NOT NULL

)

,CROSSTAB_DATA AS

(

SELECT

BD.memberID

,BD.EventSeq

,BD.TB_RID

,MAX(CASE WHEN BD.columnName = 'Authorization_Date' THEN BD.ModificationDate END) AS Authorization_Date

,MAX(CASE WHEN BD.columnName = 'decision_sent_date' THEN BD.ModificationDate END) AS decision_sent_date

,MAX(CASE WHEN BD.columnName = 'Referral_request_date' THEN BD.ModificationDate END) AS Referral_request_date

FROM BASE_DATA BD

GROUP BY BD.TB_RID

,BD.memberID

,BD.EventSeq

)

SELECT DISTINCT

CD.memberID

,TA.EventSeq

,TA.EventRefSeq

,DENSE_RANK() OVER (ORDER BY CD.decision_sent_date) AS LINE_NUMBER

,TA.[Procedure]

,TA.RefStartDate

,TA.[RefEnd Date]

,TA.[RefRequest Date]

,TC.authorizationNumber

,CD.Authorization_Date

,CASE

WHEN CD1.decision_sent_date < CD.Authorization_Date THEN (Select CD1.decision_sent_date Where CD1.TB_RID = TA.EventRefSeq +1)

ELSE (Select CD1.decision_sent_date Where CD1.TB_RID = TA.EventRefSeq +1 AND CD1.Decision_Sent_Date IS NOT NULL )

END as [CD1.Decision_Sent_Date]

FROM CROSSTAB_DATA CD

INNER JOIN @TABLE_C TC

ON CD.memberID = TC.memberID

AND CD.EventSeq = TC.EventSeq

LEFT JOIN @TABLE_A TA

ON CD.memberID = TA.memberID

AND CD.EventSeq = TA.EventSeq

AND CD.TB_RID = TA.EventRefSeq

INNER JOIN CROSSTAB_DATA CD1

ON CD.memberID = CD1.memberID

WHERECD1.memberID = TC.memberID

ANDCD1.EventSeq = TC.EventSeq

ANDTA.EventSeq IS NOT NULL

ANDTA.EventRefSeq IS NOT NULL

memberIDEventSeqEventRefSeqLINE_NUMBERProcedureRefStartDateRefEnd DateRefRequest DateauthorizationNumberAuthorization_DateCD1.Decision_Sent_Date

123456710000011990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-01-21 07:57:50.343NULL

123456710000011990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-01-21 07:57:50.3432014-01-21 08:21:40.970

123456710000022990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-02-21 09:45:20.043NULL

123456710000022990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860

123456710000033990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-07-21 08:05:07.243NULL

123456710000033990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123