Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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?
0
yonbret
Asked:
yonbret
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
;with data as (select t.SalesOrderNumber, t1.OrderQty
         , sum(case when t2.SerialNumber is not null then 1 else 0 end) serials
   from tbSalesOrder t1
   left join tbSerialNumber t2
     on t2.SalesOrderNumber = t1.SalesOrderNumber 
   group by t1.SalesOrderNumber, t1.OrderQty
)
select data.*
  from data
 where data.OrderQty > data.Serials

Open in new window

0
 
yonbretAuthor Commented:
I have the following code, the portion in the parenthesis is running perfect when ran by itself. However, when I run the whole thing, I am getting an error message: No column was specified for column 1 of 'data'.

I am confused why this error is being generated. Any help?
with data as (select LTrim(RTrim(t1.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 t1.SoNo, t1.tranlineNo, t1.QtyOrd
)
select data.*
  from data
 where data.QtyOrd > data.Serials

Open in new window

0
 
yonbretAuthor Commented:
I took the LTrim(RTrim()) out and it worked fine. I move the Trim to the second select statement and got the same result.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now