SELECT tblshopping_cart.session_id, tblshopping_cart.product_id, tblshopping_cart.product_qty, tblshopping_cart.product_option, tblproducts.product_title, tblproducts.product_price, tblproducts.product_sale_price_status, tblproducts.product_sale_price, tblproduct_options.option_text, tblproduct_options.option_upcharge,
CASE
WHEN (tblproducts.product_sale_price_status = 'Y')
    CASE
    WHEN (tblproduct_options.option_upcharge IS NOT NULL)
        THEN (tblshopping_cart.product_qty * (tblproducts.product_sale_price + tblproduct_options.option_upcharge))
        ELSE (tblshopping_cart.product_qty * tblproducts.product_sale_price)
    END
ELSE
    CASE
    WHEN (tblproduct_options.option_upchage IS NOT NULL)
        THEN (tblshopping_cart.product_qty * (tblproducts.product_price + tblproduct_options.option_upcharge))
        ELSE (tblshopping_cart.product_qty * tblproducts.product_price)
    END
END AS product_total
FROM tblshopping_cart
INNER JOIN tblproducts ON tblshopping_cart.product_id = tblproducts.product_id
LEFT JOIN tblproduct_options ON tblshopping_cart.product_option = tblproduct_options.option_product_id
ORDER BY tblshopping_cart.product_qty ASC

CASE
    WHEN (tblproduct_options.option_upcharge IS NOT NULL)
        THEN (tblshopping_' at line 4

SELECT sc.session_id
    , sc.product_id
    , sc.product_qty
    , sc.product_option
    , p.product_title
    , p.product_price
    , p.product_sale_price_status
    , p.product_sale_price
    , po.option_text
    , po.option_upcharge
    , CASE
        WHEN (p.product_sale_price_status = 'Y')
        THEN <-- add this
            CASE
            WHEN (po.option_upcharge IS NOT NULL)
                THEN (sc.product_qty * (p.product_sale_price + po.option_upcharge))
                ELSE (sc.product_qty * p.product_sale_price)
            END
        ELSE
            CASE
            WHEN (po.option_upchage IS NOT NULL)
                THEN (sc.product_qty * (p.product_price + po.option_upcharge))
                ELSE (sc.product_qty * p.product_price)
            END
        END AS product_total
FROM tblshopping_cart sc
INNER JOIN tblproducts p
    ON sc.product_id = p.product_id
LEFT JOIN tblproduct_options po
    ON sc.product_option = po.option_product_id
ORDER BY sc.product_qty ASC

CASE
WHEN (tblproducts.product_sale_price_status = 'Y') THEN
                                                   ^^^^ add this

CASE
WHEN (tblproducts.product_sale_price_status = 'Y')
    THEN
    CASE
    WHEN (tblproduct_options.option_upcharge IS NOT NULL)
        THEN (tblshopping_cart.product_qty * (tblproducts.product_sale_price + tblproduct_options.option_upcharge))
        ELSE (tblshopping_cart.product_qty * tblproducts.product_sale_price)
    END
ELSE
    CASE
    WHEN (tblproduct_options.option_upchage IS NOT NULL)
        THEN (tblshopping_cart.product_qty * (tblproducts.product_price + tblproduct_options.option_upcharge))
        ELSE (tblshopping_cart.product_qty * tblproducts.product_price)
    END
END AS product_total

, CASE
                WHEN (ud.user_id <> a.contact_id)
                THEN(
                    CASE
                    WHEN
                        ( DATE_FORMAT(b.holiday_date,'%Y%m%d') IS NULL)
                        AND ( DATE_FORMAT(thisDate,'%w') NOT IN (0,6) )
                        AND (
                            DATE_FORMAT(a.contact_addate,'%Y%m%d') <= DATE_FORMAT(thisDate,'%Y%m%d')
                            AND (
                                DATE_FORMAT(a.contact_enddate,'%Y%m%d') >= DATE_FORMAT(thisDate,'%Y%m%d')
                                OR
                                DATE_FORMAT(a.contact_enddate,'%Y-%m-%d') = '0000-00-00')
                        )
                        THEN 7.5
                        ELSE 0.0
                        END required
                    )

                ELSE(
                    CASE
                    WHEN
                        ( DATE_FORMAT(b.holiday_date,'%Y%m%d') IS NULL)
                        AND ( DATE_FORMAT(thisDate,'%w') NOT IN (0,6) )
                        AND (
                            DATE_FORMAT(ud.start_date,'%Y%m%d') <= DATE_FORMAT(thisDate,'%Y%m%d')
                            AND (
                                DATE_FORMAT(ud.end_date,'%Y%m%d') >= DATE_FORMAT(thisDate,'%Y%m%d')
                                OR
                                DATE_FORMAT(ud.end_date,'%Y-%m-%d') = '0000-00-00')
                        )
                        THEN 7.5
                        ELSE 0.0
                        END required
                )
                END
        FROM
            dotproject.contacts a,
            dotproject.user_dept ud,
            dotproject.users b
        LEFT JOIN holidays b
            ON DATE_FORMAT(thisDate,'%Y%m%d') = DATE_FORMAT(b.holiday_date,'%Y%m%d')
        WHERE
            a.contact_department LIKE
            CASE WHEN deptno <> ''
            THEN deptno
            ELSE '%%'
            END
            AND a.contact_id IN (SELECT DISTINCT user_contact FROM dotproject.users)
            AND b.user_contact = a.contact_id
            OR (b.user_contact = ud.user_id
                AND ud.user_id = a.contact_id)
            AND b.user_id NOT IN (
                SELECT User_Id
                FROM dotproject.dp_groupmembers
                WHERE gr_seqid IN ('3','4')
                )
        ORDER BY
            a.contact_last_name);