One of the most complex Views i have ever seen (View nested into another view)

Question

Post reply

One of the most complex Views i have ever seen (View nested into another view)

SQLPain

Hall of Fame

Points: 3522
More actions
August 25, 2015 at 2:27 pm

#320880

A view is nested into another view with the following code below, it was written years back with somebody inexperienced. It is a decline letter sent out by a finance company. I tried to clean some code, but if someone could help me clean up more, especially the Joins I would really appreciate it.

The first column in the select statement 'TOP 5000 '6' AS DocumentCode' does that make sense? All is the Union All required?

SELECT

TOP 5000 '6' AS DocumentCode

,CASE AppDetails.ProductID WHEN 3 THEN 'D' ELSE 'M' END AS CompanyName

,CONVERT(CHAR(30), UPPER(v_M1BorrowerNameFormatted.MailName)) AS MailingName

,CONVERT(CHAR(30), UPPER(Borrowers.MailingStreet)) AS MailingStreet

,CONVERT(CHAR(30), UPPER(Borrowers.MailingCity)) AS MailingCity

,UPPER(Borrowers.MailingState) AS MailingState

,CONVERT(CHAR(10), Borrowers.MailingZip) AS MailingZip

,CONVERT(CHAR(30), v_M1BorrowerNameFormatted.SalutationName) AS SalutationName

,CONVERT(CHAR(50), Channels.NameLong) AS DealerName

,CONVERT(CHAR(20), Products.ProductShortName) AS ProductName

,CONVERT(CHAR(80), r1.ReasonText) AS Reason1

,CONVERT(CHAR(80), r2.ReasonText) AS Reason2

,CONVERT(CHAR(80), r3.ReasonText) AS Reason3

,CONVERT(CHAR(80), r4.ReasonText) AS Reason4

,CASE WHEN Borrowers.EFXConsumer >= 1000 THEN 'Equifax Credit Information Services' ELSE ' ' END AS EFXName

,CASE WHEN Borrowers.TUConsumer >= 1000 THEN 'Trans Union Corporation ' ELSE ' ' END AS TUName

,CASE WHEN Borrowers.TRWConsumer >= 1000 THEN 'Experian Inc. ' ELSE ' ' END AS TRWName

,CONVERT(char(10),AppDetails.DateDetailDisposed, 101) AS DecisionDate

FROM

MP100.dbo.tblDocumentRequests tblDocumentRequests INNER JOIN

MP100.dbo.AppDetReasons adr1 ON tblDocumentRequests.AppID = adr1.AppID AND tblDocumentRequests.AppDetailID = adr1.AppDetailID AND

adr1.AppDetReasonID =

(SELECT min(adr5.AppDetReasonID)

FROM AppDetReasons adr5

WHERE adr5.AppDetailID = adr1.AppDetailID AND adr5.ReasonType = 'D' AND adr5.ReasonCode > 0) AND adr1.ReasonType = 'D' AND

adr1.ReasonCode > 0 LEFT OUTER JOIN

Reasons r1 ON adr1.ReasonType = r1.ReasonType AND adr1.ReasonCode = r1.ReasonCode LEFT OUTER JOIN

AppDetReasons adr2 ON adr1.AppID = adr2.AppID AND adr1.AppDetailID = adr2.AppDetailID AND adr2.AppDetReasonID = adr1.AppDetReasonID + 1 AND

adr2.ReasonType = 'D' AND adr2.ReasonCode > 0 LEFT OUTER JOIN

Reasons r2 ON adr2.ReasonType = r2.ReasonType AND adr2.ReasonCode = r2.ReasonCode LEFT OUTER JOIN

AppDetReasons adr3 ON adr1.AppID = adr3.AppID AND adr1.AppDetailID = adr3.AppDetailID AND adr3.AppDetReasonID = adr2.AppDetReasonID + 1 AND

adr3.ReasonType = 'D' AND adr3.ReasonCode > 0 LEFT OUTER JOIN

Reasons r3 ON adr3.ReasonType = r3.ReasonType AND adr3.ReasonCode = r3.ReasonCode LEFT OUTER JOIN

AppDetReasons adr4 ON adr1.AppID = adr4.AppID AND adr1.AppDetailID = adr4.AppDetailID AND adr4.AppDetReasonID = adr3.AppDetReasonID + 1 AND

adr4.ReasonType = 'D' AND adr4.ReasonCode > 0 LEFT OUTER JOIN

