-- Link to download the WideWorldImportersDW database
-- https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImportersDW-Full.bak

-- Fact Transaction Table
-- This query retrieves sales transaction data from the Fact.Order table.
-- It includes details such as order dates, stock items, quantities, customer invoices, and delivery status.
-- The CASE statement categorizes deliveries as 'On Time', 'Late', or 'Not Delivered Yet'.

SELECT FO.[Order Date Key],
        FO.[Picked Date Key],
        FO.[WWI Order ID],
        FO.[Stock Item Key],
        FO.Quantity,
        FO.[Total Including Tax],
        FO.[WWI Backorder ID],
        SI.OrderID,
        SI.InvoiceID,
        FS.[Customer Key],
        FS.[Invoice Date Key],
        FS.[Delivery Date Key],
        DATEDIFF(DAY, FO.[Order Date Key], FS.[Delivery Date Key]) AS OrderToDeliveryLeadTime,
        SO.ExpectedDeliveryDate,
        CASE
            WHEN SO.ExpectedDeliveryDate >= FS.[Delivery Date Key] THEN 'On Time Delivery'
            WHEN FS.[Delivery Date Key] IS NULL THEN 'Not Delivered Yet'
            ELSE 'Late Delivery'
        END AS [Delivery Status]
FROM [Fact].[Order] FO 
LEFT JOIN [WideWorldImporters].[Sales].[Invoices] SI
    ON FO.[WWI Order ID] = SI.OrderID
LEFT JOIN [Fact].[Sale] FS
    ON SI.InvoiceID = FS.[WWI Invoice ID] 
    AND FO.[Stock Item Key] = FS.[Stock Item Key]
LEFT JOIN [WideWorldImporters].[Sales].[Orders] SO
    ON FO.[WWI Order ID] = SO.OrderID
ORDER BY FO.[WWI Order ID];

-- Products Table
-- Retrieves details about stock items, including pricing, tax rate, and weight.
-- Excludes the default stock item key (0) to ensure only valid items are shown.

SELECT [Stock Item Key],
        [WWI Stock Item ID],
        [Stock Item],
        [Lead Time Days],
        [Quantity Per Outer],
        [Is Chiller Stock],
        [Tax Rate],
        [Unit Price],
        [Recommended Retail Price],
        [Typical Weight Per Unit]
FROM [Dimension].[Stock Item]
WHERE [Stock Item Key] != 0;

-- Stock Holding Table
-- Retrieves stock holding details from the Fact.StockHolding table.
-- This table tracks inventory levels over time.

SELECT * 
FROM [Fact].[Stock Holding];

-- Supplier Performance
-- Evaluates supplier reliability by counting complete and incomplete purchases.
-- A complete purchase means the received quantity matches or exceeds the ordered quantity.

SELECT s.Supplier, 
        COUNT(CASE WHEN p.[Received Outers] >= p.[Ordered Outers] THEN 'Complete' END) AS [Complete Purchase], 
        COUNT(CASE WHEN p.[Received Outers] < p.[Ordered Outers] THEN 'Incomplete' END) AS [Incomplete Purchase],
        COUNT(*) AS Total
FROM [Fact].[Purchase] p
LEFT JOIN [Dimension].[Supplier] s
    ON p.[Supplier Key] = s.[Supplier Key]
GROUP BY s.Supplier;

-- Supplier Invoice Duration
-- Calculates the time taken to finalize supplier invoices from the transaction date.
-- Helps assess supplier payment efficiency and compare it with expected payment terms.
-- Uses explicit database schema references for clarity and to access information outside the current working database.

SELECT s.SupplierName,
        st.TransactionDate AS [Invoice Date],
        st.FinalizationDate AS [Payment Date],
        DATEDIFF(DAY, st.TransactionDate, st.FinalizationDate) AS [Duration before Payment],
        po.PaymentDays
FROM [WideWorldImporters].[Purchasing].[SupplierTransactions] st
LEFT JOIN [WideWorldImporters].[Purchasing].[PurchaseOrders] po
ON st.PurchaseOrderID = po.PurchaseOrderID
LEFT JOIN [WideWorldImporters].[Application].[TransactionTypes] tt
ON st.TransactionTypeID = tt.TransactionTypeID
LEFT JOIN [WideWorldImporters].[Purchasing].[Suppliers] s
ON st.SupplierID = s.SupplierID
WHERE po.OrderDate IS NOT NULL AND st.FinalizationDate IS NOT NULL;