September 22, 2015 at 10:21 am
Hi,
When I run the following CTE just be itself it gives out the results fine, but when I run it under select query, DENSE RANK makes everything as DENSE RANK = 1, it messes up the results:
It might be something very small that I am not able to see, if someone can eyeball it and figure out where I might be going wrong with JOIN or where clause would be highly appreciated.
Results of the CTE just by itself:
loanIDHistoryCounterDense Rank
4518130 2 1
4518130 3 2
4518130 4 3
4518130 5 4
4518130 6 5
Results when run in the select statement:
LoanIDHistoryCounterDense Rank
4518130 4 1
4518130 5 1
4518130 6 1
4518130 7 1
FULL QUERY
WITH History_CTE
AS
(
SELECT loanID, HistoryCounter
,DENSE_RANK() OVER (PARTITION BY LoanID ORDER BY HistoryCounter) AS [Dense Rank]
FROM History
WHERE TransactionAmt > '0'
--AND LoanID = '0004518130'
)
SELECT DISTINCT
L.LoanID
,H.HistoryCounter
---,DENSE_RANK() OVER (PARTITION BY H.LoanID ORDER BY H.HistoryCounter) AS [Dense Rank]
,H1.[Dense Rank]
,H.TransactionCode
,H.TransactionAmt
,CONVERT(VARCHAR(10), H.EffectiveDate, 101) AS [Effective Date]
,CONVERT(VARCHAR(10), L.FirstDueDate, 101) AS [1st PMT Due Date]
,DATEDIFF(day,(CONVERT(VARCHAR(10), L.FirstDueDate, 101)),(CONVERT(VARCHAR(10), H.EffectiveDate, 101))) AS [Days late]
FROM loan AS L
JOIN history AS H
ON L.LoanID = H.LoanID
LEFT OUTER JOIN History_CTE AS H1
ON L.LoanID = H1.LoanID AND H1.[Dense Rank] = 1
WHERE DATEDIFF(day,(CONVERT(VARCHAR(10), L.FirstDueDate, 101)),(CONVERT(VARCHAR(10), H.EffectiveDate, 101))) > 30
--AND H.TransactionCode = '210'
--AND H1.[DENSE RANK] = 1
AND H.TransactionAmt > '0'
AND DATEPART(yyyy, (CONVERT(VARCHAR(10), L.FirstDueDate, 101))) >= '2013'
--AND H.HistoryCounter IN (2,3,4)
AND L.LoanID = '0004518130'
ORDER BY [LoanID]
September 22, 2015 at 10:27 am
Try taking the AND H1.[Dense Rank] = 1 out of the join on the select and see what happens 🙂
It looks like it might be joining the dense rank 1 record from the CTE to multiple records in the history table.
September 22, 2015 at 10:47 am
Thanks Zartin
I did the following, rest kept the same, I am guessing my select statement is still wrong,please advise
Select
,(Select H1.[Dense Rank] from H1 WHERE [Dense Rank] = 1)
JOIN
LEFT OUTER JOIN History_CTE AS H1
September 22, 2015 at 11:18 am
Is there a reason to join to the CTE instead of just putting the rank function in the SQL query?
September 22, 2015 at 11:39 am
thanks again,
the reason for CTE is because I only want to display loans where there HistoryCounter has a dense rank of 1.
According to you if I use the following in the select statement,
,DENSE_RANK() OVER (PARTITION BY H.LoanID ORDER BY H.HistoryCounter) AS [Dense Rank]
How would I filter out that only Dense Rank = 1 records are displayed?
September 22, 2015 at 1:24 pm
Would anyone else know?
September 22, 2015 at 1:46 pm
Do you need something like this?
WITH History_CTE
AS
(
SELECT loanID,
HistoryCounter,
DENSE_RANK() OVER (PARTITION BY LoanID ORDER BY HistoryCounter) AS [Dense Rank]
FROM History
WHERE TransactionAmt > '0'
--AND LoanID = '0004518130'
)
SELECT DISTINCT
L.LoanID
,H.HistoryCounter
,H.[Dense Rank]
,H.TransactionCode
,H.TransactionAmt
,CONVERT(VARCHAR(10), H.EffectiveDate, 101) AS [Effective Date]
,CONVERT(VARCHAR(10), L.FirstDueDate, 101) AS [1st PMT Due Date]
,DATEDIFF(day,(CONVERT(VARCHAR(10), L.FirstDueDate, 101)),(CONVERT(VARCHAR(10), H.EffectiveDate, 101))) AS [Days late]
FROM loan AS L
JOIN History_CTE AS H ON L.LoanID = H.LoanID -- Removed the reference to the History table
WHERE DATEDIFF(day,(CONVERT(VARCHAR(10), L.FirstDueDate, 101)),(CONVERT(VARCHAR(10), H.EffectiveDate, 101))) > 30
--AND H.TransactionCode = '210'
AND H.[DENSE RANK] = 1 --Changed the condition from the JOIN to the WHERE
AND H.TransactionAmt > '0'
AND DATEPART(yyyy, (CONVERT(VARCHAR(10), L.FirstDueDate, 101))) >= '2013'
--AND H.HistoryCounter IN (2,3,4)
AND L.LoanID = '0004518130'
ORDER BY [LoanID]
I really don't understand if the problem is that everything is showing as dense_rank = 1 or if it was just a problem with the join that wasn't necessary and you only needed the correct condition.
September 22, 2015 at 2:20 pm
Luis, still doesn't work,
lets take this example and make it simpler; consider the following code:
WITH History_CTE
AS
(
SELECT loanID, HistoryCounter
,ROW_NUMBER() OVER (PARTITION BY LoanID ORDER BY HistoryCounter) AS [Dense Rank]
FROM History
WHERE TransactionAmt > '0'
AND LoanID = '0004518130'
)
SELECT DISTINCT H.loanID, H.HistoryCounter, [Dense Rank]
From History AS H
FULL OUTER JOIN History_CTE AS H1
ON H.LoanID = H1.LoanID
WHERE
[Dense Rank] = 1
AND h.LoanID = '0004518130'
AND TransactionAmt > '0'
Result Set if only I run the CTE:
loanID.............HistoryCounter...............Dense Rank
4518130................2 .................................. 1
4518130................3 .................................. 2
4518130................4 .................................. 3
4518130................5 .................................. 4
Result when I run the whole thing together:
loanID............. HistoryCounter...............Dense Rank
4518130................ 2 .................................. 1
4518130................ 3 .................................. 1
4518130................ 4 .................................. 1
4518130................ 5 .................................. 1
Required Result set: (This is what I would like)
loanID.............HistoryCounter...............Dense Rank
4518130................2 .................................. 1
September 22, 2015 at 2:28 pm
You can't blame me if you use a different query than the one I posted. You keep joining the CTE to the table with no valid reason. You're basically assigning a 1 to every row and adding more work to your query.
September 22, 2015 at 3:11 pm
I apologize I didn't mean any disrespect. I think I finally got it, you are right there was no need for me to join the same table again.
Again thanks for your help guyz..you all do s great job !!!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply