CODEWITHSUNDEEP
sqlsql-serverreporting-services
traggatmot
traggatmot

Reputation: 1463

Using ROW_NUMBER() with a derived table

I have a table in which I am returning an ID and a DATEDIFF value:

SELECT        
    CTD_ITEM_ID, CTD_ASSIGNED_TO, 
    DATEDIFF(d, CTD_ASSIGNED_DATE, CTD_REVIEW_COMPLETED_DATE) AS DATE_DIFF
FROM            
    IRT_ITEM_TRACKING_DETAIL_TBL
ORDER BY 
    CTD_ITEM_ID, DATE_DIFF DESC

That results in many values of date_diff for identical ctd_item_id.

From this I wish to drive a table where on the largest date_diff is kept for each ctd_item_id. So, I should use ROW_NUMBER() - but I am not sure how to combine the creation of the value above (date_diff) with the ROW_NUMBER() capability.

I have tried many iterations of something like this:

WITH max_date_diffs AS 
( 
    SELECT 
        date_diffs.ctd_item_id, 
        date_diffs.date_diff 
    FROM   
        (SELECT 
             ctd_item_id, 
             DATEDIFF(d, ctd_assigned_date, ctd_review_complete_date) AS datediff 
         FROM   
             irt_item_tracking_detail_tbl) AS date_diffs
) 
SELECT   
    ROW_NUMBER() OVER ctd_item_id (ORDER BY date_diff DESC) AS rownumber, 
    * 
FROM
    max_date_diffs

But I have not been able to get it to work.

EDIT:

This statement:

SELECT        
    CTD_ITEM_ID, CTD_ASSIGNED_TO, 
    MAX(DATEDIFF(DAY, CTD_ASSIGNED_DATE, CTD_REVIEW_COMPLETED_DATE)) AS MAX_DATE_DIFF
FROM
    IRT_ITEM_TRACKING_DETAIL_TBL
GROUP BY 
    CTD_ITEM_ID, CTD_ASSIGNED_TO
ORDER BY 
    CTD_ITEM_ID, max_date_diff DESC

returns a table like this:

enter image description here

But I only want the rows that are highlighted in yellow, which is why I felt that on this table I would need to perform the ROW_NUMBER().

Upvotes: 2

Views: 217

Answers (2)

sticky bit
sticky bit

Reputation: 37472

Seems like you're missing PARTITION BY ctd_item_id.

Try:

SELECT ctd_item_id,
       ctd_assigned_to,
       max_date_diff
       FROM (SELECT ctd_item_id,
                    ctd_assigned_to,
                    max(datediff(day, ctd_assigned_date, ctd_review_completed_date)) max_date_diff,
                    row_number() OVER (PARTITION BY ctd_item_id,
                                       ORDER BY max(datediff(day, ctd_assigned_date, ctd_review_completed_date)) DESC) rn
                    FROM irt_item_tracking_detail_tbl
                    GROUP BY ctd_item_id,
                             ctd_assigned_to) x
       WHERE rn = 1
       ORDER BY ctd_item_id ASC,
                max_date_diff DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269813

You seem to want an aggregation query:

SELECT ctd_item_id, 
       MAX(DATEDIFF(DAY, ctd_assigned_date, ctd_review_completed_date)) AS MAX_DATE_DIFF
FROM irt_item_tracking_detail_tbl 
GROUP BY ctd_item_id 
ORDER BY max_date_diff ;

Upvotes: 0

Related Questions