Showing posts with label Analytical Function. Show all posts
Showing posts with label Analytical Function. Show all posts

Monday, July 25, 2016

Find out the difference between the latest value and second latest value.


SELECT EVENT_TYPE, VALUE - LATEST_VALUE "Value"
  FROM (SELECT ROWNUMBER,
               EVENT_TYPE,
               VALUE,
               NVL (
                  LAG (VALUE, 1)
                     OVER (PARTITION BY EVENT_TYPE ORDER BY EVENT_TYPE, TIME),
                  0)
                  AS LATEST_VALUE,
               TIME
          FROM (  SELECT ROW_NUMBER ()
                         OVER (PARTITION BY EVENT_TYPE
                               ORDER BY EVENT_TYPE, TIME DESC)
                            AS ROWNUMBER,
                         EVENT_TYPE,
                         VALUE,
                         TIME
                    FROM EVENTS
                ORDER BY EVENT_TYPE, TIME DESC)
         WHERE ROWNUMBER < 3)
 WHERE LATEST_VALUE > 0;

Data Preparation 

CREATE TABLE EVENTS
(
  EVENT_TYPE  INTEGER                           NOT NULL,
  VALUE       INTEGER                           NOT NULL,
  TIME        TIMESTAMP(6)                      NOT NULL
);

SET DEFINE OFF;
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 5, TO_TIMESTAMP('7/25/2016 4:27:27.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 13, TO_TIMESTAMP('7/25/2016 4:28:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 34, TO_TIMESTAMP('7/25/2016 4:28:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (4, 42, TO_TIMESTAMP('7/25/2016 4:27:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 4, TO_TIMESTAMP('7/25/2016 4:31:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 12, TO_TIMESTAMP('7/25/2016 4:31:53.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 34, TO_TIMESTAMP('7/25/2016 4:31:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 12, TO_TIMESTAMP('7/25/2016 4:27:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (7, 13, TO_TIMESTAMP('7/25/2016 4:27:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

Sunday, December 14, 2014

Day Wise Account Balance Statement (Generat Manual Data Full Month).

CREATE TABLE AC_BAL
(
  AC_NUMBER  NUMBER,
  BALANCE    NUMBER,
  TR_DATE    DATE
);

SET DEFINE OFF;
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (1, 3000, TO_DATE('12/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (1, 2000, TO_DATE('12/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (1, 8000, TO_DATE('12/16/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (1, 5000, TO_DATE('12/29/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (2, 5000, TO_DATE('12/29/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into AC_BAL
   (AC_NUMBER, BALANCE, TR_DATE)
 Values
   (3, 200, TO_DATE('12/16/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
------ Query For Generate Statement ---------------

SELECT B.AC_NUMBER, NVL(BALANCE,0) BALANCE, DAYS
FROM AC_BAL A RIGHT OUTER JOIN (SELECT DISTINCT AC_NUMBER,DAYS FROM AC_BAL, (
SELECT (CASE WHEN LEVEL>1 THEN TRUNC(TO_DATE('30-DEC-2014'),'MM')+(LEVEL-1) ELSE TRUNC(TO_DATE('30-DEC-2014'),'MM')  END) DAYS
FROM   DUAL
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY('30-DEC-2014'),'DD')))
ORDER BY AC_NUMBER) B
ON (A.TR_DATE=B.DAYS AND B.AC_NUMBER=A.AC_NUMBER)
ORDER BY B.AC_NUMBER, DAYS