UTILITYREADING TABLE

UTILITYINVOICE table should look like this:

I was able to insert the ELECCURRENT value (or the current month February reading) by executing:

INSERT INTO UTILITYINVOICE (ROOMNUMBER, ELECCURRENT, INVOICEDATE)
    SELECT 
        ROOMNUMBER, NVL(ELECTRICITYREADING, 0), SYSDATE
    FROM UTILITYREADING 
    WHERE (extract(MONTH from DATEOFREADING) = extract(MONTH from SYSDATE)) 
      AND (extract(YEAR from DATEOFREADING) = extract(YEAR from SYSDATE));

My problem is fetching the February reading, to fetch the January reading i tried this but with no success:

UPDATE UTILITYINVOICE
SET ELECPREVIOUS = (SELECT NVL(ELECTRICITYREADING, 0)
                    FROM UTILITYREADING) 
                    WHERE EXISTS (SELECT 1 FROM utilityreading
                                  WHERE (extract(MONTH from UTILITYREADING.DATEOFREADING) = extract(MONTH from SYSDATE)-1) 
                                    AND (extract(YEAR from UTILITYREADING.DATEOFREADING) = extract(YEAR from SYSDATE))
                                    AND utilityreading.roomnumber = utilityinvoice.roomnumber);

CodePudding user response：

I'd say that you need

• LAG analytic function (to select previous electricity reading)
• ROW_NUMBER (to sort rows per each room number by date in descending order)
  • and then fetch row that ranks as the highest

SQL> insert into utilityinvoice (roomnumber, eleccurrent, elecprevious, invoicedate)
  2  with temp as
  3    (select roomnumber,
  4       electricityreading as eleccurrent,
  5       lag(electricityreading) over (partition by roomnumber order by dateofreading) elecprevious,
  6       row_number() Over (partition by roomnumber order by dateofreading desc) rn
  7     from utilityreading
  8    )
  9  select roomnumber, eleccurrent, elecprevious, sysdate
 10  from temp
 11  where rn = 1;

2 rows created.

SQL> select * from utilityinvoice;

ROOM ELECCURRENT ELECPREVIOUS INVOICEDAT
---- ----------- ------------ ----------
N201       279,8        240,6 02/19/2022
N202       299,8        259,8 02/19/2022

SQL>