Select current date and status in TSQL

Question

I have a query that selects from multiple tables. I need to select only the unique loans with the most current status date.

I've tried using MAX in the SELECT clause, but that didn't work.

    SELECT
        L.LOAN_NUMBER AS [loan_number],
        LAS_LOAN_DEF.LOAN_TYPE AS [loan_type] ,
        WORKFLOW_STEP_DEF.STEP_NAME AS [current_status],
        MAX(WORKFLOW_STEPS.ACTION_DATE) AS [current_status_date]                    
    FROM 
        LOAN AS L
        LEFT JOIN LAS_LOAN_DEF ON LAS_LOAN_DEF.LAS_LOAN_DEF_ID = L.LAS_LOAN_DEF_ID
        LEFT JOIN WORKFLOW ON WORKFLOW.[ENTITY_ID] = L.LOAN_ID
        LEFT JOIN WORKFLOW_STEPS ON WORKFLOW_STEPS.WORKFLOW_ID = WORKFLOW.WORKFLOW_ID
        LEFT JOIN WORKFLOW_STEP_DEF ON WORKFLOW_STEPS.WORKFLOW_STEP_DEF_ID = WORKFLOW_STEP_DEF.WORKFLOW_STEP_DEF_ID

    GROUP BY 
        L.LOAN_NUMBER, 
        LAS_LOAN_DEF.LOAN_TYPE, 
        WORKFLOW_STEP_DEF.STEP_NAME,
        WORKFLOW_STEPS.ACTION_DATE

This is the result set I got, which is not unique.

+-------------+---------------+----------------+-------------------------+
| loan_number |  loan_type    | current_status |  current_status_date    |
+-------------+---------------+----------------+-------------------------+
| 12200       | Business      |    Analysis    | 2019-08-15 13:43:42.733 |
| 12200       | Business      |  Underwriting  | 2019-08-15 13:43:53.577 |
| 13300       | Business      |    Analysis    | 2019-08-23 15:47:49.087 |
| 13300       | Business      |  Underwriting  | 2019-08-23 16:47:40.700 |
+-------------+---------------+----------------+-------------------------+

The result set I'm looking for is this:

+-------------+---------------+----------------+-------------------------+
| loan_number |  loan_type    | current_status |  current_status_date    |
+-------------+---------------+----------------+-------------------------+
| 12200       | Business      |  Underwriting  | 2019-08-15 13:43:53.577 |
| 13300       | Business      |  Underwriting  | 2019-08-23 16:47:40.700 |
+-------------+---------------+----------------+-------------------------+

How can I solve this?

What version of SQL do you use? Try LAST_VALUE() – Vitaly Borisov Sep 03 '19 at 19:23 — Vitaly Borisov, Sep 03 '19 at 19:23

score 0 · Answer 1 · edited Sep 03 '19 at 23:37

0

SELECT LOAN_NUMBER, loan_type, current_status,current_Status_Date
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY LOAN_NUMBER ORDER BY current_Status_Date desc) AS [ROWNUM] 
    FROM Loan
) x WHERE x.ROWNUM = 1

**Here Loan is my table Name

edited Sep 03 '19 at 23:37

Vitaly Borisov

1,133
2
13
20

answered Sep 03 '19 at 19:57

arpit rai

21
1
7

Select current date and status in TSQL

1 Answers1