Geek Answers Handbook

SQL Server Advanced Order by

I am trying to execute an extended order using an SQL query

I have a database that is used to track workflows and related activities.

Previous actions (which are completed) are in the closed state. They have both an Active_Date and a Closed_Date set.

Current activity is active . There is an Active_Date set, but Closed_Date is NULL

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

I would like to order

  • ECR.item_number , but I would like the order to be based on what activity is active in descending order
  • 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]

Return 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 | |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
+6
source share
2 answers

In this scenario, I find ECR.item_number in the active state and assign this date to the entire ECR.item_number group using the MAX () OVER () clause. Further sorting (ascending) in a new column with item number 10 in the SELECT statement. You can also use an alias as a sort column instead of the position number 10 in the 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 SQLFiddle Demo

+2
source

The key to this is getting the Active validity date 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 request, 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;

SQL Fiddle Sample Data Demo

+2
source

More articles:


All Articles