February 14, 2011 at 12:12 pm
I have an Item table which holds dimensional information. Previously getting the data from the table was as simple as:
SELECT ItemId, LengthQty, WidthQty, HeightQty FROM dbo.Item
However, I now have the requirement to normalize the data. In other words, rotate any required items so the the largest dimension is the length, the next is the width, and the smallest is the height. So if a row had the dimensions of 1, 2, 3, the select should return 3, 2, 1.
This code seems to work, but just wondering if there was an easier way:
SELECT ItemId
,CASE WHEN (HeightQty > WidthQty) AND (HeightQty > LengthQty)
THEN HeightQty
ELSE
CASE WHEN (WidthQty > HeightQty) AND (WidthQty > LengthQty)
THEN WidthQty
ELSE LengthQty
END
END
AS LengthQty
,CASE WHEN ( (LengthQty < WidthQty) AND (LengthQty > HeightQty)
OR (LengthQty > WidthQty) AND (LengthQty < HeightQty) )
THEN LengthQty
ELSE
CASE WHEN ( (HeightQty < LengthQty) AND (HeightQty > WidthQty)
OR (HeightQty > LengthQty) AND (HeightQty < WidthQty) )
THEN HeightQty
ELSE WidthQty
END
END
AS WidthQty
,CASE WHEN (LengthQty < WidthQty) AND (LengthQty < HeightQty)
THEN LengthQty
ELSE
CASE WHEN (WidthQty < LengthQty) AND (WidthQty < HeightQty)
THEN WidthQty
ELSE HeightQty
END
END
AS HeightQty
FROM dbo.Item
-Matt
February 14, 2011 at 12:20 pm
I could think of a few ways, such as doing an UNPIVOT on the data, assigning row_numbers partitioning on ID and ordering by the value, but they're no less complex or prettier code, nor would I guarantee they were faster.
If this was something you needed to do regularly we could take a look at speed testing things, but this sounds like a one-off task. Is that assumption correct?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 12:31 pm
Thanks for the reply, and your right none of that sounds easier.
Right now it isn't a one off, but if speed becomes and issue I might need to force it to be as close to a one off as I can.
The current application is for fitting 1 to X SKU items into a shipping carton, so it would hit this logic quite a bit. The item "master data" is contain in another system and is passed to me in an interface. The host system can change or add items data at any time. Right now I only put > 0 checks on their data coming in, but if the above code turns out to be two slow, then I might normalize it on the inbound rather then the outbound to my code. At least the inbound is rather infrequent, where the outbound would be hit much harder. If speed isn't a factor, then I would prefer to display their dimensional data in my reports in the format as they present it to me.
February 14, 2011 at 12:38 pm
Would it be an option to add those three calculations as computed persisted columns?
It came to my mind when you stated the inbound is infrequent...
February 14, 2011 at 12:55 pm
Hmmm, interesting idea. I haven't used computed persistant columns much so I'll have to look into that more, however it would my my queries much simplier again.
Quick question, can you have IF ELSE or CASE statements as part of a computed column? So for example, I have normal original columns of LengthQty, WidthQty, and HeightQty and I could create 3 computed columns such as NormalizedLength, NormalizedWidth and NormalizedHeight. However the one time I used computed column it was to actually do an addition. This would be more complex because I would want the largest value of LengthQty, WidthQty, or HeightQty to be placed in the NormalizedLength column.
February 14, 2011 at 1:02 pm
Matt_V (2/14/2011)
Hmmm, interesting idea. I haven't used computed persistant columns much so I'll have to look into that more, however it would my my queries much simplier again.Quick question, can you have IF ELSE or CASE statements as part of a computed column? So for example, I have normal original columns of LengthQty, WidthQty, and HeightQty and I could create 3 computed columns such as NormalizedLength, NormalizedWidth and NormalizedHeight. However the one time I used computed column it was to actually do an addition. This would be more complex because I would want the largest value of LengthQty, WidthQty, or HeightQty to be placed in the NormalizedLength column.
You should be able to use the statement you posted earlier.
February 14, 2011 at 1:08 pm
Matt_V (2/14/2011)
Hmmm, interesting idea. I haven't used computed persistant columns much so I'll have to look into that more, however it would my my queries much simplier again.Quick question, can you have IF ELSE or CASE statements as part of a computed column? So for example, I have normal original columns of LengthQty, WidthQty, and HeightQty and I could create 3 computed columns such as NormalizedLength, NormalizedWidth and NormalizedHeight. However the one time I used computed column it was to actually do an addition. This would be more complex because I would want the largest value of LengthQty, WidthQty, or HeightQty to be placed in the NormalizedLength column.
Another idea would be to use an indexed view, which would only need to be updated when the source data was updated. You then could overload the field names to the rest of your queries so when the original data finally (if ever) gets cleaned up, you could simply modify the view with little effort and the rest of the task would pass through to the source table indexing.
Also, just my sense of astetics, but don't use Normalized<x> in this case. Normalization usually means something very different and it will be confusing to anyone who inherits the system later. I know what you mean (now), you know what you mean, but blind access I'd be confused. I'd be wondering why the length/width/height wasn't split to 3 rows. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 1:24 pm
Another idea would be to use an indexed view, which would only need to be updated when the source data was updated. You then could overload the field names to the rest of your queries so when the original data finally (if ever) gets cleaned up, you could simply modify the view with little effort and the rest of the task would pass through to the source table indexing.
Also, just my sense of astetics, but don't use Normalized<x> in this case. Normalization usually means something very different and it will be confusing to anyone who inherits the system later. I know what you mean (now), you know what you mean, but blind access I'd be confused. I'd be wondering why the length/width/height wasn't split to 3 rows. 😉
Ah yes, must be one of those days :w00t: I come from the C++. C# background and have only recently gotten more into the data access side of things and the word "Normalization" just popped into mind with no regards to the consequense that might create with a strong SQL presective.
Thanks for everyones thoughts. Some great ideas for me moving forward.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply