Querying SQL Server 2016 Part 2




In this exciting Episode, I continue the lecture on querying Sql Server 2016. But in this episode you will not only learn SQL server but you will learn a lot of things. Inventory management, stock cards and basic accounting is a few more things that you will learn in this episode.




Here are the scripts I used for this training.
Step 1. Get the productid and name

SELECT [StockItemID]
,[StockItemName]
FROM [Warehouse].[StockItems]

Step 2 is to join stocks to orderlines (purchase order details)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[PurchaseOrderID]
,[LastReceiptDate]

FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid

Step 3 is to join stocks to purchaseorder (p.O.summary)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[OrderDate]
,PO.[PurchaseOrderID]
,[LastReceiptDate]
,po.[SupplierID]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID

— step 4 is to join stocks to purchaseorder (p.O.summary)

SELECT s.[StockItemID]
,[StockItemName]
,[OrderedOuters]
,[ReceivedOuters]
,[OrderDate]
,PO.[PurchaseOrderID]
,[LastReceiptDate]
,po.[SupplierID]
,[SupplierName]
FROM [Warehouse].[StockItems] s
join [Purchasing].[PurchaseOrderLines] pd
on s.StockItemID=pd.stockitemid
JOIN [Purchasing].[PurchaseOrders] PO
ON PD.PurchaseOrderID=PO.PurchaseOrderID
join [Purchasing].[Suppliers] SP
on po.SupplierID =sp.SupplierID
order by 1

Leave a Reply

Your email address will not be published. Required fields are marked *