I have to write a SQL where in I have to write SQL for calculating the RunID first not seen. Let me explain with an example
Ex:
RunID | RunDate | ErrorID
----- | ---------- | ---------
101 | 04/11/2017 | 1
101 | 04/11/2017 | 2
101 | 04/11/2017 | 3
102 | 04/22/2017 | 2
102 | 04/22/2017 | 3
103 | 04/26/2017 | 1
104 | 04/27/2017 | 3
105 | 04/28/2017 | 4
In the above example RunID 101 has errors 1,2,3. RunID 102 has 2,3. During second run ErrorID 1 is not found. So, RunID first not seen here till now is 102 But ErrorID 1 is found again in RunID 103 and finally ErrorID 1 is not found in RunID 104. Query should give the RunIDs like 104 which are first not found.
I have tried using some window functions like lead and lag but it doesn't help.
Here are the expected results:
Date first not seen for ErrorID : 2
RunID | RunDate | ErrorID
----- | ---------- | ---------
103 | 04/26/2017 | 2
Because ErrorID 2 was never seen(first instance of not seen) after RunID-102
Date first not seen for ErrorID : 1
RunID | RunDate | ErrorID
----- | ---------- | ---------
104 | 04/27/2017 | 1
ErrorID 1 was never seen after RunID-104
Date first not seen for ErrorID : 3
RunID | RunDate | ErrorID
----- | ---------- | ---------
105 | 04/28/2017 | 3
ErrorID 3 was never seen after RunID-105
so=> with l as (
with m as (
select distinct max(runid) over(partition by errorid),errorid
from so80
)
, a as (
select distinct runid,errorid
from so80
)
select distinct min(a.runid) over (partition by m.errorid),m.errorid
from m
join a on m.max < a.runid
)
select s.*
from so80 s
join l on l.min=s.runid and s.errorid=s.errorid
;
runid | rundate | errorid
-------+--------------+---------
104 | 04/27/2017 | 3
103 | 04/26/2017 | 1
105 | 04/28/2017 | 4
(3 rows)
--Get the last runDate when an errorID was seen
with t1 as (select runId,runDate,errorID
,first_value(runDate) over(partition by errorID order by runDate desc
rows between unbounded preceding and unbounded following) as last_seen
from tablename
)
--Get the next runDate based on the previous result
,t2 as (select runid,errorID,runDate
,(select min(runDate) from t1 t11 where t11.runDate>t1.last_seen) as date_first_not_seen
from t1
)
--Join it to the original table to get the runID information from that runDate in the previous result (t2)
select distinct t.runid,t2.errorid,t.rundate
from t2
join tablename t on t.rundate=t2.date_first_not_seen
or
with t1 as (select runId,runDate,errorID
,first_value(runDate) over(partition by errorID order by runDate desc
rows between unbounded preceding and unbounded following) as last_seen
from tablename)
select distinct
t1.errorid
,first_value(t.runDate) over(partition by t1.errorID order by t1.runDate desc rows between unbounded preceding and unbounded following) as rundate
,first_value(t.runID) over(partition by t1.errorID order by t1.runDate desc rows between unbounded preceding and unbounded following) as runid
from t1
join tablename t on t.runDate>t1.last_seen