-- back up tables 
CREATE TABLE gsmcell_backup_20251114
    AS
        SELECT
            *
        FROM
            gsmcell;

CREATE TABLE cell_backup_20251114
    AS
        SELECT
            *
        FROM
            cell;

CREATE TABLE gsmcellband_backup_20251114
    AS
        SELECT
            *
        FROM
            gsmcellband;
 

-- for GSM band
DELETE FROM gsmcellband
WHERE
    gsmcellbandid IN (
        SELECT
            b.gsmcellbandid
        FROM
                 gsmcellband b
            INNER JOIN cell c ON b.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.btsid, MAX(c.insertdate) AS latest_insert
                FROM
                         gsmcell g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.btsid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );
  

-- for gsm cell 
DELETE FROM gsmcell
WHERE
    cellid IN (
        SELECT
            g.cellid
        FROM
                 gsmcell g
            INNER JOIN cell c ON g.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.btsid, MAX(c.insertdate) AS latest_insert
                FROM
                         gsmcell g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.btsid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );

-- for  cell 
DELETE FROM cell
WHERE
    cellid IN (
        SELECT
            c.cellid
        FROM
                 gsmcell_backup_20251114 g
            INNER JOIN cell c ON g.cellid = c.cellid
            INNER JOIN (
                SELECT
                    c.objectid, c.cellname, g.btsid, MAX(c.insertdate) AS latest_insert
                FROM
                         gsmcell_backup_20251114 g
                    INNER JOIN cell c ON g.cellid = c.cellid
                GROUP BY
                    c.objectid, c.cellname,
                    g.btsid
                HAVING
                    COUNT(*) > 1
            )    d ON c.objectid = d.objectid
                   AND c.cellname = d.cellname
                   AND c.insertdate <> d.latest_insert
    );
    
    
    commit;