#Where aTot does not equal calculations
SELECT
    fidId,  -- replace with your real key
    aTot,
    (bTot
     + (dt683 + dt214)
     + (ed5318 + ed5319 + ed5320 + ed5321)
     + (mi866 + mi543 + mt592 + as551)
     + (at420 + ai163 + ar091)
     + oTot
     - cTot) AS calc_aTot,
    aTot - (bTot
            + (dt683 + dt214)
            + (ed5318 + ed5319 + ed5320 + ed5321)
            + (mi866 + mi543 + mt592 + as551)
            + (at420 + ai163 + ar091)
            + oTot
            - cTot) AS diff_aTot
FROM fidelity
WHERE aTot <> (bTot
               + (dt683 + dt214)
               + (ed5318 + ed5319 + ed5320 + ed5321)
               + (mi866 + mi543 + mt592 + as551)
               + (at420 + ai163 + ar091)
               + oTot
               - cTot);

#Validation of other fields
SELECT
    fidId,
    dTot,
    dt683 + dt214 AS calc_dTot,
    dTot - (dt683 + dt214) AS diff_dTot,
    eTot,
    (ed5318 + ed5319 + ed5320 + ed5321) AS calc_eTot,
    eTot - (ed5318 + ed5319 + ed5320 + ed5321) AS diff_eTot,
    mTot,
    (mi866 + mi543 + mt592 + as551) AS calc_mTot,
    mTot - (mi866 + mi543 + mt592 + as551) AS diff_mTot,
    nTot,
    (at420 + ai163 + ar091) AS calc_nTot,
    nTot - (at420 + ai163 + ar091) AS diff_nTot
FROM fidelity
WHERE dTot <> (dt683 + dt214)
   OR eTot <> (ed5318 + ed5319 + ed5320 + ed5321)
   OR mTot <> (mi866 + mi543 + mt592 + as551)
   OR nTot <> (at420 + ai163 + ar091) 
ORDER BY `fidelity`.`fidId` ASC
# Show Differences
SELECT fidId, dTot, dt683 + dt214 AS calc_dTot, dTot - (dt683 + dt214) AS diff_dTot, dt683 , dt214 FROM fidelity WHERE dTot <> (dt683 + dt214) ORDER BY `fidelity`.`fidId` ASC;
#Create a view
CREATE OR REPLACE VIEW fidelity_validation AS
SELECT
    primary_key_column,
    aTot,
    (bTot
     + (dt683 + dt214)
     + (ed5318 + ed5319 + ed5320 + ed5321)
     + (mi866 + mi543 + mt592 + as551)
     + (at420 + ai163 + ar091)
     + oTot
     - cTot) AS calc_aTot,
    aTot - (bTot
            + (dt683 + dt214)
            + (ed5318 + ed5319 + ed5320 + ed5321)
            + (mi866 + mi543 + mt592 + as551)
            + (at420 + ai163 + ar091)
            + oTot
            - cTot) AS diff_aTot,
    dTot,
    dt683 + dt214 AS calc_dTot,
    dTot - (dt683 + dt214) AS diff_dTot,
    eTot,
    (ed5318 + ed5319 + ed5320 + ed5321) AS calc_eTot,
    eTot - (ed5318 + ed5319 + ed5320 + ed5321) AS diff_eTot,
    mTot,
    (mi866 + mi543 + mt592 + as551) AS calc_mTot,
    mTot - (mi866 + mi543 + mt592 + as551) AS diff_mTot,
    nTot,
    (at420 + ai163 + ar091) AS calc_nTot,
    nTot - (at420 + ai163 + ar091) AS diff_nTot
FROM fidelity;
# Query View
SELECT *
FROM fidelity_validation
WHERE diff_aTot <> 0
   OR diff_dTot <> 0
   OR diff_eTot <> 0
   OR diff_mTot <> 0
   OR diff_nTot <> 0;
#Number of records to fix
SELECT COUNT(*) AS records_to_update
FROM fidelity_validation
WHERE diff_aTot <> 0
   OR diff_dTot <> 0
   OR diff_eTot <> 0
   OR diff_mTot <> 0
   OR diff_nTot <> 0;
# How to Update the dTot

