September 27, 2016 at 9:31 am
I have a table that houses inventory data by Warehouse, item and a lot#. If a item is a random weight item it includes 2 records one for lbs and one for the case (which is the way we sell it) but I only want to value the inventory by the lbs. I have included a screenshot below:
Warehouse Item QTY UOM UOM Control
F2 1260 328 CS1 S
RF 1260 4080 CS1 S
F1 1260 789 CS1 S
R1 4231 29890 LBS S
F1 833 43 CS5 C
F1 833 1179.94 LBS C
So on item '833' I don't want to include the 43 CS5 record. the UOM Control field is a 'S' or 'C', any field that is a 'C' will have the 2 records like item 833.....any help would be greatly appreciated.....
September 27, 2016 at 9:45 am
shanegair (9/27/2016)
I have a table that houses inventory data by Warehouse, item and a lot#. If a item is a random weight item it includes 2 records one for lbs and one for the case (which is the way we sell it) but I only want to value the inventory by the lbs. I have included a screenshot below:Warehouse Item QTY UOM UOM Control
F2 1260 328 CS1 S
RF 1260 4080 CS1 S
F1 1260 789 CS1 S
R1 4231 29890 LBS S
F1 833 43 CS5 C
F1 833 1179.94 LBS C
So on item '833' I don't want to include the 43 CS5 record. the UOM Control field is a 'S' or 'C', any field that is a 'C' will have the 2 records like item 833.....any help would be greatly appreciated.....
As you 'only want to value the inventory by the lbs', can't you just select the rows where UOM = 'LBS'?
If not, why not?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 27, 2016 at 9:48 am
I only want to value the inventory of LBS on the items with the 'C' for the UOM COntrol. Thanks
September 27, 2016 at 9:55 am
shanegair (9/27/2016)
I only want to value the inventory of LBS on the items with the 'C' for the UOM COntrol. Thanks
I'm not sure whether you think that you've provided sufficient information for a person with no knowledge of your data whatsoever to solve your problem, but your requirement so far
I only want to value the inventory of LBS on the items with the 'C' for the UOM COntrol
Leads to a
SELECT col1, col2
WHERE [UOM Control] = 'C' and UOM = 'LBS'
solution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 27, 2016 at 10:06 am
Sorry I am not relaying clear information. I want all records with UOM_Control = 'S' and only UOM = 'LBS' where UOM_control = 'C'
September 27, 2016 at 10:15 am
shanegair (9/27/2016)
Sorry I am not relaying clear information. I want all records with UOM_Control = 'S' and only UOM = 'LBS' where UOM_control = 'C'
Then logically, you have an OR condition.
...WHERE UOM_Control = 'S'
OR (UOM_Control = 'LBS' AND UOM_Control = 'C')...
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 27, 2016 at 3:33 pm
Why on earth are you duplicating rows in that table instead of just adding a separate column for LBS??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 27, 2016 at 4:14 pm
It is the backside of a purchased ERP system, I am just trying to create some reports....
September 27, 2016 at 4:16 pm
Understood. Thanks for the reply.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply