CODEWITHSUNDEEP
sqlsql-server
Iofacture
Iofacture

Reputation: 685

Finding percentage on a sub-query with different where clauses

I am trying to find the percentage completed on a table with a sub-query. I can successfully get the value I need using the following, but I am concerned that I am unnecessarily using two queries to get there when one (or a join) would do:

SELECT (        
CAST((SELECT COUNT(WO.[WORK_ORDER_ID]) 
FROM [NCH].[nch].[WORK_ORDER] WO JOIN [NCH].[nch].[TASK] T ON WO.[TASK_ID] = T.[TASK_ID] 
WHERE WO.[LOT_ID] = 501 AND WO.[DRY_RUN_FLAG] != 1 
AND WO.[COMPLETED_DATE] IS NOT NULL AND T.[TASK_TYPE_ID] = 2) AS float) 
/ 
(CAST((SELECT COUNT(WO.[WORK_ORDER_ID]) 
FROM [NCH].[nch].[WORK_ORDER] WO JOIN [NCH].[nch].[TASK] T ON WO.[TASK_ID] = T.[TASK_ID] 
WHERE WO.[LOT_ID] = 501 AND T.[TASK_TYPE_ID] = 2) as Float))
) * 100 AS PERCENT_ROUGH_COMPLETED

Specifically, my concern is in the where clause. I am repeating the exact same query except for the where conditions. Could this be accomplished with a JOIN on the same table? Multiple joins are confusing me and SQL is one of my weakest skills. I feel like this query could be tightened up because I am going to have to repeat this in a much larger query.

EDIT

By using CASE I was able to configure the larger SQL query properly. I have never written a query this complex before, so forgive if it looks too complicated:

 SELECT L.[LOT_ID]
 ,L.[LOT_NUMBER]
 ,L.[JOBSITE_ID]
 ,L.[PHASE_ID]
 ,L.[MODEL_ID]
 ,M.[SQUARE_FOOTAGE]
 ,M.[MODEL_NAME]
 ,P.[PHASE_NAME]

 -- Case 1
 ,CASE 
    WHEN P.[ROUGH_SCHEDULE_FLAG] != 0 
  THEN 
(SELECT (SELECT AVG(CASE WHEN WO.[DRY_RUN_FLAG] <> 1 AND WO.[COMPLETED_DATE] IS NOT NULL THEN 1.0 ELSE 0 END)
FROM [NCH].[nch].[WORK_ORDER] WO 
JOIN [NCH].[nch].[TASK] T ON WO.[TASK_ID] = T.[TASK_ID] 
WHERE WO.[LOT_ID] = L.[LOT_ID] AND T.[TASK_TYPE_ID] = 1) * 100)
  ELSE 0 
  END AS ROUGH_PERCENT_COMPLETED,

  -- Case 2
  CASE 
    WHEN P.[INTERIOR_SCHEDULE_FLAG] != 0 
  THEN 
(SELECT (SELECT AVG(CASE WHEN WO.[DRY_RUN_FLAG] <> 1 AND WO.[COMPLETED_DATE] IS NOT NULL THEN 1.0 ELSE 0 END)
FROM [NCH].[nch].[WORK_ORDER] WO 
JOIN [NCH].[nch].[TASK] T ON WO.[TASK_ID] = T.[TASK_ID] 
WHERE WO.[LOT_ID] = L.[LOT_ID] AND T.[TASK_TYPE_ID] = 2) * 100)
  ELSE 0 
  END AS INTERIOR_PERCENT_COMPLETED,

  -- Case 3
  CASE 
    WHEN P.[WASH_SCHEDULE_FLAG] != 0 
  THEN 
(SELECT (SELECT AVG(CASE WHEN WO.[DRY_RUN_FLAG] <> 1 AND WO.[COMPLETED_DATE] IS NOT NULL THEN 1.0 ELSE 0 END)
FROM [NCH].[nch].[WORK_ORDER] WO 
JOIN [NCH].[nch].[TASK] T ON WO.[TASK_ID] = T.[TASK_ID] 
WHERE WO.[LOT_ID] = L.[LOT_ID] AND T.[TASK_TYPE_ID] = 3) * 100)
  ELSE 0 
  END AS WASH_PERCENT_COMPLETED  

 FROM [NCH].[nch].[LOT] L
 JOIN [NCH].[nch].[MODEL] M ON L.[MODEL_ID] = M.[MODEL_ID]
 JOIN [NCH].[nch].[PHASE] P ON L.[PHASE_ID] = P.[PHASE_ID]

WHERE L.[JOBSITE_ID] = 1502

result set

Upvotes: 0

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I would simplify this to:

select avg(case when wo.dry_run_flalg <> 1 and
                     wo.completed_date is not null
                then 1.0 else 0
           end) as ratio
from [NCH].[nch].[WORK_ORDER] wo join
     [NCH].[nch].[TASK] t
     on wo.[TASK_ID] = t.[TASK_ID] 
where wo.lot_id = 501 and
      wo.work_order_id is not null and
      t.task_type_id = 2

Upvotes: 2

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

You can do it by using CASE. In my opinion is better to use numeric(decimal) instead of float. float is an approximate datatype that can lead to wrong results. You can read more about this here.

WITH calculations as
(
    SELECT
        CASE 
            WHEN WO.[LOT_ID] = 501 
            AND WO.[DRY_RUN_FLAG] != 1 
            AND WO.[COMPLETED_DATE] IS NOT NULL 
            AND T.[TASK_TYPE_ID] = 2 
            AND WO.[WORK_ORDER_ID] IS NOT NULL THEN 1 --the last condition is to simulate COUNT(WO.[WORK_ORDER_ID])
        ELSE 0
        END AS part1
       ,CASE
            WHEN WO.[LOT_ID] = 501 
            AND T.[TASK_TYPE_ID] = 2
            AND WO.[WORK_ORDER_ID] IS NOT NULL THEN 1 --the last condition is to simulate COUNT(WO.[WORK_ORDER_ID])
        ELSE 0
        END AS part2
    FROM [NCH].[nch].[WORK_ORDER] WO 
    JOIN [NCH].[nch].[TASK] T 
        ON WO.[TASK_ID] = T.[TASK_ID] 
)
SELECT
    (cast(sum(part1) as numeric(18,6)) / cast(sum(part2) as numeric(18,6))) * 100 as calculation
from calculations

Upvotes: 1

Related Questions