Faith Pratt Portfolio
Question: How many task where being completed in every department, bank wide. Leadership needed a drill down method for looking loan level and create a dashboard for visualization
SQL:
SELECT dbo.TaskTracking.LoanID, dbo.TaskTracking.TaskID, dbo.TaskCommentLog.TaskCommentDate, dbo.TaskCommentLog.TaskCommentCode,
dbo.TaskCommentLog.TaskCommentuserID,
dbo.TaskCommentLog.TaskCommentTextLine1, dbo.TaskCommentLog.TaskCommentTextLine2,
dbo.TaskCommentLog.TaskCommentTextLine3, dbo.TaskCommentLog.TaskCommentTextLine4,
dbo.TaskCommentLog.TaskCommentTextLine5, dbo.TaskCommentLog.TaskCommentTextLine6,
dbo.TaskTracking.ActualCloseDate, dbo.TaskTracking.ExpectedCloseDate,
dbo.TaskTracking.LastUpdateDate, dbo.TaskTracking.ResponsibleDepartmentID,ResponsibleID,
dbo.TaskTracking.StatusFlag
FROM dbo.TaskTracking INNER JOIN
dbo.TaskCommentLog ON dbo.TaskTracking.LoanID = dbo.TaskCommentLog.LoanID
dbo.TaskCommentLog.TaskCommentuserID,
dbo.TaskCommentLog.TaskCommentTextLine1, dbo.TaskCommentLog.TaskCommentTextLine2,
dbo.TaskCommentLog.TaskCommentTextLine3, dbo.TaskCommentLog.TaskCommentTextLine4,
dbo.TaskCommentLog.TaskCommentTextLine5, dbo.TaskCommentLog.TaskCommentTextLine6,
dbo.TaskTracking.ActualCloseDate, dbo.TaskTracking.ExpectedCloseDate,
dbo.TaskTracking.LastUpdateDate, dbo.TaskTracking.ResponsibleDepartmentID,ResponsibleID,
dbo.TaskTracking.StatusFlag
FROM dbo.TaskTracking INNER JOIN
dbo.TaskCommentLog ON dbo.TaskTracking.LoanID = dbo.TaskCommentLog.LoanID
OUTPUT:
DELIVERABLES:
---------------------------------------------------------------------------------------------------------------
Question: How many new Escrow Analysis where coming in the upcoming months?
SQL:
SELECT LastAnalysisDate, Count(LastAnalysisDate) AS [# of Analysis],
MONTH(LastAnalysisDate) AS [Month] FROM dbo.Escrow
WHERE LastAnalysisDate between '01/01/2023' and '07/30/2023'
GROUP BY LastAnalysisDate
ORDER BY MONTH(LastAnalysisDate)
MONTH(LastAnalysisDate) AS [Month] FROM dbo.Escrow
WHERE LastAnalysisDate between '01/01/2023' and '07/30/2023'
GROUP BY LastAnalysisDate
ORDER BY MONTH(LastAnalysisDate)
OUTPUT:
DELIVERABLES:
---------------------------------------------------------------------------------------------------------------
QUESTION:
1. HOW MANY TASK WERE CLOSED BY EACH LEAD SINCE AUGUST 2023- DEC 2023
2. HOW MANY TASK WERE COMPLETED ON BY MONTH GROUPED BY TASK
SQL:
SELECT dbo.TaskTracking.LoanID, dbo.TaskTracking.TaskID, dbo.TaskTracking.ActualCloseDate, dbo.TaskTracking.ExpectedCloseDate, dbo.TaskTracking.LastUpdateDate, dbo.TaskTracking.ResponsibleDepartmentID,ResponsibleID, dbo.TaskTracking.StatusFlag
FROM dbo.TaskTracking INNER JOIN
dbo.TaskCommentLog ON dbo.TaskTracking.LoanID = dbo.TaskCommentLog.LoanID
WHERE ResponsibleDepartmentID = 'CUS'and ActualCloseDate between '08-01-2023' and '12-13-23'
ORDER BY ActualCloseDate
DELIVERABLES:
--------------------------------------------------------------------------------------------------------------
Question: In the 1CR department, how many task where being completed on time based on compliance
SQL:
SELECT LoanID, TaskID, ActualCloseDate, CompletedByID, ContactDepartmentID, ExpectedCloseDate, FollowUpDate,
LastUpdateDate, ReceivedDate, ResponsibleDepartmentID, ResponsibleID, StatusFlag, TaskProcessorID
FROM dbo.TaskTracking
WHERE ResponsibleDepartmentID = 'CUS'and ActualCloseDate between '08-01-2023' and '12-13-23'
LastUpdateDate, ReceivedDate, ResponsibleDepartmentID, ResponsibleID, StatusFlag, TaskProcessorID
FROM dbo.TaskTracking
WHERE ResponsibleDepartmentID = 'CUS'and ActualCloseDate between '08-01-2023' and '12-13-23'
OUTPUT:
DELIVERABLES:
