AcumaticaSQLQueriesAndFunctions/LO23QtyMatchRateTable.sql

IF OBJECT_ID ('dbo.LO23QtyMatchRateTable','V') IS NOT NULL DROP VIEW dbo.LO23QtyMatchRateTable
GO 
CREATE VIEW dbo.LO23QtyMatchRateTable
AS
WITH RateTable AS (
    SELECT 1 AS ref, 0 AS MinAmt, 1000 AS MaxAmt, 1.0 AS ExpectedQty, 90 AS Rate UNION ALL
    SELECT 2, 1001, 2500, 1.5, 90 UNION ALL
    SELECT 3, 2501, 5000, 4.5, 90 UNION ALL
    SELECT 4, 5001, 7500, 4.5, 90 UNION ALL
    SELECT 5, 7501, 10000, 7.5, 90 UNION ALL
    SELECT 6, 10001, 25000, 9.0, 90 UNION ALL
    SELECT 7, 25001, 50000, 10.5, 90 UNION ALL
    SELECT 8, 50001, 99999999999, 10.5, 90
),
ProjectSums AS (
    SELECT 
        b.CompanyID,
        b.ProjectID,
         SUM(case when b.[type] = 'I' then  b.CuryRevisedAmount else 0 end) AS TotalCuryRevisedAmount,
        SUM(CASE WHEN i.InventoryCD = 'L023' THEN b.Qty ELSE 0 END) AS L023Qty
    FROM PMBudget b
    LEFT JOIN InventoryItem i 
        ON b.CompanyID = i.CompanyID AND b.InventoryID = i.InventoryID
    GROUP BY b.CompanyID, b.ProjectID
),
Matched AS (
    SELECT 
        ps.CompanyID,
        ps.ProjectID,
        ps.TotalCuryRevisedAmount,
        ps.L023Qty,
        rt.ExpectedQty
    FROM ProjectSums ps
    JOIN RateTable rt
        ON ps.TotalCuryRevisedAmount BETWEEN rt.MinAmt AND rt.MaxAmt
)
SELECT 
    CompanyID,
    ProjectID,
    TotalCuryRevisedAmount,
    L023Qty,
    ExpectedQty,
    CASE 
        WHEN L023Qty = ExpectedQty THEN 0
        ELSE 1
    END AS MismatchFlag
FROM Matched