Tukukkk

I am trying understand how range clause is working in below case (oracle database)

SELECT

    EMPID,NAME, 

    HIRE_DATE_1, 

    SALARY,

    count(1) over(order by HIRE_DATE_1 range between 1 preceding and 1 preceding) as PREV_MIN_SA

FROM (

    SELECT 

        EMPID,

        NAME,

        (EXTRACT(year from HIRE_DATE)*10000)+(EXTRACT(MONTH FROM HIRE_DATE) * 100) + (extract(DAY from HIRE_DATE)) as HIRE_DATE_1,SALARY

     FROM EMPLOYEE A order by HIRE_DATE,SALARY

) A

ORDER BY HIRE_DATE_1 

Result Set :

EMPID   NAME    HIRE_DATE_1 SALARY  PREV_MIN_SA

100     Ravi    20180101    5000    0

101     Kumar   20180101    7000    0

102     Satish  20180101    13000   0

103     Naresh  20180102    7500    3

105     Lalith  20180104    17300   0

104     Suresh  20180104    40000   0

106     Latha   20180201    16000   0

The inner query is just converting date into numeric YYYYMMDD format.
My intention is to get the count of people who joined immediately prior to the date of the employee in each record. I can take the count of rows with same HIRE_DATE and use LAG function but somehow not understand how the sql is returning this result set.

Also, once I am done with the counts I would like to get the MIN(SALARY) of the employees who joined immediately prior to the employee in current row and find the difference in salaries so wondering if somehow I can define the window to only have all records with immediately prior HIRE_DATE.

Thanks

This should get the preceding hire date...

SELECT

    EMPID,NAME, HIRE_DATE, SALARY,

    MAX(HIRE_DATE) OVER (ORDER BY HIRE_DATE

                            RANGE BETWEEN UNBOUNDED PRECEDING

                                      AND INTERVAL '1 DAY' PRECEDING

                        )

                           AS PREV_HIRE_DATE

FROM

    EMPLOYEE

Then I think you need to join back on to the employee table to find the number of employees and their min salary?

SELECT

    *

FROM

(

    SELECT

        EMPID,NAME, HIRE_DATE, SALARY,

        MAX(HIRE_DATE) OVER (ORDER BY HIRE_DATE

                                RANGE BETWEEN UNBOUNDED PRECEDING

                                          AND INTERVAL '1 DAY' PRECEDING

                            )

                               AS PREV_HIRE_DATE

    FROM

        EMPLOYEE

)

    EMPS

LEFT JOIN

(

    SELECT

        HIRE_DATE,

        COUNT(*)     AS COUNT_EMPS,

        MIN(SALARY)  AS MIN_SALARY

    FROM

        EMPLOYEE

    GROUP BY

        HIRE_DATE

)

    PREV_EMPS

        ON PREV_EMPS.HIRE_DATE = EMPS.PREV_HIRE_DATE

EDIT:

Maybe try something like this? (I have to run, good luck!)

WITH

  ranked AS

(

    SELECT

      *,

      DENSE_RANK() OVER (ORDER BY HIRE_DATE)   AS HIRE_SEQ_ID

    FROM

      EMPLOYEE

)

SELECT

  *,

  MIN(SALARY) OVER (ORDER BY HIRE_SEQ_ID

                       RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING

                   )

                      AS PREV_MIN_SALARY,

  COUNT(*)    OVER (ORDER BY HIRE_SEQ_ID

                       RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING

                   )

                      AS COUNT_PREV_EMPS

FROM

  ranked

This does what you're after, I believe (I've left it as an exercise for you to find the difference between the max and min salaries!):

WITH employee AS (SELECT 100 empid, 'Ravi' NAME, to_date('01/01/2018', 'dd/mm/yyyy') hire_date, 5000 salary FROM dual UNION ALL

                  SELECT 101 empid, 'Kumar' NAME, to_date('01/01/2018', 'dd/mm/yyyy') hire_date, 7000 salary FROM dual UNION ALL

                  SELECT 102 empid, 'Satish' NAME, to_date('01/01/2018', 'dd/mm/yyyy') hire_date, 13000 salary FROM dual UNION ALL

                  SELECT 103 empid, 'Naresh' NAME, to_date('02/01/2018', 'dd/mm/yyyy') hire_date, 7500 salary FROM dual UNION ALL

                  SELECT 104 empid, 'Lalith' NAME, to_date('04/01/2018', 'dd/mm/yyyy') hire_date, 17300 salary FROM dual UNION ALL

                  SELECT 105 empid, 'Suresh' NAME, to_date('04/01/2018', 'dd/mm/yyyy') hire_date, 40000 salary FROM dual UNION ALL

                  SELECT 106 empid, 'Latha' NAME, to_date('01/02/2018', 'dd/mm/yyyy') hire_date, 16000 salary FROM dual)

SELECT empid,

       NAME,

       hire_date,

       salary,

       COUNT(*) OVER (ORDER BY hire_date RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) prev_day_hire_count,

       MIN(salary) OVER (ORDER BY hire_date RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) prev_day_min_sal,

       MAX(salary) OVER (ORDER BY hire_date RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) prev_day_max_sal

FROM   employee;



     EMPID NAME   HIRE_DATE       SALARY PREV_DAY_HIRE_COUNT PREV_DAY_MIN_SAL PREV_DAY_MAX_SAL

---------- ------ ----------- ---------- ------------------- ---------------- ----------------

       100 Ravi   01/01/2018        5000                   0                  

       101 Kumar  01/01/2018        7000                   0                  

       102 Satish 01/01/2018       13000                   0                  

       103 Naresh 02/01/2018        7500                   3             5000            13000

       104 Lalith 04/01/2018       17300                   0                  

       105 Suresh 04/01/2018       40000                   0                  

       106 Latha  01/02/2018       16000                   0