LEAD function in SQL query not getting desired result

Question

I'm trying to get an entry from a table that has START_TIME and FINISH_TIME. If FINISH_TIME is null then have to get the next START_TIME. Below is the query I tried, also have to use the lead function for optimization

    CREATE TABLE RUN_LOG (
ID int,
PROCESS_TYPE varchar(20),
START_TIME DATETIME,
FINISH_TIME DATETIME
)

INSERT INTO RUN_LOG (ID,PROCESS_TYPE,START_TIME,FINISH_TIME)
VALUES (1,'A','2023-08-10 10:11:00',NULL),
(2,'A','2023-08-10 12:11:00','2023-08-11 12:24:00'),
(3,'B','2023-08-10 12:25:00','2023-08-11 12:44:00')

CREATE TABLE VALIDATION_LOG (
TABLE_NAME VARCHAR(50),
PROCESS_TYPE varchar(20),
PROCESS_TIME TIMESTAMP,
REMARK VARCHAR(1000))

INSERT INTO VALIDATION_LOG (TABLE_NAME,PROCESS_TIME,REMARK)
VALUES ('TAB1','A','2023-08-11 12:05:00', 'ERROR ON STOREID'),
('TAB2','b','2023-08-11 12:26:00', 'NULL VALUES IN CARD')

CREATE TABLE PROCESS_TYPE (
PROCESS_TYPE varchar(20),
PROCESS_CATALOGUE TIMESTAMP)

INSERT INTO PROCESS_TYPE (PROCESS_TYPE,PROCESS_CATALOGUE)
VALUES ('A','ELT JOB'),
('B','EXPORT')

In the above script PROCESS_TYPE 'A' has no FINISH_TIME in row 1, so I need to take row 1 START_TIME and row 2 START_TIME to find if that falls between VL.PROCESS_TIME.

 SELECT 
        RH.PROCESS_TYPE, PT.PROCESS_TYPE, VL.REMARK, 
        VL.TABLE_NAME, VL.PROCESS_TIME 
    FROM 
        RUN_LOG AS RH
    INNER JOIN 
        VALIDATION_LOG VL ON RH.PROCESS_TYPE = VL.PROCESS_TYPE 
                          AND VL.PROCESS_TIME BETWEEN RH.START_TIME AND ISNULL(RH.FINISH_TIME, (SELECT LEAD(RH.START_TIME, 10) OVER (ORDER BY RH.PR_RUN_LOG_ID) dd FROM RUN_LOG))
    INNER JOIN 
        PROCESS_TYPE PT ON PT.PROCESS_TYPE = RH.PROCESS_TYPE
    WHERE 
        VL.REMARK LIKE '%ERROR%'  
        AND PT.PROCESS_CATALOGUE LIKE '%JOB%' 
        AND VL.PROCESS_TIME > GETDATE()

With data and tables and wanredbtesultv, it is hard to reproduce your problem, so please edit your question — nbk, Aug 13 '23 at 06:27
@nbk Sorry for being unclear, now edited the post. Hope this is clear. — Vasanth, Aug 13 '23 at 13:46
images are not allowed here and this is only the run_log and where is the VALIDATION_LOG table, also we need also know, what the result for the presented data should be, see https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question and https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and https://stackoverflow.com/help/minimal-reproducible-example — nbk, Aug 13 '23 at 13:50
@nbk I have edited again and added more details, Also, none of the data is real data and have created for this post. As I'm using this lead function first time wanted to know how to get the same column value of next row if the previous row column is null. to check a between condition. — Vasanth, Aug 13 '23 at 20:27
that is very good but it has its flaws, but which rdms are you using see https://dbfiddle.uk/4_XRF6Lo and change the database system and ad the link — nbk, Aug 13 '23 at 20:36

tinazmu · Answer 1 · 2023-08-14T01:49:43.367

Replace

FROM 
        RUN_LOG AS RH...

with

FROM 
 (SELECT RL.*
       , COALESCE(  RL.FINISH_TIME
                  , LEAD(RL.FINISH_TIME)
                    OVER(ORDER BY RL.ID) as FINISH_TIME
  FROM RUN_LOG RL
 ) RH

I couldn't find what PR_RUN_LOG_ID you refer to in your query is. (ORDER BY RH.PR_RUN_LOG_ID)

If your RDBMS doesn't accept Alias.* (RL.*), you need to list the columns. If your RDBMS doesn't allow using the column name as alias, you will have to rename it.

LEAD function in SQL query not getting desired result

1 Answers1