Advanced SQL Server Order by

Question

I am trying to do an advanced order by with SQL query

I have a database that is used to track workflows and their associated activities

The previous activities (that are completed) are in a closed state. They have both an Active_Date and Closed_Date set

The current activity is in an active state. There is an Active_Date set, but the Closed_Date is NULL

For each ECR.item_number there will only be one activity in the active state

I would like to order by

1. ECR.item_number but I would like the order to be based on which activity in the active state in descending order
2. A.ACTIVE_DATE

Query:

SELECT 
    ECR.item_number,
    ECR.title,
    convert(VARCHAR(10),ECR.CREATED_ON,120) AS [Date CR Created],
    convert(VARCHAR(10),A.ACTIVE_DATE,120) AS [Activity Activated],
    convert(VARCHAR(10),A.CLOSED_DATE,120) AS [Activity Closed],
    A.STATE,
    A.NAME AS [Activity Name],

    (CASE
        WHEN 
            (A.STATE='Closed')
        THEN 
            DATEDIFF(DAY, A.ACTIVE_DATE, A.CLOSED_DATE)      
    END) AS [DAYS ACTIVITY (WAS) OPEN],

    (CASE
        WHEN 
            (A.STATE='Active')
        THEN 
            DATEDIFF(DAY, A.ACTIVE_DATE, Getdate())
    END) AS [DAYS ACTIVITY HAS BEEN OPEN]

FROM 
    innovator.ecr AS ECR
    INNER JOIN innovator.workflow AS WF 
        ON CR.id = WF.source_id
    INNER JOIN innovator.workflow_process AS WFP 
        ON WF.related_id = WFP.id
    INNER JOIN innovator.workflow_process_activity AS WPA 
        ON WFP.id = WPA.source_id
    INNER JOIN innovator.activity AS A 
        ON WPA.related_id = A.id

--Shown for clarity
ORDER BY
    ECR.ITEM_NUMBER,
    [Activity Activated]

Sample return data

|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
|CR Number  |   Title   |   Date CR Created |   Activity Name       |   Activity Activated  |   Activity Closed |   DAYS ACTIVITY (WAS) OPEN    |   DAYS ACTIVITY HAS BEEN OPEN |
|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
|CR-000119  |   ITEM 1  |   4/12/2012       |   Submit CR           |   4/12/2012           |   31/01/2013      |   58                          |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Check CR            |   31/01/2013          |   13/02/2013      |   13                          |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Review CR           |   13/02/2013          |   21/02/2013      |   8                           |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Technical Review    |   21/02/2013          |   28/02/2013      |   7                           |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   CRB Meeting         |   28/02/2013          |   NULL            |   NULL                        |   11                          |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Check CR            |   6/12/2012           |   21/12/2012      |   15                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Submit CR           |   6/12/2012           |   6/12/2012       |   0                           |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Review CR           |   21/12/2012          |   17/01/2013      |   27                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Technical Review    |   17/01/2013          |   6/03/2013       |   48                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   CRB Meeting         |   6/03/2013           |   NULL            |   NULL                        |   5                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Submit CR           |   11/01/2013          |   15/01/2013      |   4                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Check CR            |   15/01/2013          |   16/01/2013      |   1                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Review CR           |   16/01/2013          |   21/01/2013      |   5                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Technical Review    |   21/01/2013          |   25/01/2013      |   4                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   CRB Meeting         |   25/01/2013          |   31/01/2013      |   6                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Technical Review    |   31/01/2013          |   27/02/2013      |   27                          |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   CRB Meeting         |   27/02/2013          |   NULL            |   NULL                        |   12                          |
|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|

Desired Result

|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
|CR Number  |   Title   |   Date CR Created |   Activity Name       |   Activity Activated  |   Activity Closed |   DAYS ACTIVITY (WAS) OPEN    |   DAYS ACTIVITY HAS BEEN OPEN |
|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
|CR-000136  |   ITEM 3  |   11/01/2013      |   Submit CR           |   11/01/2013          |   15/01/2013      |   4                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Check CR            |   15/01/2013          |   16/01/2013      |   1                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Review CR           |   16/01/2013          |   21/01/2013      |   5                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Technical Review    |   21/01/2013          |   25/01/2013      |   4                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   CRB Meeting         |   25/01/2013          |   31/01/2013      |   6                           |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   Technical Review    |   31/01/2013          |   27/02/2013      |   27                          |   0                           |
|CR-000136  |   ITEM 3  |   11/01/2013      |   CRB Meeting         |   27/02/2013          |   NULL            |   NULL                        |   12                          |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Submit CR           |   4/12/2012           |   31/01/2013      |   58                          |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Check CR            |   31/01/2013          |   13/02/2013      |   13                          |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Review CR           |   13/02/2013          |   21/02/2013      |   8                           |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   Technical Review    |   21/02/2013          |   28/02/2013      |   7                           |   0                           |
|CR-000119  |   ITEM 1  |   4/12/2012       |   CRB Meeting         |   28/02/2013          |   NULL            |   NULL                        |   11                          |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Check CR            |   6/12/2012           |   21/12/2012      |   15                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Submit CR           |   6/12/2012           |   6/12/2012       |   0                           |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Review CR           |   21/12/2012          |   17/01/2013      |   27                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   Technical Review    |   17/01/2013          |   6/03/2013       |   48                          |   0                           |
|CR-000123  |   ITEM 2  |   6/12/2012       |   CRB Meeting         |   6/03/2013           |   NULL            |   NULL                        |   5                           |
|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|

Answer 1

In this scenario I find ECR.item_number in the active state and assign this date to the whole group ECR.item_number by means of MAX() OVER () clause. Further sorting (ascending) on the new column with number of position 10 in SELECT statement. Also you can use alias as the sort column instead of number of position 10 in SELECT statement

SELECT ECR.item_number,
       ECR.title,
       convert(VARCHAR(10),ECR.CREATED_ON,120) AS [Date CR Created],
       convert(VARCHAR(10),A.ACTIVE_DATE,120) AS [Activity Activated],
       convert(VARCHAR(10),A.CLOSED_DATE,120) AS [Activity Closed],
       A.STATE,
       A.NAME AS [Activity Name],
    (CASE WHEN (A.STATE='Closed')
          THEN DATEDIFF(DAY, A.ACTIVE_DATE, A.CLOSED_DATE)      
     END) AS [DAYS ACTIVITY (WAS) OPEN],
    (CASE WHEN (A.STATE='Active')
        THEN DATEDIFF(DAY, A.ACTIVE_DATE, Getdate())
     END) AS [DAYS ACTIVITY HAS BEEN OPEN],
     MAX(CASE WHEN A.CLOSED_DATE IS NULL THEN A.ACTIVE_DATE END) OVER (PARTITION BY ECR.item_number)
FROM innovator.ecr AS ECR
    INNER JOIN innovator.workflow AS WF 
        ON CR.id = WF.source_id
    INNER JOIN innovator.workflow_process AS WFP 
        ON WF.related_id = WFP.id
    INNER JOIN innovator.workflow_process_activity AS WPA 
        ON WFP.id = WPA.source_id
    INNER JOIN innovator.activity AS A 
        ON WPA.related_id = A.id
ORDER BY 10 ASC, ECR.ITEM_NUMBER, A.ACTIVE_DATE ASC

Simple demo on SQLFiddle

Answer 2

The key to this is to get the date of the Active activity for each Item. This can be done using analytic functions:

[DateOfActiveActivity] = MIN(CASE WHEN A.CLOSED_DATE IS NULL THEN A.ACTIVE_DATE END) OVER(PARTITION BY ECR.item_number)

So to integrate this into your query you can use:

WITH CTE AS
(   SELECT  [CRNumber] = ECR.item_number,
            ECR.title,
            [DateCRCreated] = CAST(ECR.CREATED_ON AS DATE),
            [ActivityActivated] = CAST(A.ACTIVE_DATE AS DATE),
            [ActivityClosed] = CAST(A.CLOSED_DATE AS DATE),
            A.STATE,
            [ActivityName] = A.NAME,
            [DAYSACTIVITYOPEN] = CASE WHEN A.STATE = 'Closed' THEN DATEDIFF(DAY, A.ACTIVE_DATE, A.CLOSED_DATE) END,
            [DAYSACTIVITYHASBEENOPEN] = CASE WHEN A.STATE = 'Active' THEN DATEDIFF(DAY, A.ACTIVE_DATE, GETDATE()) END, 
            [DateOfActiveActivity] = MIN(CASE WHEN A.CLOSED_DATE IS NULL THEN A.ACTIVE_DATE END) OVER(PARTITION BY ECR.item_number)
    FROM    innovator.ecr AS ECR
            INNER JOIN innovator.workflow AS WF 
                ON CR.id = WF.source_id
            INNER JOIN innovator.workflow_process AS WFP 
                ON WF.related_id = WFP.id
            INNER JOIN innovator.workflow_process_activity AS WPA 
                ON WFP.id = WPA.source_id
            INNER JOIN innovator.activity AS A 
                ON WPA.related_id = A.id
)
SELECT  [CR Number] = [CRNumber],
        Title,
        [Date CR Created] = CONVERT(VARCHAR(10), DateCRCreated, 120),
        [Activity Activated] = CONVERT(VARCHAR(10), ActivityActivated, 120),
        [Activity Closed] = CONVERT(VARCHAR(10), ActivityClosed, 120),
        [STATE],
        [Activity Name] = ActivityName,
        [DAYS ACTIVITY (WAS) OPEN] = [DAYSACTIVITYOPEN],
        [DAYS ACTIVITY HAS BEEN OPEN] = [DAYSACTIVITYHASBEENOPEN]
FROM    CTE
ORDER BY DateOfActiveActivity ASC, ActivityActivated, ActivityClosed; 

Demo with sample data on SQL Fiddle