Approved Workman Database

Community Contributions => SQL Workbench => Topic started by: mluldrich on September 24, 2013, 07:16:49 AM

Title: Order Summary
Post by: mluldrich on September 24, 2013, 07:16:49 AM
I would really like to have a report/screen/SQL that would allow me to see a summary of all the orders placed in a club year.   This would help me to determine if I have enough of certain items to last the club year - even though some have been given out and some have not been earned yet.  right now I keep it on a spreadsheet, which is hard to determine if it's been updated correctly or not.
Title: Re: Order Summary
Post by: Rick Leffler on September 30, 2013, 07:11:38 PM
The Inventory > Order tab does show a list of all ORDERS so you can see how many orders were placed and see the bottom line totals in the grid's footer. However, I think I understand what you're really asking for here is to see a list of the ITEMS on all of the orders.

We can use the SQL Workbench to get a list of the Items.  Copy and paste the following SQL statement and run it:

--------------------

      select Item_Number, Description, sum(Qty_Ordered) as Quantity from Item where Order_ID in (select Order_ID from Order where Order_Date between '2012-9-1' and '2013-5-1') Group by Item_Number

--------------------

Note: Since you wanted to see all the ITEMS order LAST YEAR, I used an example date range of 9/1/2012 to 5/1/2013, if your starting and ending dates are different you can adjust accordingly, but you have to keep the date format of  'yyyy/mm/dd'  as shown above.

Note: The Quantity column is the Order Quantity and therefore does not take into account Package Quantity, but this should be ok as long as you are aware of it.
Title: Re: Order Summary
Post by: Awana@CBVB on October 02, 2013, 07:50:42 PM
Funny...our secretaries were discussing something similar tonight. I'll post a separate thread to ask the question.  Jason
Title: Re: Order Summary
Post by: mluldrich on October 07, 2013, 12:47:47 PM
Exactly what I needed (except the dates :))!  Thank you!  Very helpful!