SELECT *
FROM (
    SELECT
        fidId,
        fidCurDate,
        mTot,
        nTot,
        LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS prevMTot,
        LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS prevNTot,
        mTot - LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS mTotChange,
        nTot - LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS nTotChange,
        CASE
            WHEN mTot > LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN mTot < LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE'
        END AS mTotStatus,
        CASE
            WHEN nTot > LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN nTot < LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE'
        END AS nTotStatus
    FROM fidelity
) diffs
ORDER BY fidCurDate DESC
LIMIT 20;


Add diff for fidTot
SELECT *
FROM (
    SELECT
        fidId,
        fidCurDate,
        mTot,
        nTot,
        (mTot + nTot) AS fidTot,
        LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS prevMTot,
        LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS prevNTot,
        LAG(mTot + nTot) OVER (ORDER BY fidCurDate ASC) AS prevFidTot,

        mTot - LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS mTotChange,
        nTot - LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS nTotChange,
        (mTot + nTot) - LAG(mTot + nTot) OVER (ORDER BY fidCurDate ASC) AS fidTotChange,

        CASE
            WHEN mTot > LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN mTot < LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE'
        END AS mTotStatus,
        CASE
            WHEN nTot > LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN nTot < LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE'
        END AS nTotStatus,
        CASE
            WHEN (mTot + nTot) > LAG(mTot + nTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN (mTot + nTot) < LAG(mTot + nTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE'
        END AS fidTotStatus
    FROM fidelity
) diffs
ORDER BY fidCurDate DESC
LIMIT 20;

#Add formatting
SELECT
    fidId,
    DATE_FORMAT(fidCurDate, '%Y-%m-%d (%W)') AS fidCurDate,
    CONCAT('$ ', FORMAT(mTot, 2)) AS mTot,
    CONCAT('$ ', FORMAT(nTot, 2)) AS nTot,
    CONCAT('$ ', FORMAT(fidTot, 2)) AS fidTot,
    CONCAT('$ ', FORMAT(mTotChange, 2)) AS mTotChange,
    CONCAT('$ ', FORMAT(nTotChange, 2)) AS nTotChange,
    CONCAT('$ ', FORMAT(fidTotChange, 2)) AS fidTotChange
FROM (
    SELECT
        fidId,
        fidCurDate,
        mTot,
        nTot,
        fidTot,
        mTot - LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS mTotChange,
        nTot - LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS nTotChange,
        fidTot - LAG(fidTot) OVER (ORDER BY fidCurDate ASC) AS fidTotChange
    FROM fidelity
) diffs
ORDER BY fidCurDate DESC
LIMIT 20;

# Duplicate Dates
SELECT DATE(fidCurDate) AS dayOnly, COUNT(*) AS cnt FROM fidelity GROUP BY DATE(fidCurDate) HAVING cnt > 1 ORDER BY dayOnly DESC;
# Duplict fidId
SELECT
    fidId,
    fidCurDate,
    CONCAT('$ ', FORMAT(mTot, 2)) AS mTot,
    CONCAT('$ ', FORMAT(nTot, 2)) AS nTot,
    CONCAT('$ ', FORMAT(fidTot, 2)) AS fidTot,
    fidComment
FROM fidelity
WHERE DATE(fidCurDate) IN (
    SELECT DATE(fidCurDate)
    FROM fidelity
    GROUP BY DATE(fidCurDate)
    HAVING COUNT(*) > 1
)
ORDER BY DATE(fidCurDate) DESC, fidCurDate ASC;
XXXXXX
Create View
CREATE OR REPLACE VIEW vw_compare_tots AS 
SELECT 
    fidId, fidCurDate, mTot, nTot, prevMTot, prevNTot, 
    mTotChange, nTotChange, mTotStatus, nTotStatus
FROM (
    SELECT 
        fidId, fidCurDate, mTot, nTot,
        LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS prevMTot,
        LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS prevNTot,
        mTot - LAG(mTot) OVER (ORDER BY fidCurDate ASC) AS mTotChange,
        nTot - LAG(nTot) OVER (ORDER BY fidCurDate ASC) AS nTotChange,
        CASE 
            WHEN mTot > LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN mTot < LAG(mTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE' 
        END AS mTotStatus,
        CASE 
            WHEN nTot > LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'PROFIT'
            WHEN nTot < LAG(nTot) OVER (ORDER BY fidCurDate ASC) THEN 'LOSS'
            ELSE 'NO CHANGE' 
        END AS nTotStatus
    FROM fidelity
) AS diffs; -- <--- The 'AS diffs' alias is mandatory here
XXXXXX
Query View
SELECT * FROM vw_compare_tots LIMIT 10;
XXXXXX