Home » SQL & PL/SQL » SQL & PL/SQL » Previous Date value calculation through LAG function (Oracle 12C)
| Previous Date value calculation through LAG function [message #686036] |
Wed, 01 June 2022 04:44  |
 |
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Hello All,
Good day,
I'm attempting to compute the hours based on an event's prior day. In the data an event's starting date is always null, however, when I use the lag function, it adds all of the data, even though it should group by Name.
Create TABLE TEST
(NAME VARCHAR2(20),
TYPE1 VARCHAR2(20),
TYPE2 VARCHAR2(20),
EVENT_TIME DATE
)
Insert into test values ('A','I','S',(to_date('01-JAN-2022', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('A','I','S',(to_date('02-JAN-2022 2:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('A','I','S',(to_date('04-JAN-2022 3:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('A','I','S',(to_date('06-JAN-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('A','I','S',(to_date('10-JAN-2022 15:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('B','I','S',(to_date('12-JAN-2022', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('B','I','S',(to_date('22-JAN-2022 2:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('B','I','S',(to_date('24-JAN-2022 3:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('B','I','S',(to_date('26-JAN-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('B','I','S',(to_date('30-JAN-2022 15:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('C','I','S',(to_date('05-FEB-2022', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('C','I','S',(to_date('14-FEB-2022 2:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('C','I','S',(to_date('18-FEB-2022 3:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('C','I','S',(to_date('27-FEB-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('C','I','S',(to_date('28-FEB-2022 15:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('D','I','S',(to_date('05-MAR-2022', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('D','I','S',(to_date('14-MAR-2022 2:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('D','I','S',(to_date('18-MAR-2022 3:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('D','I','S',(to_date('27-MAR-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss')));
Insert into test values ('D','I','S',(to_date('28-MAR-2022 15:00:00', 'dd-mm-yyyy hh24:mi:ss')));
I have written the below query :-
SELECT NAME,
TYPE1,
TYPE2,
EXTRACT(YEAR FROM EVENT_TIME) EVENT_YEAR,
EVENT_TIME,
(EVENT_TIME - (LAG(EVENT_TIME, 1) OVER(ORDER BY EVENT_TIME))) TOTAL_UP_TIME
FROM TEST
group by NAME, EXTRACT(YEAR FROM EVENT_TIME),TYPE1,TYPE2,EVENT_TIME
ORDER BY NAME, EVENT_TIME DESC
Result comes as follows:-
NAME TYPE1 TYPE2 EVENT_YEAR EVENT_TIME TOTAL_UP_TIME
A I S 2022 1/10/2022 3:00:00 PM 4.125
A I S 2022 1/6/2022 12:00:00 PM 2.375
A I S 2022 1/4/2022 3:00:00 AM 2.041666667
A I S 2022 1/2/2022 2:00:00 AM 1.083333333
A I S 2022 1/1/2022
B I S 2022 1/30/2022 3:00:00 PM 4.125
B I S 2022 1/26/2022 12:00:00 PM 2.375
B I S 2022 1/24/2022 3:00:00 AM 2.041666667
B I S 2022 1/22/2022 2:00:00 AM 10.08333333
B I S 2022 1/12/2022 1.375
C I S 2022 2/28/2022 3:00:00 PM 1.125
C I S 2022 2/27/2022 12:00:00 PM 9.375
C I S 2022 2/18/2022 3:00:00 AM 4.041666667
C I S 2022 2/14/2022 2:00:00 AM 9.083333333
C I S 2022 2/5/2022 5.375
D I S 2022 3/28/2022 3:00:00 PM 1.125
D I S 2022 3/27/2022 12:00:00 PM 9.375
D I S 2022 3/18/2022 3:00:00 AM 4.041666667
D I S 2022 3/14/2022 2:00:00 AM 9.083333333
D I S 2022 3/5/2022 4.375
However the result should be as follows :-
NAME TYPE1 TYPE2 EVENT_YEAR EVENT_TIME TOTAL_UP_TIME
A I S 2022 1/10/2022 3:00:00 PM 4.125
A I S 2022 1/6/2022 12:00:00 PM 2.375
A I S 2022 1/4/2022 3:00:00 AM 2.041666667
A I S 2022 1/2/2022 2:00:00 AM 1.083333333
A I S 2022 1/1/2022
B I S 2022 1/30/2022 3:00:00 PM 4.125
B I S 2022 1/26/2022 12:00:00 PM 2.375
B I S 2022 1/24/2022 3:00:00 AM 2.041666667
B I S 2022 1/22/2022 2:00:00 AM 10.08333333
B I S 2022 1/12/2022
C I S 2022 2/28/2022 3:00:00 PM 1.125
C I S 2022 2/27/2022 12:00:00 PM 9.375
C I S 2022 2/18/2022 3:00:00 AM 4.041666667
C I S 2022 2/14/2022 2:00:00 AM 9.083333333
C I S 2022 2/5/2022
D I S 2022 3/28/2022 3:00:00 PM 1.125
D I S 2022 3/27/2022 12:00:00 PM 9.375
D I S 2022 3/18/2022 3:00:00 AM 4.041666667
D I S 2022 3/14/2022 2:00:00 AM 9.083333333
D I S 2022 3/5/2022
Based on original result, below comes when we group by year;
select NAME, TYPE1, TYPE2, EVENT_YEAR, sum(TOTAL_UP_TIME)
from (SELECT NAME,
TYPE1,
TYPE2,
EXTRACT(YEAR FROM EVENT_TIME) EVENT_YEAR,
EVENT_TIME,
(EVENT_TIME - (LAG(EVENT_TIME, 1) OVER(ORDER BY EVENT_TIME))) TOTAL_UP_TIME
FROM TEST
ORDER BY NAME, EVENT_TIME DESC)
group by NAME, TYPE1, TYPE2, EVENT_YEAR
NAME TYPE1 TYPE2 EVENT_YEAR SUM(TOTAL_UP_TIME)
A I S 2022 9.625
B I S 2022 20
C I S 2022 29
D I S 2022 28
Expected result is as follows:-
NAME TYPE1 TYPE2 EVENT_YEAR Expected Result
A I S 2022 9.625
B I S 2022 18.625
C I S 2022 23.625
D I S 2022 23.625
Best Regards,
|
|
|
|
| Re: Previous Date value calculation through LAG function [message #686038 is a reply to message #686036] |
Thu, 02 June 2022 00:33  |
|
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Dear All,
Good morning.
This is to report that issue has been resolved, by using Partition by in the function.
SELECT NAME, TYPE1, TYPE2, EVENT_YEAR, SUM(prev_EVENT_TIME)
FROM (SELECT NAME,
TYPE1,
TYPE2,
EXTRACT(YEAR FROM EVENT_TIME) EVENT_YEAR,
EVENT_TIME - LAG(EVENT_TIME, 1) OVER(PARTITION BY NAME ORDER BY EVENT_TIME) AS prev_EVENT_TIME
FROM test)
GROUP BY NAME,TYPE1, TYPE2, EVENT_YEAR
ORDER BY NAME
|
|
|
|
Goto Forum:
Current Time: Wed Mar 29 11:37:12 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
