I am looking for a SQL Qury/ Sql Statement to set Last week/Last Month QTY sold sale Reorder # in my item table. We have a unique business where reorder level changes every 3-4 months.
If we can run a query for time frame to set qty sold in last week or last month as a reorder point, this will help us a lot
Thanks
this is an example .. and I can complete the example if only I understand your question ...
SELECT ItemLookupCode, ReorderPoint, SUM(lastweek.Quantity) as LastWeek, SUM(lastqy.Quantity) as LastQY FROM Item LEFT JOIN TransactionEntry lastweek ON Item.ID = lastweek.ItemID and lastweek.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) < 2004) LEFT JOIN TransactionEntry lastqy ON Item.ID = lastqy.ItemID and lastqy.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) >= 2004) GROUP BY ItemLookupCode, ReorderPoint
Work on year..
Hello COD:
I am new to SQL. Could you please tell me how do I use "UPDATE" command in this statement.
Like what ever Quantity sold last month is updated in Item table as ReorderPoint.
I am looking for SQL statement When I run will give me for All the item quantity Sold Last Month and Up date the ReorderPoint in my Item Table. And
Also When I run will give me for All the item quantity Sold Last Month Multiply by 2 and Up date the ReorderQuantity.
We want to carry the two month Inventory in stores.
For eg. If I sold 20 Pices of Item Quantity this month I want to UPDATE Item table with ReorderQuantity= 40 and ReorderPoint = 20
Also I like to find out how to enter the proper time and date in SQL Statement. In this example. Could you please provide me the example in this statement.
" SELECT ItemLookupCode, ReorderPoint, SUM(lastweek.Quantity) as LastWeek, SUM(lastqy.Quantity) as LastQY FROM Item LEFT JOIN TransactionEntry lastweek ON Item.ID = lastweek.ItemID and lastweek.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) < 2004) LEFT JOIN TransactionEntry lastqy ON Item.ID = lastqy.ItemID and lastqy.TransactionNumber IN ( SELECT [TransactionNumber] FROM [Transaction] WHERE YEAR([Time]) >= 2004) GROUP BY ItemLookupCode, ReorderPoint "
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.