Oracle Data Grooming

1. There are four fields in the table: Personnel number, start time, end time, type, data ID, need to implement the following requirements

A. When the type is-1, the record is discarded

B. When the type is-1, and the previous line end time is null, the start time of the current line-1 as the end time of the previous line

C. If the later time is earlier than the previous time, overwrite the previous time, the time that cannot be overwritten is preserved

D. Overlapping time periods to be merged into one line

 withX1 as(SelectUserid,startdate,COALESCE(min( Case  whenType=-1  ThenAdd_months (StartDate,-1)ElseStartDateEnd) Over(Partition byUseridOrder  byVid rowsbetween 1Following andunbouded following), StartDate+1) asMinsdate, EndDate asOrgenddate, Case  whenEndDate is NULL  and(Lead (type) Over(Partition byUseridOrder  byVID))=-1  ThenAdd_months (Lead (StartDate) Over(Partition byUseridOrder  byVID)),-1)ElseEndDateEnd  asEnddate,type,vid,Max(VID) Over(Partition byUserID asmax_id fromTest, X2 as(Selectuserid,startdate,minsdate,enddate,type,vid,max_id, Case  when(Lag (EndDate) Over(Partition byUseridOrder  byVID))<Add_months (StartDate,-1) Then 1   when(Lag (type) Over(Partition byUseridOrder  byVID))=1  Then NULL Else 1 End  asSo fromx1), X3 as (SelectUserid,vid,max_id,type,sum(SO) Over(Partition byUseridOrder  byVid asSo, startdate,minsdate, Case  whenMinsdate<EndDate andMinsdate>=StartDate ThenMinsdateElseEndDateEnd  asEndDate fromX2whereType=1  andStartDate<=minsdate), X4 as(SelectUSERID,MAX_ID,Max(VID) asMax_id2,sum(type) astype,min(startdate) Keep (Dense_rank firstOrder  byVid asStartDate,Max(EndDate) Keep (Dense_rank lastOrder  byVid asEndDate fromx3)SelectUserid,to_char (StartDate,'yyyymm')||'--'||COALESCE(To_char (EndDate,'yyyymm'),'NULL') asRangespace fromX4where(max_id=Max_id2orStartDate<=EndDate andType>-1;

Oracle Data Grooming