CODEWITHSUNDEEP
sqlsql-server
Hafiz
Hafiz

Reputation: 37

SQL query to group the data by the workflow criteria

I am writing a SQL query to view some project metrics I have 2 tables.

WorkflowDefinition (In this the workflow definition is according to the   product land and is different for different productLaneID)
ProductLaneID | StepID | StepName | WorkflowRevisionID
4               1        RA         1
4               2        Design     1
4               3        ME         1
4               4        Tooling    1
4               5        Waiting    1
4               6        Manuf      1
4               7        Completed  1
5               1        RA         1
5               2        Concepts   1
5               3        Design     1
5               4        ME         1
5               5        Tooling    1
5               6        Waiting    1
5               7        Manuf      1
5               8        FieldTesting1
5               9        Completed  1

In this I want to club

 Step 3&4 from ProductLaneID=4 as PLES
 StepID 5&6 from ProductLaneID = 4 as Manuf

StepID 2&3 from productLaneID =5 as Design
StepID 4&5 from productLaneID =5 as PLES
StepID 6&7 from productLaneID =5 as Manufacturing


ProjectScheduleHistory
ProjectID|FromStepID|ToStepID|EnteredStepDate|ExitedStepDate

Now a project can go from Step 1-2 and from 2-1 and then again to 1-2 So i want to find the Date of Initial step entry and the last date of step exit. Accordingly It will calculate the no of days in each step in terms of Design, PLES and Manuf

Result would be like

ProjectID | Design CompletionDate | Days in Design| PLES Comp Date | Days in PLES| Manuf Comp Date | No of Days in Manuf.

I have started with the following sql query.

select ProjectID,FromStepID,ToStepID,
[EnteredStepDate] = MIN(EnteredStepDate),
[ExitedStepDate] = MAX(ExitedStepDate),ProjectLaneID,
[DaysInStep] = SUM(DATEDIFF(DAY,EnteredStepDate,ExitedStepDate)),
REPLACE(REPLACE(REPLACE(REPLACE(StepName,'Manufacturing             Engineering','PLES'),'Tooling','PLES'),'Concept','Design'),'Waiting On   Order','Manufacturing')StepName
from lovWorkflowDefinition
left outer join dtaProjectScheduleHistory
on FromStepID = StepID
where (StepName = 'Design' or StepName = 'PLES' or StepName =  'Manufacturing') and (ProjectLaneID=4 or ProjectLaneID=5)
group by   ProjectID,FromStepID,ToStepID,EnteredStepDate,ExitedStepDate,ProjectLaneID,StepName

I want to group the data according to Design, PLES and Manuf

.

Upvotes: 1

Views: 390

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider the following using conditional CASE/WHEN statements and derived table subquery:

SELECT ProjectID, Max([Step Name]) As [StepName],
       Max(CASE WHEN [Step Name] = 'Design' THEN ExitedStepDate END) As [Design Comp Date],
       Sum(CASE WHEN [Step Name] = 'Design' 
                THEN DATEDIFF(DAY, EnteredStepDate, ExitedStepDate) END) AS [Days in Design],

       Max(CASE WHEN [Step Name] = 'Ples' THEN ExitedStepDate END) As [PLES Comp Date],
       Sum(CASE WHEN [Step Name] = 'Ples' 
                THEN DATEDIFF(DAY, EnteredStepDate, ExitedStepDate) END) AS [Days in Design],

       Max(CASE WHEN [Step Name] = 'Manuf' THEN ExitedStepDate END) As [Manuf Comp Date],
       Sum(CASE WHEN [Step Name] = 'Manuf' 
                THEN DATEDIFF(DAY, EnteredStepDate, ExitedStepDate) END) AS [Days in Design],
FROM 
   (SELECT proj.ProjectID, proj.FromStepID, proj.ToStepID, 
           proj.EnteredStepDate, proj.ExitedStepDate,
           CASE 
             WHEN proj.StepID IN (3, 4) AND work.ProductLaneID = 4 THEN 'Ples'
             WHEN proj.StepID IN (5, 6) AND work.ProductLaneID = 4 THEN 'Manufacturing'
             WHEN proj.StepID IN (2, 3) AND work.ProductLaneID = 5 THEN 'Design'
             WHEN proj.StepID IN (4, 5) AND work.ProductLaneID = 5 THEN 'Ples'
             WHEN proj.StepID IN (6, 7) AND work.ProductLaneID = 5 THEN 'Manufacturing'
           END  As [Step Name]
   FROM WorkflowDefinition work
   LEFT OUTER JOIN ProjectScheduleHistory proj
   ON wrk.StepID = proj.FromStepID
   ) As dT
GROUP BY ProjectID

Upvotes: 1

Related Questions