yonbret
asked on
SQL Server Query Order Allocation
I have two SQL Server database tables.
table: tbSalesOrder
columns: SalesOrderNumber, OrderQty
table: tbSerialNumber
columns: SerialNumber, SalesOrderNumber (Sales Order to which the Serial Number is assigned)
I want to return a list of Sales Order Numbers where the Order Qty is less than the assigned Serial Numbers for that Sales Order. Basically, I need a list of Sales Orders that do not have enough Serial Numbers assigned to fill the order.
Serial Numbers can only be assigned to one Sales Order and Sales Orders can be assigned to more than one Serial Number.
Any help with the T-SQL to make this happen?
table: tbSalesOrder
columns: SalesOrderNumber, OrderQty
table: tbSerialNumber
columns: SerialNumber, SalesOrderNumber (Sales Order to which the Serial Number is assigned)
I want to return a list of Sales Order Numbers where the Order Qty is less than the assigned Serial Numbers for that Sales Order. Basically, I need a list of Sales Orders that do not have enough Serial Numbers assigned to fill the order.
Serial Numbers can only be assigned to one Sales Order and Sales Orders can be assigned to more than one Serial Number.
Any help with the T-SQL to make this happen?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I took the LTrim(RTrim()) out and it worked fine. I move the Trim to the second select statement and got the same result.
to fix that error:
with data as (select LTrim(RTrim(t1.SONo)) SoNo, t1.tranlineNo, t1.QtyOrd
, sum(case when t2.SerialNumber is not null then 1 else 0 end) serials
from SageProSOTRAN01 t1
left join tbSerialNumberShipment t2
on LTrim(RTrim(t1.SONo)) = t2.SalesOrder and t1.tranlineno = t2.LineNumber
group by LTrim(RTrim(t1.SONo)), t1.tranlineNo, t1.QtyOrd
)
select data.*
from data
where data.QtyOrd > data.Serials
ASKER
I am confused why this error is being generated. Any help?
Open in new window