I'm looking for help with a SQL query to pull orders from our database with a particular status.

We have an SQL 2008 installation that is the db for our ERP system.  I'm trying to come up with a query (that I can turn into an SSRS report) that will give me a breakdown of the Open Sales Orders (by line within each order) and their status.  I'm VERY new to SQL queries.

I asked a similar question earlier in regards to this same problem, and received some great help.  However it appears that I needed more information, as the query is missing some status indicators.

It looks like the query will be picking data from three tables:

The SalesTable table has fairly predictable fields such as SalesID, CustAccount, SalesType, DocumentStatus (uses values of 0, 3, 4, 5, and 7 to flag the pick as Deleted (0), Not Picked (3), Picked (4), Canceled (5), or Invoiced (7)),  SalesStatus (1=Open order, 2=Delivered, 3=Invoiced, 4=Canceled).
The SalesLine table gives order line detail (SalesID, LineNum, ItemID, QtyRemain (used if only part of the line has been shipped), ShippingDate, SalesQty, etc.)

The query that I am working from joins these two tables on SalesID, looking for orders with a SALESSTATUS = 1 (open) and a DOCUMENT STATUS <> 7 (Invoiced)

SELECT *
FROM dbo.SALESTABLE ST
    INNER JOIN dbo.SALESLINE SL ON ST.SALESID = L.SALESID
WHERE ST.SALESSTATUS = 1 AND ST.DOCUMENTSTATUS <> 7

However, I found that it isn't giving the warehouse quite enough information.  The DocumentStatus field tells whether the items have been 'picked' (collected and brought to a staging area), but not whether they have been 'Packed' (assembled onto a pallet, tagged, and are ready for pickup).

It looks like to do this, I need to incorporate a table called WMSPickingRoute.  It uses a field called EXPEDITIONSTATUS to designate the status of the actual picking process.  3=Pick Activated, 4=Pick Started, 10=Completed, 20=Canceled.  There is a field called TRANSFERID which is the same as the SALESID in the SalesTable table.

My question is, how can I join the data in the WMSPickingRoute table to add the picking status in every instance where the pick has not yet been completed (DOCUMENTSTATUS=3)?


Thank you to Vitor Montalvao for help with the first query, and to anyone who might be able to help me finish it off!

Scott
Scott MilnerApplication AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
>>"I need to incorporate a table called WMSPickingRoute .... There is a field called TRANSFERID which is the same as the SALESID in the SalesTable table."

You just described a JOIN, and all you need to do is translate that into SQL, in bold below.

SELECT *
FROM dbo.SALESTABLE ST
    INNER JOIN dbo.SALESLINE SL ON ST.SALESID = L.SALESID
    INNER JOIN dbo.WMSPickingRoute ES ON ST.SALESID = EL.EXPEDITIONSTATUS
WHERE ST.SALESSTATUS = 1
AND ST.DOCUMENTSTATUS <> 7
AND EL.DOCUMENTSTATUS <> 3

Now for the filtering (where clause) I'm not sure I understand, so I just added that last line.

Basically you should see FEWER ROWS but MORE COLUMNS with this query. Is that what you were expecting?

==== btw:
the best way to explain you needs for a SQL question is:
a. provide "sample data" (for each table), and
b. the "expected result"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott MilnerApplication AdministratorAuthor Commented:
Thanks for the response, Paul, and for the help in how to format my SQL question.  I can see that I wasn't clear at all.  Fortunately, in reviewing what I wrote and thinking hard about the requirements, I can see that I was going about the original question wrong--however, your response seems like it is returning exactly what I need.  The proper request is below.

I am in need of a query that looks at all open sales orders to see if the picking process has begun (this can be determined by whether there is a value in the WMSPickingRoute.EXPEDITIONSTATUS field.  If the order is open, and there is a value in the EXPEDITIONSTATUS field, the row should be returned.

I have three tables which hold the data in question:

dbo.SalesTable
SALESID
SALESNAME (customer name)
SALESSTATUS (order status - 1 = Open Order)
SALESTYPE (order type)

dbo.SalesLine
SALESID
LINENUM (line number for the order)
SALESQTY
ITEMID
REMAINSALESPHYSICAL

dbo.WMSPickingRoute
TRANSREFID (SalesID)
EXPEDITIONSTATUS (Picking status, used to determine the status of an unfilled order)


here is my result using your recommended query...  THANKS!


SELECT
      ST.SALESID,
        ST.SALESTYPE,
      ST.SALESNAME,
      SL.LINENUM,
      SL.ITEMID,
      SL.SALESQTY,
      SL.REMAINSALESPHYSICAL,
      WMS.EXPEDITIONSTATUS,
      ST.SALESSTATUS,
FROM dbo.SALESTABLE ST
      INNER JOIN dbo.SALESLINE SL ON ST.SALESID = SL.SALESID
      INNER JOIN dbo.WMSPICKINGROUTE WMS ON ST.SALESID = WMS.TRANSREFID
WHERE ST.SalesStatus = 1
0
Scott MilnerApplication AdministratorAuthor Commented:
Your solution worked perfectly, and your explanation helped a bunch.  I appreciate the advice for future questions.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

PortletPaulfreelancerCommented:
Its a pleasure.
0
Scott MilnerApplication AdministratorAuthor Commented:
Mind if I ask a follow-up, or would you prefer it to be in another question (more points for you that way!)

I've taken what you gave me and revised it somewhat, but it now seems that I'm getting some duplicate rows...
0
PortletPaulfreelancerCommented:
:)
it should be a new question really that way you get multiple experts vying for your attention but if it's quick I'm willing.
0
Scott MilnerApplication AdministratorAuthor Commented:
I totally understand... actually, I think I figured it out.  I was getting what I thought at first were duplicate lines, but in actuality they had a couple of fields that were slightly different.

I added a MAX(CREATEDDATETIME) aggregator to select the most recent of the duplicate transactions, and things look better.

I appreciate your help!
0
PortletPaulfreelancerCommented:
ok.

one great way of getting "the most recent" rows is to use row_number() over()

this is a wild guess just as a demo


FROM dbo.SALESTABLE ST
      INNER JOIN dbo.SALESLINE SL ON ST.SALESID = SL.SALESID
      INNER JOIN (
               select *
, ROW_NUMBER() OVER(PARTITION BY WMS.TRANSREFID ORDER BY CREATEDDATETIME DESC) AS RN
              dbo.WMSPICKINGROUTE
           ) WMS ON ST.SALESID = WMS.TRANSREFID AND WMS.RN = 1
WHERE ST.SalesStatus = 1
1
Scott MilnerApplication AdministratorAuthor Commented:
That does seem like a lot less work than the MAX aggregation and then having to work out the associated Group By.

I'll look up the command to try to get an understanding of your example... thanks for the tip!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.