Reasons r4 ON adr4.ReasonType = r4.ReasonType AND adr4.ReasonCode = r4.ReasonCode INNER JOIN

MP100.dbo.Borrowers Borrowers ON tblDocumentRequests.AppID = Borrowers.AppID AND tblDocumentRequests.BorrowerID = Borrowers.BorrowerID AND

((Borrowers.TUConsumer >= 1000) OR

(Borrowers.TRWConsumer >= 1000) OR

(Borrowers.EFXConsumer >= 1000)) INNER JOIN

MP100.dbo.v_M1BorrowerNameFormatted v_M1BorrowerNameFormatted ON

tblDocumentRequests.BorrowerID = v_M1BorrowerNameFormatted.BorrowerID INNER JOIN

MP100.dbo.Channels Channels ON tblDocumentRequests.ChannelID = Channels.ChannelID INNER JOIN

MP100.dbo.AppDetails AppDetails ON tblDocumentRequests.AppID = AppDetails.AppID AND tblDocumentRequests.AppDetailID = AppDetails.AppDetailID AND

AppDetails.StatusAppDetail = 'D' AND AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20 INNER JOIN

MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID

WHERE tblDocumentRequests.DocumentID = 5

UNION ALL

SELECT

TOP 5000 '5' AS DocumentCode

,CASE AppDetails.ProductID WHEN 3 THEN 'D' ELSE 'M' END AS CompanyName

,CONVERT(CHAR(30), UPPER(v_M1BorrowerNameFormatted.MailName)) AS MailingName

,CONVERT(CHAR(30), UPPER(Borrowers.MailingStreet)) AS MailingStreet

,CONVERT(CHAR(30), UPPER(Borrowers.MailingCity)) AS MailingCity

,UPPER(Borrowers.MailingState) AS MailingState

,CONVERT(CHAR(10), Borrowers.MailingZip) AS MailingZip

,CONVERT(CHAR(30), v_M1BorrowerNameFormatted.SalutationName) AS SalutationName

,CONVERT(CHAR(50), Channels.NameLong) AS DealerName

,CONVERT(CHAR(20), Products.ProductShortName) AS ProductName

,CONVERT(CHAR(80), r1.ReasonText) AS Reason1

,CONVERT(CHAR(80), r2.ReasonText) AS Reason2

,CONVERT(CHAR(80), r3.ReasonText) AS Reason3

,CONVERT(CHAR(80), r4.ReasonText) AS Reason4

,CASE WHEN Borrowers.EFXConsumer >= 1000 THEN 'Equifax Credit Information Services' ELSE ' ' END AS EFXName

,CASE WHEN Borrowers.TUConsumer >= 1000 THEN 'Trans Union Corporation ' ELSE ' ' END AS TUName

,CASE WHEN Borrowers.TRWConsumer >= 1000 THEN 'Experian Inc. ' ELSE ' ' END AS TRWName

,CONVERT(char(10), AppDetails.DateDetailDisposed, 101) AS DecisionDate

FROM

MP100.dbo.tblDocumentRequests tblDocumentRequests INNER JOIN

MP100.dbo.AppDetReasons adr1 ON tblDocumentRequests.AppID = adr1.AppID AND

tblDocumentRequests.AppDetailID = adr1.AppDetailID AND

adr1.AppDetReasonID =

(SELECT min(adr5.AppDetReasonID)

FROM AppDetReasons adr5

WHERE adr5.AppDetailID = adr1.AppDetailID AND adr5.ReasonType = 'I' AND adr5.ReasonCode > 0) AND adr1.ReasonType = 'I' AND

adr1.ReasonCode > 0 LEFT OUTER JOIN

Reasons r1 ON adr1.ReasonType = r1.ReasonType AND adr1.ReasonCode = r1.ReasonCode LEFT OUTER JOIN

AppDetReasons adr2 ON adr1.AppID = adr2.AppID AND adr1.AppDetailID = adr2.AppDetailID AND adr2.AppDetReasonID = adr1.AppDetReasonID + 1 AND

adr2.ReasonType = 'I' AND adr2.ReasonCode > 0 LEFT OUTER JOIN

Reasons r2 ON adr2.ReasonType = r2.ReasonType AND adr2.ReasonCode = r2.ReasonCode LEFT OUTER JOIN

AppDetReasons adr3 ON adr1.AppID = adr3.AppID AND adr1.AppDetailID = adr3.AppDetailID AND adr3.AppDetReasonID = adr2.AppDetReasonID + 1 AND

