I have two tables like so:
tblOrders: OrderNo (pk), CurrentStepNo (fk)
tblSteps: StepNo (pk), OrderNo (fk), StepName, StepType, StepStart, StepStop
tblOrders contains tons of information about our sales orders, while
tblSteps contains tons of information regarding the proper sequential steps it takes to build the material we are selling.
I am trying to construct a query that follows this logic:
“For all orders, select the current step name from the step table. If the Step Type is equal to ‘XO’, then select the most recently completed (where StepStop is not null) regular step (where StepStop is equal to ‘YY’)”
I have the following query:
SELECT tblOrders.*, tblSteps.StepName FROM tblOrders INNER JOIN tblSteps ON tblOrders.OrderNo = tblSteps.OrderNo AND tblOrders.CurrentStepNo = tblSteps.StepNo
Which successfully returns to me the current step name for an in-process order. What I need to achieve is, when the
tblOrders.CurrentStepNo is of type
'XO', to find the
MAX(tblSteps.StepStop) WHERE tblSteps.StepType = 'YY'. However, I am having trouble putting that logic into my already working query.
Note: I am sorry for the lack of sample data in this example. I would normally post but cannot in this instance. This is also not a homework question.
I have reviewed these references:
But no luck so far.
I have tried this:
SELECT tblOrders.*, CASE WHEN tblSteps.StepType = 'XO' THEN (-- Some logic here) ELSE tblSteps.StepName END AS StepName FROM tblOrders INNER JOIN tblSteps ON tblOrders.OrderNo = tblSteps.OrderNo AND tblOrders.CurrentStepNo = tblSteps.StepNo
But am struggling to properly formulate the logic
Join all steps, rank them with
ROW_NUMBER, and stay with the best ranked:
select * from ( select o.*, s.*, row_number() over (partition by o.orderno order by case when s.steptype <> 'XO' and s.stepno = o.currentstepno then 1 when s.steptype <> 'YY' then 2 else 3 end, s.stepstop desc nulls last) as rn from tblorders o join tblsteps s on s.orderno = o.orderno ) ranked where rn = 1 order by orderno;