Data Validation Via SQL Script

Question

I'm looking for a little assistance here writing a SELECT statement that will also do some data validation for me.

Here's an example of the table:

Invoice Line ID	Invoice No	Line Item Cost (Collect USD)	Total Invoice Amount
1	VIK123	1000	1100
2	VIK123	100	1100
1	VIK456	2000	2200
2	VIK456	200	2200

I want a line of code that will sum up Line Item Cost (Collect USD) for Invoice Line IDs 1 & 2 for Invoice No VIK123 and compare this to the value in Total Invoice Amount. My thought is that it'll be a case statement where "true" results in "Validated" and "false" results in "Price Discrepancy".

I imagine the SELECT statement will look something like this...

SELECT col1, 
col2, 
...

CASE 
   WHEN SUM([Line Item Cost] WHERE [Invoice No] = [Invoice No]) = MAX([Total Invoice Amount] WHERE     
   [Invoice No] = [Invoice No]) THEN 'Validated'
      ELSE 'Price Discrepancy'
   END AS [Validation]

FROM [table]
GROUP BY ...
ORDER BY ...

I can provide some additional info if needed.

[Line Item Cost (Collect USD)] is the same as [Collect USD] in the code, I was just referring to it this way to make understanding a little easier (hopefully).

I'm currently using a CAST function on both [Line Item Cost (Collect USD)] and [Total Invoice Amount] to cast them from varchar(100) to decimal(18,5).

I'm also using a temporary table to hold most of the query and then a secondary SELECT statement to join the temp table to another reference table. I think this is more elegantly handled in the second select statement.

Here's the whole thing for additional reference:

SET NOCOUNT ON;

DROP TABLE IF EXISTS #temp

SELECT * INTO #temp
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ai.[Invoice No] ASC) AS [Invoice Line ID]
        ,ISNULL(ai.[Supplier Code], 'No Supplier Code Found') as [Supplier Code]
        ,ISNULL(ai.[Invoice No], 'No Invoice No Found') as [Invoice No]
        ,PARSE(ai.[Invoice Date] AS DATE USING 'en-US') as [Invoice Date]
        ,PARSE(ai.[Received Date] AS DATE USING 'en-US') as [Batch Date]
        ,REPLACE(RIGHT(CONVERT(VARCHAR(10),(PARSE(ai.[Invoice Date] AS DATE USING 'en-US')),103),7), '/', '-') as [Period]
        ,ISNULL(REPLACE(ai.[Master B/L No], '*', ''), 'No Master B/L No Found') as [Master B/L No]
        ,PARSE(ai.[On Board Date] AS DATE USING 'en-US') as [On Board Date]
        ,ISNULL(REPLACE(ai.[Ocean Vessel & Voyage], 'Ocean Vessel ', ''), 'No Ocean Vessel & Voyage Found') as [Ocean Vessel and Voyage]
        ,ISNULL(REPLACE(ai.[No of Cartons], 'CTNS', ''), 'No Cartons Found') as [No of Cartons]
        ,ISNULL(REPLACE(ai.[No of Pallets], 'PLTS', ''), 'No Pallets Found') as [No of Pallets]
        ,ISNULL(CONCAT('[',LEFT(ai.[Container No], CHARINDEX('/', ai.[Container No]) - 1),']'), 'No Container No Found') as [Container No]
        ,ISNULL(ai.[Port of Discharge], 'No Port of Discharge Found') as [Port of Discharge]
        ,ISNULL(ai.[Place of Delivery], 'No Place of Delivery Found') as [Place of Delivery]
        ,ISNULL(ai.[Final Destination], 'No Final Destination Found') as [Final Destination]
        ,CAST(CAST((REPLACE(ai.[Gross Weight (KG)], ' KGS', '')) as decimal(18,5)) as float) as [Gross Weight KG]
        ,ISNULL(REPLACE(ai.[H B/L No], '*', ''), 'No H B/L No Found') as [H BL No]
        ,ISNULL(REPLACE(ai.[Consignee], ',', ''), 'No Consignee Found') as [Consignee]
        ,ISNULL(ai.[Volume], 'No Volume Found') as [Volume]
        ,ISNULL(ai.[Item], 'No Item Found') as [Item]
        ,ISNULL(ai.[Prepaid], 'No Prepaid Found') as [Prepaid]
        ,CAST(ai.[Collect (USD)] as decimal(18,5)) as [Collect USD]
        ,CAST(ai.[Total Invoice Amount] as decimal(18,5)) as [Total Invoice Amount]
        ,CASE
            WHEN ai.[Port of Discharge] LIKE '%LONG BEACH%' 
            OR ai.[Port of Discharge] LIKE '%LOS ANGELES%' 
            OR ai.[Port of Discharge] LIKE '%TACOMA%' 
            OR ai.[Port of Discharge] LIKE '%VANCOUVER%'
            OR ai.[Port of Discharge] LIKE '%NEW YORK%'
            OR ai.[Port of Discharge] LIKE '%SEATTLE%'
            AND ai.[Final Destination] IS NULL
                THEN 'GRAND RAPIDS, MI'
                ELSE ISNULL(ai.[Final Destination], 'No Warehouse Found')
            END as [Warehouse]
        ,CASE
            WHEN ai.[Consignee] LIKE '%VIKING PRODUCTS INC.%' AND ai.[Item] LIKE '%OCEAN FREIGHT%' THEN '51-512000-VIK'
            WHEN ai.[Consignee] LIKE '%VIKING PRODUCTS DE M%' AND ai.[Item] LIKE '%OCEAN FREIGHT%' THEN '51-512050-VIK'
            WHEN ai.[Item] LIKE '%ORIGIN EXAM FEE%' OR ai.[Item] LIKE '%ISF%' THEN '51-514000-VIK'
                ELSE 'Account No Not Found'
            END as [Account No]
        FROM [DWH].[vplx].[AP_Invoice_Detail] ai
        WHERE 1=1
            AND CHARINDEX('/', [Container No]) > 0
    ) as t