adr3.ReasonType = 'I' AND adr3.ReasonCode > 0 LEFT OUTER JOIN

Reasons r3 ON adr3.ReasonType = r3.ReasonType AND adr3.ReasonCode = r3.ReasonCode LEFT OUTER JOIN

AppDetReasons adr4 ON adr1.AppID = adr4.AppID AND adr1.AppDetailID = adr4.AppDetailID AND adr4.AppDetReasonID = adr3.AppDetReasonID + 1 AND

adr4.ReasonType = 'I' AND adr4.ReasonCode > 0 LEFT OUTER JOIN

Reasons r4 ON adr4.ReasonType = r4.ReasonType AND adr4.ReasonCode = r4.ReasonCode INNER JOIN

MP100.dbo.Borrowers Borrowers ON tblDocumentRequests.AppID = Borrowers.AppID AND tblDocumentRequests.BorrowerID = Borrowers.BorrowerID AND

((Borrowers.TUConsumer >= 1000) OR

(Borrowers.TRWConsumer >= 1000) OR

(Borrowers.EFXConsumer >= 1000)) INNER JOIN

MP100.dbo.v_M1BorrowerNameFormatted v_M1BorrowerNameFormatted ON

tblDocumentRequests.BorrowerID = v_M1BorrowerNameFormatted.BorrowerID INNER JOIN

MP100.dbo.Channels Channels ON tblDocumentRequests.ChannelID = Channels.ChannelID INNER JOIN

MP100.dbo.AppDetails AppDetails ON tblDocumentRequests.AppID = AppDetails.AppID AND tblDocumentRequests.AppDetailID = AppDetails.AppDetailID AND

AppDetails.StatusAppDetail = 'I' AND AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20 INNER JOIN

MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID

WHERE

tblDocumentRequests.DocumentID = 231

ORDER BY 7, 4, 3
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ScottPletcher SSC Guru Points: 100262 More actions · Answer 1

I think you can combine the two queries, possibly if will perform better, esp. if the joins are a lot of overhead. As to the joins, they seem fairly straightforward -- although I don't have a lot of time now -- but don't see what could be done to them.



SELECT

CASE WHEN adr1.ReasonType = 'D' THEN '6' ELSE '5' END AS DocumentCode

,ROW_NUMBER() OVER(PARTITION BY CASE WHEN adr1.ReasonType = 'D' THEN '6' ELSE '5' END ORDER BY DecisionDate DESC) AS row_num

,CASE AppDetails.ProductID WHEN 3 THEN 'D' ELSE 'M' END AS CompanyName

,CONVERT(CHAR(30), UPPER(v_M1BorrowerNameFormatted.MailName)) AS MailingName

,CONVERT(CHAR(30), UPPER(Borrowers.MailingStreet)) AS MailingStreet

,CONVERT(CHAR(30), UPPER(Borrowers.MailingCity)) AS MailingCity

,UPPER(Borrowers.MailingState) AS MailingState

,CONVERT(CHAR(10), Borrowers.MailingZip) AS MailingZip

,CONVERT(CHAR(30), v_M1BorrowerNameFormatted.SalutationName) AS SalutationName

,CONVERT(CHAR(50), Channels.NameLong) AS DealerName

,CONVERT(CHAR(20), Products.ProductShortName) AS ProductName

,CONVERT(CHAR(80), r1.ReasonText) AS Reason1

,CONVERT(CHAR(80), r2.ReasonText) AS Reason2

,CONVERT(CHAR(80), r3.ReasonText) AS Reason3

,CONVERT(CHAR(80), r4.ReasonText) AS Reason4

,CASE WHEN Borrowers.EFXConsumer >= 1000 THEN 'Equifax Credit Information Services' ELSE ' ' END AS EFXName

,CASE WHEN Borrowers.TUConsumer >= 1000 THEN 'Trans Union Corporation ' ELSE ' ' END AS TUName

,CASE WHEN Borrowers.TRWConsumer >= 1000 THEN 'Experian Inc. ' ELSE ' ' END AS TRWName

,CONVERT(char(10),AppDetails.DateDetailDisposed, 101) AS DecisionDate

FROM

MP100.dbo.tblDocumentRequests tblDocumentRequests INNER JOIN

