I've got a problem with porting my MS Access database to SQL Server. In my inventory database, I have 4 tables: (1)Purchase, (2)Sales, (3)Quantity Adjustment, (4)Amount Adjustment. I want to calculate the stock position from those 4 tables. In access database, I do with this kind of queries:
a. parameterized query to filter each table and union it all and I name it 'qryStockPos2':
SELECT Product, Quantity FROM tbPurchase WHERE PurchaseDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbSales WHERE SalesDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbQtyAdj WHERE QtyAdjDate<=[selDate]
UNION ALL SELECT Product, Quantity FROM tbAmountAdj WHERE AmountAdjDate<=[selDate]
b. select query based on the first query:
SELECT Product, Sum(Quantity) FROM qryStockPos2 GROUP BY Product;
Please help me how to port those queries into sql server stored procedures. Thanks in advance.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!