SELECT t.*, 
    aj.[accounting_job_no] as [Accounting Job No]
    FROM #temp t
LEFT JOIN [plx].[Accounting_v_Accounting_Job_e_lookup] aj
    ON (t.[Warehouse] = aj.[final_dest_one]) OR
    (t.[Warehouse] = aj.[final_dest_two]) OR
    (t.[Warehouse] = aj.[final_dest_three]) OR
    (t.[Warehouse] = aj.[final_dest_four]) OR
    (t.[Warehouse] = aj.[final_dest_five]) OR
    (t.[Warehouse] = aj.[final_dest_six]) OR
    (t.[Warehouse] = aj.[final_dest_seven])
ORDER BY t.[Invoice No] asc

Any assistance would be greatly appreciated.

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (InvoiceLineID INT,InvoiceNo VARCHAR(10), LineItemCost MONEY, TotalInvoiceAmount MONEY);
INSERT @tbl (InvoiceLineID, InvoiceNo, LineItemCost, TotalInvoiceAmount) VALUES
(1, 'VIK123', 1000, 1100),
(2, 'VIK123', 100,  1100),
(1, 'VIK456', 2000, 2200),
(2, 'VIK456', 200,  2200);
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT *
        , InvoiceTotal = SUM(LineItemCost) OVER (PARTITION BY InvoiceNo)
    FROM @tbl
)
SELECT * 
    , result = IIF(TotalInvoiceAmount = InvoiceTotal, 'Validated', 'Price Discrepancy')
FROM rs;

Output

InvoiceLineID	InvoiceNo	LineItemCost	TotalInvoiceAmount	InvoiceTotal	result
1	VIK123	1000.00	1100.00	1100.00	Validated
2	VIK123	100.00	1100.00	1100.00	Validated
1	VIK456	2000.00	2200.00	2200.00	Validated
2	VIK456	200.00	2200.00	2200.00	Validated