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 Go to next message
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

Kindly suggest.

Best Regards,
Re: Previous Date value calculation through LAG function [message #686038 is a reply to message #686036] Thu, 02 June 2022 00:33 Go to previous message
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
Previous Topic: Group by department name, employee
Next Topic: Trigger created with compilation errors (invalid specification trigger)
Goto Forum:
  


Current Time: Thu Mar 28 07:55:53 CDT 2024