August 25, 2015 at 2:27 pm
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
August 25, 2015 at 2:45 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2015 at 3:25 pm
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?
August 25, 2015 at 3:41 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2015 at 4:07 pm
Is row number statement still required in the select statement?
August 25, 2015 at 5:05 pm
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);September 11, 2015 at 2:58 pm
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'
September 12, 2015 at 2:13 am
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;
September 14, 2015 at 11:16 am
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
September 14, 2015 at 11:21 am
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.
September 14, 2015 at 11:25 am
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.
September 14, 2015 at 11:34 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 14, 2015 at 11:45 am
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;
September 14, 2015 at 2:00 pm
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
September 14, 2015 at 2:35 pm
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.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply