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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply