Menu
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
ECR.item_number but I would like the order to be based on which activity in the active state in descending orderA.ACTIVE_DATEQuery:
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 |
|-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
975
You can use the ASC and DESC keywords to specify ascending (smallest value first) or descending (largest value first) order. The default order is ascending.
You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
ASC. The ASC command is used to sort the data returned in ascending order.
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
95
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
