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:
WarehouseItemQTYUOMUOM Control
F21260328CS1S
RF12604080CS1S
F11260789CS1S
R1423129890LBSS
F183343CS5C
F18331179.94LBSC
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:WarehouseItemQTYUOMUOM Control
F21260328CS1S
RF12604080CS1S
F11260789CS1S
R1423129890LBSS
F183343CS5C
F18331179.94LBSC
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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