Complex SQL Query similar to a z order problem
- by AaronLS
I have a complex SQL problem in MS SQL Server, and in drawing on a piece of paper I realized that I could think of it as a single bar filled with rectangles, each rectangle having segments with different Z orders. In reality it has nothing to do with z order or graphics at all, but more to do with some complex business rules that would be difficult to explain. Howoever, if anyone has ideas on how to solve the below that will give me my solution.
I have the following data:
ObjectID, PercentOfBar, ZOrder (where smaller is closer)
A, 100, 6
B, 50, 5
B, 50, 4
C, 30, 3
C, 70, 6
The result of my query that I want is this, in any order:
PercentOfBar, ZOrder
50, 5
20, 4
30, 3
Think of it like this, if I drew rectangle A, it would fill 100% of the bar and have a z order of 6.
66666666666
AAAAAAAAAAA
If I then layed out rectangle B, consisting of two segments, both segments would cover up rectangle A resulting in the following rendering:
4444455555
BBBBBBBBBB
As a rule of thumb, for a given rectangle, it's segments should be layed out such that the highest z order is to the right of the lower Z orders.
Finally rectangle C would cover up only portions of Rectangle B with it's 30% segment that is z order 3, which would be on the left. You can hopefully see how the is represented in the output dataset I listed above:
3334455555
CCCBBBBBBB
Now to make things more complicated I actually have a 4th column such that this grouping occurs for each key:
Input:
SomeKey, ObjectID, PercentOfBar, ZOrder (where smaller is closer)
X, A, 100, 6
X, B, 50, 5
X, B, 50, 4
X, C, 30, 3
X, C, 70, 6
Y, A, 100, 6
Z, B, 50, 2
Z, B, 50, 6
Z, C, 100, 5
Output:
SomeKey, PercentOfBar, ZOrder
X, 50, 5
X, 20, 4
X, 30, 3
Y, 100, 6
Z, 50, 2
Z, 50, 5
Notice in the output, the PercentOfBar for each SomeKey would add up to 100%.
This is one I know I'm going to be thinking about when I go to bed tonight.
Just to be explicit and have a question:
What would be a query that would produce the results described above?
