Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '2025-03-01' AND '2025-03-07'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Query failed: Unknown column 'location' in 'field list'
Query: SELECT 
    transaction_date,
    dealer,
    sales_order,
    open_balance,
    stock_in,
    stock_out,
    close_balance,
    remarks
FROM (
    SELECT 
        id.entered_date AS transaction_date,
        @open_balance := (
            CASE 
                WHEN id.stock_status = 1 THEN 
                    (SELECT 
                        ih.open_balance
                    FROM 
                        item_header ih
                    WHERE 
                        ih.barcode = id.barcode
                        AND (ih.year < YEAR(id.entered_date) OR (ih.year = YEAR(id.entered_date) AND ih.month <= MONTH(id.entered_date)))
                    ORDER BY
                        ih.year DESC, ih.month DESC
                    LIMIT 1)
                ELSE @open_balance
            END) AS open_balance,
        (CASE 
            WHEN id.stock_status = 1 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_in,
        (CASE 
            WHEN id.stock_status = 2 THEN id.stock_qty
            WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
            ELSE 0
        END) AS stock_out,
        (@open_balance := @open_balance + 
            (CASE 
                WHEN id.stock_status = 1 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty > 0 THEN id.stock_qty
                ELSE 0
            END) + 
            (CASE 
                WHEN id.stock_status = 2 THEN id.stock_qty
                WHEN id.stock_status = 3 AND id.stock_qty < 0 THEN id.stock_qty
                ELSE 0
            END)) AS close_balance,
        `location`,
        `best_before_date`,
        `dealer`,
        `sales_order`,
        (CASE
            WHEN id.stock_status = 1 THEN 'stock in'
            WHEN id.stock_status = 2 THEN 'stock out'
            WHEN id.stock_status = 3 THEN id.remarks
            ELSE ''
        END) AS remarks
    FROM 
        `item_details` id
    JOIN
        (SELECT @open_balance := 0) AS init
    WHERE 
        id.barcode = ''
        AND id.entered_date BETWEEN '1970-01-01' AND '1970-01-01'
    ORDER BY 
        id.entered_date
) AS transactions
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)
Failed to connect to MySQL: Access denied for user 'hwataico'@'localhost' (using password: YES)