MP100.dbo.AppDetReasons adr1 ON tblDocumentRequests.AppID = adr1.AppID AND tblDocumentRequests.AppDetailID = adr1.AppDetailID AND

adr1.ReasonType IN ( 'D', 'I' ) AND

adr1.AppDetReasonID =

    (SELECT min(adr5.AppDetReasonID)

    FROM AppDetReasons adr5

    WHERE adr5.AppDetailID = adr1.AppDetailID AND adr5.ReasonType = adr1.ReasonType AND adr5.ReasonCode > 0) AND

adr1.ReasonCode > 0 LEFT OUTER JOIN

Reasons r1 ON adr1.ReasonType = r1.ReasonType AND adr1.ReasonCode = r1.ReasonCode LEFT OUTER JOIN

AppDetReasons adr2 ON adr1.AppID = adr2.AppID AND adr1.AppDetailID = adr2.AppDetailID AND adr2.AppDetReasonID = adr1.AppDetReasonID + 1 AND

adr2.ReasonType = adr1.ReasonType AND adr2.ReasonCode > 0 LEFT OUTER JOIN

Reasons r2 ON adr2.ReasonType = r2.ReasonType AND adr2.ReasonCode = r2.ReasonCode LEFT OUTER JOIN

AppDetReasons adr3 ON adr1.AppID = adr3.AppID AND adr1.AppDetailID = adr3.AppDetailID AND adr3.AppDetReasonID = adr2.AppDetReasonID + 1 AND

adr3.ReasonType = adr1.ReasonType AND adr3.ReasonCode > 0 LEFT OUTER JOIN

Reasons r3 ON adr3.ReasonType = r3.ReasonType AND adr3.ReasonCode = r3.ReasonCode LEFT OUTER JOIN

AppDetReasons adr4 ON adr1.AppID = adr4.AppID AND adr1.AppDetailID = adr4.AppDetailID AND adr4.AppDetReasonID = adr3.AppDetReasonID + 1 AND

adr4.ReasonType = adr1.ReasonType AND adr4.ReasonCode > 0 LEFT OUTER JOIN

Reasons r4 ON adr4.ReasonType = r4.ReasonType AND adr4.ReasonCode = r4.ReasonCode INNER JOIN

MP100.dbo.Borrowers Borrowers ON tblDocumentRequests.AppID = Borrowers.AppID AND tblDocumentRequests.BorrowerID = Borrowers.BorrowerID AND

((Borrowers.TUConsumer >= 1000) OR

(Borrowers.TRWConsumer >= 1000) OR

(Borrowers.EFXConsumer >= 1000)) INNER JOIN

MP100.dbo.v_M1BorrowerNameFormatted v_M1BorrowerNameFormatted ON

tblDocumentRequests.BorrowerID = v_M1BorrowerNameFormatted.BorrowerID INNER JOIN

MP100.dbo.Channels Channels ON tblDocumentRequests.ChannelID = Channels.ChannelID INNER JOIN

MP100.dbo.AppDetails AppDetails ON tblDocumentRequests.AppID = AppDetails.AppID AND tblDocumentRequests.AppDetailID = AppDetails.AppDetailID AND

AppDetails.StatusAppDetail = adr1.ReasonType AND AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20 INNER JOIN

MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID

WHERE tblDocumentRequests.DocumentID IN ( 5, 231 )

ORDER BY 7, 4, 3

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

SQLPain

Hall of Fame

Points: 3522

August 25, 2015 at 3:25 pm

#1822339

Thanks for the fast reply.

the current result set contains only 500 values, I don't think it would ever go higher than that. what would be the difference in the syntax if we completely remove the 'TOP' clause?

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

SQLPain (8/25/2015)

Thanks for the fast reply.

the current result set contains only 500 values, I don't think it would ever go higher than that. what would be the difference in the syntax if we completely remove the 'TOP' clause?

That makes the combined simpler and more efficient. I've made the relevant changes to my code above.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

SQLPain Hall of Fame Points: 3522 More actions · Answer 4

SQLPain

Hall of Fame

Points: 3522

August 25, 2015 at 4:07 pm

#1822346

Is row number statement still required in the select statement?

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

SQLPain (8/25/2015)

Thanks for the fast reply.

the current result set contains only 500 values, I don't think it would ever go higher than that. what would be the difference in the syntax if we completely remove the 'TOP' clause?

When the dataset grows one day and you get blacklisted for spamming, then you won't care about the syntax difference. 😉

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLPain Hall of Fame Points: 3522 More actions · Answer 6