#Best and worst days
WITH daily_calculations AS (
    SELECT 
        fidCurDate, 
        fidTot,
        fidComment,
        LAG(fidTot) OVER (ORDER BY fidCurDate) AS prev_fidTot
    FROM fidelity
    -- Adjust these dates as needed
    WHERE fidCurDate BETWEEN '2023-01-01' AND '2023-12-31'
),
formatted_results AS (
    SELECT 
        -- Formats as: "Monday, 01/01/2023"
        DATE_FORMAT(fidCurDate, '%W, %m/%d/%Y') AS formatted_date,
        fidComment,
        CONCAT('$', FORMAT(fidTot, 2)) AS formatted_fidTot,
        -- Logic to place negative sign before the $ for losses
        CONCAT(IF(fidTot < prev_fidTot, '-$', '$'), FORMAT(ABS(fidTot - prev_fidTot), 2)) AS formatted_dollar_change,
        ROUND(((fidTot - prev_fidTot) / NULLIF(prev_fidTot, 0)) * 100, 2) AS percent_change
    FROM daily_calculations
    WHERE prev_fidTot IS NOT NULL
)
-- Top Performer
(SELECT 'BEST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change DESC LIMIT 1)

UNION ALL

-- Bottom Performer
(SELECT 'WORST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change ASC LIMIT 1);

#Best and Worst Date Results
-- 1. Set your date range here
SET @startDate = '2019-01-01';
SET @endDate   = '2026-12-31';

-- 2. Execute the search
WITH daily_calculations AS (
    SELECT 
        fidCurDate, 
        fidTot,
        fidComment,
        LAG(fidTot) OVER (ORDER BY fidCurDate) AS prev_fidTot
    FROM fidelity
    -- Filter happens here
    WHERE fidCurDate BETWEEN @startDate AND @endDate
),
formatted_results AS (
    SELECT 
        DATE_FORMAT(fidCurDate, '%W, %m/%d/%Y') AS formatted_date,
        fidComment,
        CONCAT('$', FORMAT(fidTot, 2)) AS formatted_fidTot,
        CONCAT(IF(fidTot < prev_fidTot, '-$', '$'), FORMAT(ABS(fidTot - prev_fidTot), 2)) AS formatted_dollar_change,
        ROUND(((fidTot - prev_fidTot) / NULLIF(prev_fidTot, 0)) * 100, 2) AS percent_change
    FROM daily_calculations
    WHERE prev_fidTot IS NOT NULL
)
(SELECT 'BEST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change DESC LIMIT 1)

UNION ALL

(SELECT 'WORST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change ASC LIMIT 1);

#Total Change Summary for Range with Best and Worst
-- 1. Set your date range here
SET @startDate = '2019-01-01';
SET @endDate   = '2027-12-31';

-- 2. Execute the combined search and summary
WITH daily_calculations AS (
    SELECT 
        fidCurDate, 
        fidTot,
        fidComment,
        LAG(fidTot) OVER (ORDER BY fidCurDate) AS prev_fidTot,
        -- Window functions to find the absolute start and end values for the summary
        FIRST_VALUE(fidTot) OVER (ORDER BY fidCurDate) AS period_start_val,
        LAST_VALUE(fidTot) OVER (ORDER BY fidCurDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS period_end_val
    FROM fidelity
    WHERE fidCurDate BETWEEN @startDate AND @endDate
),
formatted_results AS (
    SELECT 
        DATE_FORMAT(fidCurDate, '%W, %m/%d/%Y') AS formatted_date,
        fidComment,
        CONCAT('$', FORMAT(fidTot, 2)) AS formatted_fidTot,
        CONCAT(IF(fidTot < prev_fidTot, '-$', '$'), FORMAT(ABS(fidTot - prev_fidTot), 2)) AS formatted_dollar_change,
        ROUND(((fidTot - prev_fidTot) / NULLIF(prev_fidTot, 0)) * 100, 2) AS percent_change
    FROM daily_calculations
    WHERE prev_fidTot IS NOT NULL
)

-- Combined Result Set
(SELECT 'BEST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change DESC LIMIT 1)

UNION ALL

(SELECT 'WORST' AS category, formatted_date, formatted_fidTot, formatted_dollar_change, percent_change, fidComment 
 FROM formatted_results 
 ORDER BY percent_change ASC LIMIT 1)

UNION ALL

-- The Summary Row
(SELECT 
    'SUMMARY' AS category, 
    CONCAT(DATE_FORMAT(@startDate, '%m/%d/%y'), ' to ', DATE_FORMAT(@endDate, '%m/%d/%y')) AS formatted_date,
    CONCAT('Final: $', FORMAT(period_end_val, 2)) AS formatted_fidTot,
    CONCAT(IF(period_end_val < period_start_val, '-$', '$'), FORMAT(ABS(period_end_val - period_start_val), 2)) AS formatted_dollar_change,
    ROUND(((period_end_val - period_start_val) / NULLIF(period_start_val, 0)) * 100, 2) AS percent_change,
    'Total change for the selected period' AS fidComment
 FROM daily_calculations 
 LIMIT 1);