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)
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!