SQLPain

Hall of Fame

Points: 3522

September 11, 2015 at 2:58 pm

#1826166

I ran the code again you gave, this time its giving me the following error.

Msg 195, Level 15, State 10, Line 3

'ROW_NUMBER' is not a recognized function name.

Msg 156, Level 15, State 1, Line 29

Incorrect syntax near the keyword 'AND'

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

Eirikur Eiriksson

SSC Guru

Points: 182631

September 12, 2015 at 2:13 am

#1826196

SQLPain (9/11/2015)

I ran the code again you gave, this time its giving me the following error.

Msg 195, Level 15, State 10, Line 3

'ROW_NUMBER' is not a recognized function name.

Msg 156, Level 15, State 1, Line 29

Incorrect syntax near the keyword 'AND'

What is the output of this code

😎

SELECT @@VERSION;

SQLPain Hall of Fame Points: 3522 More actions · Answer 8

SQLPain

Hall of Fame

Points: 3522

September 14, 2015 at 11:16 am

#1826475

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

Ed Wagner SSC Guru Points: 287015 More actions · Answer 9

The ROW_NUMBER() function is valid on SQL 2012. I would check to make sure you have the comma before it.

One thing that's been missed is that if you remove your TOP clause, you'll need to remove your ORDER BY clause. This also means that you'll have to add the ORDER BY clause to your SQL that queries this view if you don't have one already.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287015 More actions · Answer 10

ScottPletcher (8/25/2015)

I think you can combine the two queries, possibly if will perform better, esp. if the joins are a lot of overhead. As to the joins, they seem fairly straightforward -- although I don't have a lot of time now -- but don't see what could be done to them.

I agree with Scott that the joins look pretty straightforward. The only thing I'd suggest for your join predicates is to make sure the data types you're joining on match. If you join mismatched data types, the implicit cast will definitely impact performance.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ScottPletcher SSC Guru Points: 100262 More actions · Answer 11

ScottPletcher

SSC Guru

Points: 100262

September 14, 2015 at 11:34 am

#1826483

Check the compatibility level of the db. Compatibility needs to be at least 90 for ROW_NUMBER() to work.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 12

ScottPletcher (9/14/2015)

Check the compatibility level of the db. Compatibility needs to be at least 90 for ROW_NUMBER() to work.

No, the compatibility level does not affect newer functionality.

😎

This will run on SQL Server 2005

USE [master]



GO

EXEC dbo.sp_dbcmptlevel @dbname=N'[My 2005 DB Name]', @new_cmptlevel=70

GO

SELECT

    TOP (10) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RID

FROM sys.objects;

SQLPain Hall of Fame Points: 3522 More actions · Answer 13

Thank you guyz,

In the last part, when I just want to see (.AppDetails.DateDetailDisposed), 21 days less instead of (20-25 days) the whole result messes up.

I am using:

AppDetails.DateDetailDisposed = getdate() - 21

INNER JOIN

MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID

Not sure what's happening here

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 14

ScottPletcher (8/25/2015)

I think you can combine the two queries, possibly if will perform better, esp. if the joins are a lot of overhead. As to the joins, they seem fairly straightforward -- although I don't have a lot of time now -- but don't see what could be done to them.

