Solved

SQL Server Query Order Allocation

Posted on 2010-09-09
4
313 Views
Last Modified: 2012-08-13
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
Comment
Question by:yonbret
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33638628
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
 

Author Comment

by:yonbret
ID: 33638828
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
 

Author Closing Comment

by:yonbret
ID: 33639034
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33640127
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question