Querying SQL Server 2016 Part 3




In this episode we will complete the mini project to build a stock card for “Widewordimporters” Database. We will use join combine the data from different tables and union to merge the result set from different queries. Order by sorts the data by column positions in this demo.

Should you want to follow the examples in this training “worldwideimporters” database is found on this link.

to




Here are the queries that I have used.

— query from part 2
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

I rearrange the select list

SELECT s.[StockItemID]
,[StockItemName]
,[OrderDate] as transactiondate
,’purchase’ as transactiontype
,po.[PurchaseOrderID]
,[OrderedOuters]
,[ReceivedOuters]
,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

— copy the select list but use null if the data is not available. Start doing the Sales query
–starting again from the stock items

SELECT
s.[StockItemID]
,[StockItemName]
,null transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]

FROM [warehouse].[StockItems] s

— get the sales transaction

SELECT
s.[StockItemID]
,[StockItemName]
,pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] so
on s.StockItemID=so.StockItemID

–select top 1 * from [Sales].[OrderLines] so
— get the customername

SELECT
s.[StockItemID]
,[StockItemName]
,so.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid

Add the sales quantity

SELECT
s.[StockItemID]
,[StockItemName]
,sol.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,null as customername
,quantity as salesquantity
,customername
FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID

–combine the purchase and sales data using union. they must have the same select list

SELECT s.[StockItemID]
,[StockItemName]
,[OrderDate] as transactiondate
,’purchase’ as transactiontype
,po.[PurchaseOrderID]
,[OrderedOuters]
,[ReceivedOuters]
,po.[SupplierID]
,[SupplierName]
,null as salesorderid
, null as customername
,null as salesquantity
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

union

SELECT
s.[StockItemID]
,[StockItemName]
,sol.pickingcompletedwhen transactiondate
,’Sales’ as transactiontype
,null as [PurchaseOrderID]
,null [OrderedOuters]
,null [ReceivedOuters]
,null [SupplierID]
,null [SupplierName]
,so.OrderID
,customername as customername
,quantity as salesquantity

FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID

order by 1, 3

add the ending inventory on the balance

SELECT s.[StockItemID]
    ,[StockItemName]
    ,[OrderDate] as transactiondate
    ,’purchase’ as transactiontype
    ,po.[PurchaseOrderID]
    ,[OrderedOuters]
    ,[ReceivedOuters]
    ,po.[SupplierID]
    ,[SupplierName]
    ,null as salesorderid
    , null as customername
    ,null as salesquantity
    ,null  [QuantityOnHand]
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

union

SELECT
     s.[StockItemID]
    ,[StockItemName]
    ,sol.pickingcompletedwhen transactiondate
    ,’Sales’ as transactiontype
    ,null as [PurchaseOrderID]
    ,null [OrderedOuters]
    ,null [ReceivedOuters]
    ,null [SupplierID]
    ,null [SupplierName]
    ,so.OrderID
    ,customername as customername
    ,quantity as salesquantity
    ,null  [QuantityOnHand]
    
FROM [warehouse].[StockItems] s
left join [Sales].[OrderLines] sol
on s.StockItemID=sol.StockItemID
left join
[Sales].[Orders] so
on sol.OrderID=so.orderid
join [Sales].[Customers] c
on so.CustomerID=c.CustomerID
union
SELECT
     s.[StockItemID]
    ,s.[StockItemName]
    ,[LastEditedWhen] transactiondate
    ,’ending inventory’ as transactiontype
    ,null as [PurchaseOrderID]
    ,null [OrderedOuters]
    ,null [ReceivedOuters]
    ,null [SupplierID]
    ,null [SupplierName]
    ,null OrderID
    ,null customername
    ,null salesquantity
    , [QuantityOnHand]
    
FROM [warehouse].[StockItems] s
join[Warehouse].[StockItemHoldings] sh
on s.StockItemID=sh.stockitemid
order by 1, 3




Leave a Reply

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