Joe Torre - Monday, February 5, 2018 2:23 PM
i tried below query, but this does not work with all scenarios. it always get Max values for test which does not have parallel test , rather it should SUM for different tests.SELECT SUM(vbn.new) as NewestimatedTime from
(
SELECT CASE WHEN xyz.parallelMethodId = 0 THEN sum(xyz.estimatedTIme)
when xyz.parallelMethodId =1 THEN Max(xyz.estimatedTIme)
end as new
from(
SELECT CASE WHEN abc.parallelMethodId IS NULL THEN 0
WHEN abc.parallelMethodId IS NOT NULL THEN 1
END as parallelMethodId,abc.estimatedTIme
from(SELECT testmethod, Max(estimatedtime) as estimatedTIme , b.parallelMethodId FROM temp_testmethods A
LEFT OUTER JOIN temp_parallelmethods B ON B.parallelmethodid = A.id
WHERE testmethod IN ( 'T103', 'T102', 'T109','TX','TX1' )
group by testmethod,b.parallelMethodId) abc) xyz
group by xyz.parallelMethodId) vbn