May 14, 2014 at 2:31 pm
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
actionKindCode nvarchar(40),
sortingkey int,
LastRealisedActionID int,
id_resource int)
insert into @mytable values
(4609,139,'pickup',1,153769,4),
(4613,139,'pickup',2,153769,4),
(4614,139,'pickup',3,153769,4),
(4617,139,'deliver',4,153769,4),
(4621,139,'deliver',5,153769,4),
(4622,139,'deliver',6,153769,4),
(4650,141,'pickup',1,153769,4),
(4654,141,'pickup',2,153769,4),
(4655,141,'pickup',3,153769,4),
(4658,141,'deliver',4,153769,4),
(4662,141,'deliver',5,153769,4),
(4663,141,'deliver',6,153769,4),
(4665,141,'clean',7,153769,4),
(153769,1457,'section_decoupling',1,153769,4),
(8215,376,'pickup',1,66545,96),
(8242,376,'deliver',2,66545,96),
(8248,376,'clean',3,66545,96),
(9730,472,'pickup',1,66545,96),
(9757,472,'deliver',2,66545,96),
(9763,472,'clean',3,66545,96),
(34540,1465,'pickup',1,66545,96),
(66545,1465,'section_decoupling',2,66545,96),
(9152,437,'pickup',1,23991,901),
(23995,1113,'deliver',1,23991,901),
(24000,1113,'clean',2,23991,901),
(23991,1113,'section_decoupling',3,23991,901),
(31530,1365,'deliver',1,23991,901),
(59860,1365,'clean',2,23991,901),
(35602,1540,'pickup',1,23991,901),
(65002,2242,'pickup',1,23991,901),
(65112,2247,'pickup',1,23991,901),
(72379,2473,'pickup',1,23991,901),
(72644,2489,'pickup',1,23991,901)
;with mycte as (
select *, ROW_NUMBER() over(partition by id_resource order by id_shift, sortingkey) as rownr
from @mytable)
SELECT id_action, id_resource,actionKindCode
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID_resource ORDER BY rownr desc) as rn
FROM (select cte1.*
from mycte cte1
join mycte cte2 on cte1.id_resource = cte2.id_resource
and cte1.rownr <= cte2.rownr
and cte2.id_action = cte2.LastRealisedActionID
and cte1.actionKindCode in ('pickup','deliver','clean')
) q
) previousrealisedaction
WHERE
previousrealisedaction.rn = 1
May 18, 2014 at 7:33 am
Here's a slightly different approach using CROSS APPLY:
;WITH cte as
(
SELECT id_resource, max(id_shift) as last_shift
FROM @mytable
WHERE actionKindCode='section_decoupling'
GROUP BY id_resource
)
SELECT x.id_action, x.id_resource, x.actionKindCode
FROM cte
CROSS APPLY
(
SELECT TOP 1 id_action, id_resource, actionKindCode
FROM @mytable mt
WHERE mt.id_resource = cte.id_resource
AND mt.id_shift <=cte.last_shift
AND actionKindCode<>'section_decoupling'
ORDER BY mt.id_shift desc, mt.sortingkey DESC
)x
May 18, 2014 at 9:48 am
Before I explore any alternatives can you explain how your code works to create the given results based on the sample data?
I'm not sure what you are actually trying to accomplish. Lutz's code doesn't really help with understanding as differs enough from yours that it actually clouds things still.
May 18, 2014 at 10:13 am
I'm sorry Lynn for not providing any addtl. information how I ended up with the solution I posted....
Here's the "story behind":
I looked at the result set based on the sample data and figured it's always the last action before actionKindCode='section_decoupling'.
Therefore, I used the cte to get the highest value of id_shift for this actionKindCode and used CROSS APPLY to find the row before that actionKindCode.
It's all based on guessing and reverse-engineering...
The result set is identical.
When comparing the logic again it seems like the cte needs a different WHERE clause (and I also changed the actionKindCodein comparison to match the original quer):
;WITH cte as
(
SELECT id_resource, id_shift as last_shift
FROM @mytable
WHERE id_action = LastRealisedActionId --actionKindCode='section_decoupling'
)
SELECT x.id_action, x.id_resource, x.actionKindCode
FROM cte
CROSS APPLY
(
SELECT TOP 1 id_action, id_resource, actionKindCode
FROM @mytable mt
WHERE mt.id_resource = cte.id_resource
AND mt.id_shift <=cte.last_shift
AND actionKindCodein IN ('pickup','deliver','clean') -- equivalent to <>'section_decoupling'
ORDER BY mt.id_shift desc, mt.sortingkey DESC
)x
May 19, 2014 at 4:15 am
LutzM (5/18/2014)
I'm sorry Lynn for not providing any addtl. information how I ended up with the solution I posted....Here's the "story behind":
I looked at the result set based on the sample data and figured it's always the last action before actionKindCode='section_decoupling'.
Therefore, I used the cte to get the highest value of id_shift for this actionKindCode and used CROSS APPLY to find the row before that actionKindCode.
It's all based on guessing and reverse-engineering...
The result set is identical.
When comparing the logic again it seems like the cte needs a different WHERE clause (and I also changed the actionKindCodein comparison to match the original quer):
;WITH cte as(
SELECT id_resource, id_shift as last_shift
FROM @mytable
WHERE id_action = LastRealisedActionId --actionKindCode='section_decoupling'
)
SELECT x.id_action, x.id_resource, x.actionKindCode
FROM cte
CROSS APPLY
(
SELECT TOP 1 id_action, id_resource, actionKindCode
FROM @mytable mt
WHERE mt.id_resource = cte.id_resource
AND mt.id_shift <=cte.last_shift
AND actionKindCodein IN ('pickup','deliver','clean') -- equivalent to <>'section_decoupling'
ORDER BY mt.id_shift desc, mt.sortingkey DESC
)x
Thanks for your suggestion Lutz.
It's not the actionKindCode='section_decoupling' but id_action=lastRealisedActionId. I changed this in your suggestion as per below. I think the cross apply construction is more readable. I have to check for performance as the original reason of rewriting a complex lengthy ugly query was performance. I'll report back on it.
;WITH cte as
(
SELECT id_resource, max(id_shift) as last_shift
FROM @mytable
WHERE id_action<>LastRealisedActionID
GROUP BY id_resource
)
SELECT x.id_action, x.id_resource, x.actionKindCode
FROM cte
CROSS APPLY
(
SELECT TOP 1 id_action, id_resource, actionKindCode
FROM @mytable mt
WHERE mt.id_resource = cte.id_resource
AND mt.id_shift <=cte.last_shift
AND id_action<>LastRealisedActionID
ORDER BY mt.id_shift desc, mt.sortingkey DESC
)x
May 19, 2014 at 8:12 am
Well, I hope you got what you wanted. I still can't quite figure out how you get your expected results from your sample data as your code really isn't documented real nor is the logic of it readily deduced. I hope that you have documented this well else where so that if you need to make changes 6 months or a year from now you will understand what is going on, or should someone new to the organization have to modify it and you aren't around to help.
May 19, 2014 at 8:34 am
Lynn Pettis (5/19/2014)
Well, I hope you got what you wanted. I still can't quite figure out how you get your expected results from your sample data as your code really isn't documented real nor is the logic of it readily deduced. I hope that you have documented this well else where so that if you need to make changes 6 months or a year from now you will understand what is going on, or should someone new to the organization have to modify it and you aren't around to help.
I realize that the logic is not clear or that I could not make it clear completely. The database is for a planning tool. Trucks, drivers and orders are optimized for efficiency and cost minimalization. The datamodel is very complex and can deal with many different business cases. I have already stripped the problem from a lot of complexity to an abstracted level.
This complexity is a problem indeed as not every detail is documented and there are quite a few people that know the ins and outs but also people struggling to get the finesses of the model, myself included.
Lutz' answer has given me another option and good understanding.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply