SQL & PL/SQL

I have the following query right now

  SELECT "DATE",
          "CELL_SITE",
          "LASTV_ATTCNT",
          "LASTV_ATTCNT2",
          "LASTV_BLKCNT",
          "LASTV_DRPCNT",
          "V_ATT_CNT",
          "V_CUST_BLK_CNT",
          "V_DRP_CALL_CNT",
          "LASTD_ATTCNT",
          "LASTD_BLKCNT",
          "LASTD_DRPCNT",
          "D_ATT_CNT",
          "D_CUST_BLK_CNT",
          "D_DRP_CALL_CNT"          
     FROM (  SELECT DATE,
                   CELL_SITE,
                    LAG (SUM (V_ATT_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTV_ATTCNT",
                    LAG (SUM (V_CUST_BLK_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTV_BLKCNT",
                    LAG (SUM (V_DRP_CALL_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTV_DRPCNT",
                    LAG (SUM (D_ATT_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTD_ATTCNT",
                    LAG (SUM (D_CUST_BLK_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTD_BLKCNT",
                    LAG (SUM (D_DRP_CALL_CNT), 24)
                       OVER (PARTITION BY BSM_NM ORDER BY DATE)
                       AS "LASTD_DRPCNT",
                    SUM (V_ATT_CNT) AS "V_ATT_CNT",
                    SUM (V_CUST_BLK_CNT) AS "V_CUST_BLK_CNT",
                    SUM (V_DRP_CALL_CNT) AS "V_DRP_CALL_CNT",
                    SUM (D_ATT_CNT) AS "D_ATT_CNT",
                    SUM (D_CUST_BLK_CNT) AS "D_CUST_BLK_CNT",
                    SUM (D_DRP_CALL_CNT) AS "D_DRP_CALL_CNT"
               FROM DMSN.DS3R_FH_1XRTT_FA_LVL_KPI
              WHERE DATE >
                         (SELECT MAX (DATE) FROM DMSN.DS3R_FH_1XRTT_FA_LVL_KPI)
                       - 2
           GROUP BY DATE, CELL_SITE)
    WHERE DATE >=
             (SELECT MAX (DATE) - NUMTODSINTERVAL (12, 'HOUR')
                FROM DMSN.DS3R_FH_1XRTT_FA_LVL_KPI)

What I've noticed is that the LAG function is doing kind of what I want but not exactly. Lets say I have data for hours 12AM, 1AM, 2AM, 3AM, 4AM, 5AM, 6AM, 7AM, 8AM, 9AM, 10AM, 11AM, 12PM, and that the current hour is 12PM, the LAG function goes back to 12AM, which is what it should be doing.

but lets say I have data for 12AM, 1AM, 2AM, 3AM, 4AM, 5AM, 7AM, 8AM, 9AM, 10AM, 11AM, 12PM, but am MISSING 6AM (no data for 6AM). The lag function will now go back to 11PM instead of 12AM since the 6AM hour is missing.

if I have data for 12AM, 1AM, 2AM, 3AM, 4AM, 7AM, 8AM, 9AM, 10AM, 11AM, 12PM and am MISSING 5AM and 6AM, the data will go back to 10PM then and not just 12AM.

Can I prevent this from happening and always just go back 12 hours?

Edited by: k1ng87 on Apr 25, 2013 1:27 PM