SELECT CASE WHEN adr1.ReasonType = 'D' THEN '6' ELSE '5' END AS DocumentCode ,ROW_NUMBER() OVER(PARTITION BY CASE WHEN adr1.ReasonType = 'D' THEN '6' ELSE '5' END ORDER BY DecisionDate DESC) AS row_num ,CASE AppDetails.ProductID WHEN 3 THEN 'D' ELSE 'M' END AS CompanyName ,CONVERT(CHAR(30), UPPER(v_M1BorrowerNameFormatted.MailName)) AS MailingName ,CONVERT(CHAR(30), UPPER(Borrowers.MailingStreet)) AS MailingStreet ,CONVERT(CHAR(30), UPPER(Borrowers.MailingCity)) AS MailingCity ,UPPER(Borrowers.MailingState) AS MailingState ,CONVERT(CHAR(10), Borrowers.MailingZip) AS MailingZip ,CONVERT(CHAR(30), v_M1BorrowerNameFormatted.SalutationName) AS SalutationName ,CONVERT(CHAR(50), Channels.NameLong) AS DealerName ,CONVERT(CHAR(20), Products.ProductShortName) AS ProductName ,CONVERT(CHAR(80), r1.ReasonText) AS Reason1 ,CONVERT(CHAR(80), r2.ReasonText) AS Reason2 ,CONVERT(CHAR(80), r3.ReasonText) AS Reason3 ,CONVERT(CHAR(80), r4.ReasonText) AS Reason4 ,CASE WHEN Borrowers.EFXConsumer >= 1000 THEN 'Equifax Credit Information Services' ELSE ' ' END AS EFXName ,CASE WHEN Borrowers.TUConsumer >= 1000 THEN 'Trans Union Corporation ' ELSE ' ' END AS TUName ,CASE WHEN Borrowers.TRWConsumer >= 1000 THEN 'Experian Inc. ' ELSE ' ' END AS TRWName ,CONVERT(char(10),AppDetails.DateDetailDisposed, 101) AS DecisionDate FROM MP100.dbo.tblDocumentRequests tblDocumentRequests INNER JOIN MP100.dbo.AppDetReasons adr1 ON tblDocumentRequests.AppID = adr1.AppID AND tblDocumentRequests.AppDetailID = adr1.AppDetailID AND adr1.ReasonType IN ( 'D', 'I' ) AND adr1.AppDetReasonID = (SELECT min(adr5.AppDetReasonID) FROM AppDetReasons adr5 WHERE adr5.AppDetailID = adr1.AppDetailID AND adr5.ReasonType = adr1.ReasonType AND adr5.ReasonCode > 0) AND adr1.ReasonCode > 0 LEFT OUTER JOIN Reasons r1 ON adr1.ReasonType = r1.ReasonType AND adr1.ReasonCode = r1.ReasonCode LEFT OUTER JOIN AppDetReasons adr2 ON adr1.AppID = adr2.AppID AND adr1.AppDetailID = adr2.AppDetailID AND adr2.AppDetReasonID = adr1.AppDetReasonID + 1 AND adr2.ReasonType = adr1.ReasonType AND adr2.ReasonCode > 0 LEFT OUTER JOIN Reasons r2 ON adr2.ReasonType = r2.ReasonType AND adr2.ReasonCode = r2.ReasonCode LEFT OUTER JOIN AppDetReasons adr3 ON adr1.AppID = adr3.AppID AND adr1.AppDetailID = adr3.AppDetailID AND adr3.AppDetReasonID = adr2.AppDetReasonID + 1 AND adr3.ReasonType = adr1.ReasonType AND adr3.ReasonCode > 0 LEFT OUTER JOIN Reasons r3 ON adr3.ReasonType = r3.ReasonType AND adr3.ReasonCode = r3.ReasonCode LEFT OUTER JOIN AppDetReasons adr4 ON adr1.AppID = adr4.AppID AND adr1.AppDetailID = adr4.AppDetailID AND adr4.AppDetReasonID = adr3.AppDetReasonID + 1 AND adr4.ReasonType = adr1.ReasonType AND adr4.ReasonCode > 0 LEFT OUTER JOIN Reasons r4 ON adr4.ReasonType = r4.ReasonType AND adr4.ReasonCode = r4.ReasonCode INNER JOIN MP100.dbo.Borrowers Borrowers ON tblDocumentRequests.AppID = Borrowers.AppID AND tblDocumentRequests.BorrowerID = Borrowers.BorrowerID AND ((Borrowers.TUConsumer >= 1000) OR (Borrowers.TRWConsumer >= 1000) OR (Borrowers.EFXConsumer >= 1000)) INNER JOIN MP100.dbo.v_M1BorrowerNameFormatted v_M1BorrowerNameFormatted ON tblDocumentRequests.BorrowerID = v_M1BorrowerNameFormatted.BorrowerID INNER JOIN MP100.dbo.Channels Channels ON tblDocumentRequests.ChannelID = Channels.ChannelID INNER JOIN MP100.dbo.AppDetails AppDetails ON tblDocumentRequests.AppID = AppDetails.AppID AND tblDocumentRequests.AppDetailID = AppDetails.AppDetailID AND AppDetails.StatusAppDetail = adr1.ReasonType AND AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20 INNER JOIN MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID WHERE tblDocumentRequests.DocumentID IN ( 5, 231 ) ORDER BY 7, 4, 3

Only real issue I have with the code above is the ORDER BY is sorting by ordinal position instead of column name. The query would be much clearer using the column names instead of ordinal position.