February 6, 2015 at 6:39 pm
I need help creating a query in SQL server. The problem is that TABLE A has EventRefSeq and the other two tables don't, so it cause the results to duplicate the information from TABLE B for each record in TABLE A. I am only pulling values form TABLE B that does not have null values in the last column. Below I show my expected result which should return only 3 rows, but instead it is returning 9 rows because it is repeating all 3 authorization, and decision date information on each of the eventRefSeq so that each eventRefSeq shows 3 rows instead f 1
I have the following 3 tables
TABLE A
memberIDEventSeqEventRefSeqProcedureRefStartDateRefEnd DateRefRequest Date
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.000
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.000
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.000
Table B
memberIDEventSeqcolumnNameModificationDateModifiedbyUpdatedValue
1234567100000Authorization_Date2014-01-21 07:57:50.34372Jan 21 2014 12:00AM
1234567100000Authorization_Date2014-02-20 08:59:32.86771Null
1234567100000Authorization_Date2014-02-21 09:45:20.04378Feb 21 2014 12:00AM
1234567100000Authorization_Date2014-07-08 15:06:41.77370Null
1234567100000Authorization_Date2014-07-21 08:05:07.24372Jul 21 2014 12:00AM
1234567100000decision_sent_date2014-01-07 17:08:19 87072Jan 7 2014 5:08PM
1234567100000decision_sent_date2014-01-21 07:57:50.34372Null
1234567100000decision_sent_date2014-01-21 08:21:40.97078Jan 21 2014 08:21AM
1234567100000decision_sent_date2014-02-20 08:59:32.86771Null
1234567100000decision_sent_date2014-02-21 09:46:36.86078Feb 21 2014 9:46AM
1234567100000decision_sent_date2014-07-08 15:06:41.77370Null
1234567100000decision_sent_date2014-07-08 15:21:03.12370Jul 8 2014 3:21PM
1234567100000decision_sent_date2014-07-21 08:05:07.24372Null
1234567100000Referral_request_date2014-01-06 17:53:48.69372Jan 2 2014 5:47PM
1234567100000Referral_request_date2014-02-20 08:59:32.86771Feb 18 2014 5:47PM
1234567100000Referral_request_date2014-07-08 15:06:41.77370Jul 7 2014 5:47PM
Table C
memberIDEventSeqauthorizationNumber
12345671000009999999999999
Query result I am trying to get
memberIDEventSeqEventReferralSequenceProcedureRefStartDateRefEnd DateRefRequest DateauthorizationNumberAuthorization DateDecision_date
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-01-21 07:57:50.3432014-01-07 17:08:19 870
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123
Any assistance will be greatly appreciated
Thanks
Moe
February 7, 2015 at 2:27 am
Quick question, how do the tables relate to each other, cannot see anything obvious at the first glance?
😎
February 7, 2015 at 6:10 am
Thanks for responding.
The tables are linked by the two first columns: memberID and EventSeq
I am selecting all the columns for table A, the last column from table C and the 4th column for table B. Table B is where I believe the issues stems from. I need the first not null row where the column name is authorization date to go on the first row of the result, the second not null row where the column name is authorization date to go on the second row of the results, and the third not null row where the column name is Authorization_Date to go on the third row - this goes in the authorization date column. The the same should happen for the decision date column in the result only this should use where column name is decision_sent_date from Table B
Expected Results
memberIDEventSeqEventReferralSequenceProcedureRefStartDateRefEnd DateRefRequest DateauthorizationNumberAuthorization DateDecision_date
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-01-21 07:57:50.3432014-01-07 17:08:19 870
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123
Actual Results
memberIDEventSeqEventReferralSequenceProcedureRefStartDateRefEnd DateRefRequest DateauthorizationNumberAuthorization DateDecision_date
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-01-21 07:57:50.3432014-01-07 17:08:19 870
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860
12345671000001990002014-01-062014-02-062014-01-02 17:47:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-01-21 07:57:50.3432014-01-07 17:08:19 870
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860
12345671000002990002014-01-062014-07-062014-02-18 08:56:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-01-21 07:57:50.3432014-01-07 17:08:19 870
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-02-21 09:45:20.0432014-02-21 09:46:36.860
12345671000003990002014-07-062015-01-062014-07-07 15:05:00.00099999999999992014-07-21 08:05:07.2432014-07-08 15:21:03.123
February 7, 2015 at 8:38 am
Here is a quick possible solution which uses the ROW_NUMER function to enumerate the values in Table B in order to create an iterator for linking with the EventRefSeq from Table A.
😎
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 NULL)
) 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
TA.memberID
,TA.EventSeq
,TA.EventRefSeq
,TA.[Procedure]
,TA.RefStartDate
,TA.[RefEnd Date]
,TA.[RefRequest Date]
,TC.authorizationNumber
,CD.Authorization_Date
,CD.decision_sent_date
FROM @TABLE_A TA
INNER JOIN @TABLE_C TC
ON TA.memberID = TC.memberID
AND TA.EventSeq = TC.EventSeq
INNER JOIN CROSSTAB_DATA CD
ON TA.memberID = CD.memberID
AND TA.EventSeq = CD.EventSeq
AND TA.EventRefSeq = CD.TB_RID
;
Results
memberID EventSeq EventRefSeq Procedure RefStartDate RefEnd Date RefRequest Date authorizationNumber Authorization_Date decision_sent_date
----------- ----------- ----------- ----------- ------------ ----------- ----------------------- -------------------- ----------------------- -----------------------
1234567 100000 1 99000 2014-01-06 2014-02-06 2014-01-02 17:47:00.000 9999999999999 2014-01-21 07:57:50.343 2014-01-07 17:08:19.870
1234567 100000 2 99000 2014-01-06 2014-07-06 2014-02-18 08:56:00.000 9999999999999 2014-02-21 09:45:20.043 2014-01-21 08:21:40.970
1234567 100000 3 99000 2014-07-06 2015-01-06 2014-07-07 15:05:00.000 9999999999999 2014-07-21 08:05:07.243 2014-02-21 09:46:36.860
February 7, 2015 at 11:43 am
Eirikur,
This is excellent!!
Thank You so much for responding and for the code. Learn something new with the crosstab.
I will work on getting the decision_sent_date to begin with the second date so that the decision sent dates are:
2014-01-21 08:21:40.970
2014-02-21 09:46:36.860
2014-07-08 15:21:03.123
If I am unable to get it to show the above I may have to post back for help. If I figure it out I will post what I did.
There are 4 decision sent dates. The first two dates goes with the first row as it was a denial and then reversed to approved - so the first row has two decision sent dates. Then the third decision date should be on the second row and the fourth decision sent date should be on the third row.
I cannot thank you enough for this. Greatly appreciate you help!
February 7, 2015 at 12:02 pm
You are very welcome, if you have any problems just let us know.
😎
A quick thought on the remainder of the problem, what is missing (from my point of view) is more knowledge of the data. To explain what I mean, a data set has no order, the order of which the rows are returned from the server are entirely up to the server to decide unless one explicitly applies an order by directive. What this means is simply that if there are any rules/order applicable such as ascending date/time, that has to be made clear in order to achieve the correct results.
February 7, 2015 at 12:27 pm
Yes, you are correct. We are using a vendor application and apparently when it was implemented table A stores line level history with the EventReferralSequence field. But they did not carry that over to the other tables. Hence the problem with trying to get data.
But you have helped much on getting me most the results I am aiming for. Your code works perfectly for 98% of the data. Only in those cases where there is multiple decision on a line it does not work. And I am working with 100,000 plus rows of data.
The rule is that:
- The Authorization_Date cannot be less than the RefRequest Date, it can be equal to or greater than but not less than
- The decision_sent_date cannot be less than the Authorization_Date, it can be equal to or greater than but not less than
Basically a customer makes a request for an authorization - the date when customer make the request is the RefRequest Date
Then the request is reviewed and it is either approved or denied - that is the authorization_date
If it is denied and the customer appeals, and it is approved or denied, then there is a second decision_sent_date
There can be multiple denials and appeal, hence multiple decision sent dates.
Once it is reviewed and approved/denied the customer is notified of the decision - this is the decision_sent_date
Thanks again!
February 14, 2015 at 1:59 pm
Hi Eirikur,
I modified the final select as follows to include the DENSE_RANK to get line number.
SELECT
CD.memberID
,TA.EventSeq
,TA.EventRefSeq
,DENSE_RANK() OVER (ORDER BY TA.RefStartDate) AS LINE_NUMBER
,TA.[Procedure]
,TA.RefStartDate
,TA.[RefEnd Date]
,TA.[RefRequest Date]
,TC.authorizationNumber
,CD.Authorization_Date
,CD.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
;
However the decision_sent_column is not ordering lowest to highest as I expect it to. Any idea how I can sort it ascending so the dates are arranged lowest to highest??
And is there a way to sync the event sequence with the Line_Number field. I tried:
AND LINE_NUMBER = TA.EventRefSeq
WHERE LINE_NUMBER = TA.EventRefSeq
I also tried putting LINE_NUMBER in square bracket, single and double quotes but it keeps giving me an error
I either need a way to get the decision sent date column to sort ascending or a way to sync the Line number with the event referral sequence, or maybe I have the Line_Number in the wrong place, maybe is should be place in and earlier part of the code.
Any help you or anyone else can offer will be appreciated.
Thanks,
Moe
February 16, 2015 at 7:47 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply