May 7, 2012 at 3:02 pm
I need to select some data based on a weight and a unit of measure. However, the unit of measure will not always match so I need to convert the data. For example, @gross_weight = 90,000 and @gross_weight_um = LB so it would bring back my ('ABC', '123', 80000, 'LB') row. But, I also need it to bring back this row values ('ABC', '123', 40, 'STN') because 90,00 LBS > 40 Shot tons (80,000 LBS). I have a conversion function but I don't know how to put everything in 1 select - is there a way?
DECLARE @Sample TABLE
(origin_code char(5) NOT NULL,
over_weight_qty numeric(13, 4) NOT NULL,
over_weight_um char(3) NOT NULL)
INSERT @Sample
values ('ABC', '123', 40, 'STN')
('ABC', '123', 80000, 'LB')
('ABC', '123', 0, 'LB')
SELECT origin_code,
over_weight_qty,
over_weight_um
FROM @Sample
where (origin_code = @origin_code OR origin_code = 'ALL')
AND (over_weight_qty >= @gross_weight OR (over_weight_qty IS NULL OR over_weight_qty = 0))
AND (over_weight_um = @gross_weight_um OR (over_weight_um IS NULL OR over_weight_um = ''))
May 8, 2012 at 3:03 am
I don't really think I understand what your requirement is. But, I came up with something. Hope it helps you:
Select b.origin_code, b.Id, b.over_weight_qty, b.over_weight_um From
(Select Origin_Code, Id,
(Case When Over_weight_um = 'LB' then Over_Weight_qty
When Over_weight_um = 'STN' then (Over_Weight_qty*2000)
Else ''
End) As Over_Weight_qty, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum
From Sample) As a
JOIN
(Select *, ROW_NUMBER() Over (Order By (Select NULL) ) As rownum From Sample) As b
ON a.rownum = b.rownum
Where a.Over_Weight_qty < 90000
You really need to add an Id field to your table or else it would lead to really complex situations. The above query could have been easier with an Id field already present in your table.
May 8, 2012 at 7:37 am
Your table DDL and insert statements columns don't match, but it looks like you simply need something like this in the WHERE clause:
WHERE (origin_code = @origin_code OR origin_code = 'ALL')
AND @gross_weight <
CASE
WHEN over_weight_um = 'LB' THEN over_weight_qty
WHEN over_weight_um = 'STN' THEN over_weight_qty * 2000
END
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 8, 2012 at 10:25 am
If you have enough units of measure, you'll probably want to create a conversion table instead of using the CASE Statement. Something like the following.
DECLARE @Sample TABLE
(origin_code char(5) NOT NULL,
some_code char(3) NOT NULL,
over_weight_qty numeric(13, 4) NOT NULL,
over_weight_um char(3) NOT NULL)
INSERT @Sample
values ('ABC', '123', 40, 'STN')
,('ABC', '123', 80000, 'LB')
,('ABC', '123', 0, 'LB')
DECLARE @Conversions TABLE(
UnitOfMeasure CHAR(3) NOT NULL UNIQUE
,ConversionToLbs INT NOT NULL
)
INSERT @Conversions(UnitOfMeasure, ConversionToLbs)
VALUES( 'STN', 2000)
, ( 'LB', 1)
SELECT s.over_weight_qty * c.ConversionToLbs, *
FROM @Sample AS s
INNER JOIN @Conversions AS c
ON s.over_weight_um = c.UnitOfMeasure
The conversion table is also likely to perform better than the conversion function that you mentioned.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 8, 2012 at 10:44 pm
drew.allen (5/8/2012)
If you have enough units of measure, you'll probably want to create a conversion table instead of using the CASE Statement.Drew
I agree with Drew.
Since you have got some time here, you should get some things right with your tables:
1. Add Id field to your tables.
2. Have a Conversion Table(as Drew said).
This would help you in avoiding a lot of problems you might run into with your current Design.
May 8, 2012 at 10:58 pm
toddasd (5/8/2012)
Your table DDL and insert statements columns don't match, but it looks like you simply need something like this in the WHERE clause:
WHERE (origin_code = @origin_code OR origin_code = 'ALL')
AND @gross_weight <
CASE
WHEN over_weight_um = 'LB' THEN over_weight_qty
WHEN over_weight_um = 'STN' THEN over_weight_qty * 2000
END
Todd's Logic is very nice. Didn't hit me when I was trying it.
But it was just lacking something.
I tweaked it a little and got it working. 😀 Here's how it goes:
--Creating Table
Create TABLE Sample
(origin_code char(5) NOT NULL,
Id int,--==========Added Id as missing field because the sample data has four values
over_weight_qty numeric(13, 4) NOT NULL,
over_weight_um char(3) NOT NULL)
--Inserting Sample Data
INSERT Sample values ('ABC', '123', 40, 'STN')
INSERT Sample values ('ABC', '123', 80000, 'LB')
INSERT Sample values ('ABC', '123', 0, 'LB')
--Required Query(Originally posted by: Toddasd)
Select * From Sample
WHERE over_weight_qty <
(CASE
WHEN over_weight_um = 'LB' THEN 90000
WHEN over_weight_um = 'STN' THEN 45
Else NULL
END)
May 9, 2012 at 8:26 am
vinu512 (5/8/2012)
Todd's Logic is very nice. Didn't hit me when I was trying it.
But it was just lacking something.
I tweaked it a little and got it working. 😀 Here's how it goes:
Frankly, that is not an improvement. Todd's solution was general, because it referenced column names and parameters wherever possible. You have replaced those columns and parameters with hard-coded values, turning the general solution into a specific solution. Todd's solution will continue to work as the values change, but your solution will have to be re-written as the values change.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 9, 2012 at 10:41 pm
drew.allen (5/9/2012)
vinu512 (5/8/2012)
Todd's Logic is very nice. Didn't hit me when I was trying it.
But it was just lacking something.
I tweaked it a little and got it working. 😀 Here's how it goes:
Frankly, that is not an improvement. Todd's solution was general, because it referenced column names and parameters wherever possible. You have replaced those columns and parameters with hard-coded values, turning the general solution into a specific solution. Todd's solution will continue to work as the values change, but your solution will have to be re-written as the values change.
Drew
New Resolution for Vinu: Read every thread thoroughly before posting. 😀
I missed the @gross_weight = 90000 and @gross_weight_um = 'LB' from the OP's original post. That is why I couldn't understand the @gross_weight in Todd's query.
But, you know what?? There is still a small correction in Todd's query Drew:
--Creating Table
Create TABLE Sample
(origin_code char(5) NOT NULL,
Id int,--==========Added Id as missing field because the sample data has four values
over_weight_qty numeric(13, 4) NOT NULL,
over_weight_um char(3) NOT NULL )
--Inserting Sample Data
INSERT Sample values ('ABC', '123', 40, 'STN')
INSERT Sample values ('ABC', '123', 80000, 'LB')
INSERT Sample values ('ABC', '123', 0, 'LB')
--Todd's Query
Declare @gross_weight numeric = 90000
Declare @gross_weight_um varchar(5) = 'LB'
Select * From Sample
WHERE (origin_code = origin_code OR origin_code = 'ALL')
AND @gross_weight <
CASE
WHEN over_weight_um = 'LB' THEN over_weight_qty
WHEN over_weight_um = 'STN' THEN over_weight_qty * 2000
END
--Correction
Declare @gross_weight numeric = 90000
Declare @gross_weight_um varchar(5) = 'LB'
Select * From Sample
WHERE (origin_code = origin_code OR origin_code = 'ALL')
AND @gross_weight >
CASE
WHEN over_weight_um = 'LB' THEN over_weight_qty
WHEN over_weight_um = 'STN' THEN over_weight_qty * 2000
END
The "@gross_weight <" should be replaced with "@gross_weight >". Right?? :unsure:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply