Calculate the number of days spent at each station for each item in oracle plsql

Having the log table as below:

| prod_id  | station_id |             date_in          |
| -------- | ---------- | ---------------------------- |
| p1       |    s1      |  2022-09-01 12:06:41.6216195 |
| p2       |    s1      |  2022-09-02 10:06:14.6216195 |
| p2       |    s2      |  2022-09-02 02:04:55.6216195 |
| p1       |    s2      |  2022-09-02 11:06:40.6216195 |
| p3       |    s1      |  2022-09-02 04:06:23.6216195 |
| p1       |    s3      |  2022-09-03 12:00:33.6216195 |
| p2       |    s1      |  2022-09-04 02:06:44.6216195 |
| p1       |    s4      |  2022-09-04 07:12:20.6216195 |
| p2       |    s2      |  2022-09-05 03:04:21.6216195 |
| p2       |    s3      |  2022-09-07 05:17:35.6216195 |
| p1       |    s3      |  2022-09-08 14:50:54.6216195 |
| p1       |    s4      |  2022-09-10 09:08:10.6216195 |
| p1       |    s5      |  2022-09-11 11:22:47.6216195 |

How can I calculate the total time (in day) spent in each station for each product?

For example to calculate the number of days spent for p1:

| prod_id  | station_id |    date_in   |       sysdate = (2022-09-13)       |
| -------- | ---------- | ------------ | ---------------------------------- |
| p1       |    s1      |  2022-09-01  |  (2022-09-02) - (2022-09-01) = 1   |
| p1       |    s2      |  2022-09-02  |  (2022-09-03) - (2022-09-02) = 1   |  
| p1       |    s3      |  2022-09-03  |  (2022-09-04) - (2022-09-03) = 1   |
| p1       |    s4      |  2022-09-04  |  (2022-09-08) - (2022-09-04) = 2   |
| p1       |    s3      |  2022-09-08  |  (2022-09-10) - (2022-09-08) = 2   |
| p1       |    s4      |  2022-09-10  |  (2022-09-11) - (2022-09-10) = 2   |
| p1       |    s5      |  2022-09-11  |     sysdate   - (2022-09-11) = 2   |

result for p1:

 | prod_id  | s1 | s2 | s3  |  s4 | s5 | s6 |...
 | -------- | -- | -- | --  |  -- | -- | -- |...
 |    p1    |  1 |  1 | 1+2 | 2+2 |  2 |  0 |...

Finally the result should look like this:

 | prod_id  | s1 | s2 | s3 | s4 | s5 | s6 |...
 | -------- | -- | -- | -- | -- | -- | -- |...
 |    p1    |  1 |  1 |  3 |  4 |  2 |  0 |...
 |    p2    |  1 |  4 |  0 |  0 |  0 |  0 |...
 |    p3    | 11 |  0 |  0 |  0 |  0 |  0 |...

For the last record of each product, sysdate should be considered for calculation.

The Staions are not in any particular order (The log table can only be sorted by date_in).

i'm using oracle 11g.

Solution

You can use the LEAD analytic function to find the difference from the next date and then find the number of days and PIVOT:

SELECT prod_id,
       COALESCE(s1, 0) AS s1,
       COALESCE(s2, 0) AS s2,
       COALESCE(s3, 0) AS s3,
       COALESCE(s4, 0) AS s4,
       COALESCE(s5, 0) AS s5,
       COALESCE(s6, 0) AS s6
FROM   (
  SELECT prod_id,
         station_id,
         ROUND(
           EXTRACT(DAY FROM diff)
           + EXTRACT(HOUR FROM diff)/24
           + EXTRACT(MINUTE FROM diff)/24/60
           + EXTRACT(SECOND FROM diff)/24/60/60,
           5
         ) AS days_diff
  FROM   (
    SELECT prod_id,
           station_id,
           LEAD(date_in, 1, SYSDATE) OVER (
             PARTITION BY prod_id
             ORDER BY date_in
           ) - date_in AS diff
    FROM   table_name
  )
)
PIVOT (
  SUM(days_diff)
  FOR station_id IN (
    's1' AS s1,
    's2' AS s2,
    's3' AS s3,
    's4' AS s4,
    's5' AS s5,
    's6' AS s6
  )
)

Which, for your sample data:

CREATE TABLE table_name ( prod_id, station_id, date_in ) AS
SELECT 'p1', 's1', TIMESTAMP '2022-09-01 12:06:41.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's1', TIMESTAMP '2022-09-02 10:06:14.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's2', TIMESTAMP '2022-09-02 02:04:55.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's2', TIMESTAMP '2022-09-02 11:06:40.6216195' FROM DUAL UNION ALL
SELECT 'p3', 's1', TIMESTAMP '2022-09-02 04:06:23.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's3', TIMESTAMP '2022-09-03 12:00:33.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's1', TIMESTAMP '2022-09-04 02:06:44.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's4', TIMESTAMP '2022-09-04 07:12:20.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's2', TIMESTAMP '2022-09-05 03:04:21.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's3', TIMESTAMP '2022-09-07 05:17:35.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's3', TIMESTAMP '2022-09-08 14:50:54.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's4', TIMESTAMP '2022-09-10 09:08:10.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's5', TIMESTAMP '2022-09-11 11:22:47.6216195' FROM DUAL

Outputs:

PROD_ID	S1	S2	S3	S4	S5
p1	.95832	1.03742	2.56184	5.41193	78.06039
p2	2.70702	2.42677	82.314	0	0
p3	87.36344	0	0	0	0

fiddle