I am a beginner with TSQL and was hoping to get some guidance on conditions with TSQL
Is there a way for the same SQL field to queried as different columns? My field name changes as the file moves from different statuses we have defined and I am attempting to use the associated timestamp from the status change to analyze some trends . For example the associated data for a file would look something like this:
<File_History
Loanstatusdescriptor= “Underwrite Assigned” Status.EventTime= “4/1/2017 12:05”>
<File_History
Loanstatusdescriptor= “Closing Request Received” Status.EventTime= “4/3/2017 17:15”>
I have a report I am creating that is based on the statuses of certain files as they move through the workflow process. Would the query look something like:
SELECT
LoanInfo.UnderwriterName
,File_History.Status.EventTime (IF loanstatusdescriptor= “Underwrite Assigned”) AS ‘Underwriter Assigned Date’
,File_History.Status.EventTime ( IF loanstatusdescriptor= “Closing Request Received”) AS ‘Closing Request Receieved Date’
….
The End Product Would Look something like
Underwriter|Underwriter Assigned Date |Closing Request Received Date
Bobby Brown 4/1/2017 4/3/2017
Sally Jones 4/7/2017 4/9/2017
Chris Graff 4/6/2017 4/17/2017
How would I write out the T-SQL Statements that would allow the columns to be created based on loan status?
Assuming File_History_Status is a table that can be joined to LoanInfo:
Using conditional aggregation with a known amount of columns:
select
li.UnderwriterName
, [Underwriter_Assigned_Date] = max(case
when fhs.LoanStatusDescriptor = 'Underwriter Assigned'
then fhs.EventTime
end)
, [Closing_Request_Received_Date] = max(case
when fhs.LoanStatusDescriptor = 'Closing Request Received'
then fhs.EventTime
end)
from LoanInfo li
inner join File_History_Status fhs
on li.LoanId = fhs.LoanId
group by li.UnderwriterName