finding min and max staus

Database
Enthusiast

finding min and max staus

Hi Friends,

I have a requirement from clint like below

my input is 

ACCOUNT_IDPRODUCT_IDSTART_DATEEND_DATEPREV_FOR_STATUSCURR_FOR_STATUS
397,3941,28612/10/201512/20/2015In ProgressRejected
235,8631,34812/10/201512/20/2015UnrestrictedIn Progress
397,3941,28612/21/201512/25/2015RejectedApproved
316,1681,11612/26/201512/30/2015UnrestrictedIn Progress
397,3941,2861/10/20161/15/2016ApprovedRejected
235,8631,3481/16/20161/20/2016In ProgressApproved
316,1681,1161/21/20161/25/2016In Progress

Blacklist

 

The oupt is

ACCOUNT_IDPRODUCT_IDSTART_DATEEND_DATEPREV_FOR_STATUSCURR_FOR_STATUS
397,3941,28612/10/201512/20/2015In ProgressApproved
235,8631,34812/10/201512/20/2015UnrestrictedIn Progress
316,1681,11612/26/201512/30/2015UnrestrictedIn Progress
397,3941,2861/10/20161/15/2016ApprovedRejected
235,8631,3481/16/20161/20/2016In ProgressApproved
316,1681,1161/21/20161/25/2016In Progress

Blacklist

397,394&1,286 for this combination  there are multiple records in the month

but i want one record that is min START_DATE of PREV_FOR_STATUS and max START_DATE of CURR_FOR_STATUS

like below

397,3941,28612/10/201512/20/2015In ProgressApproved

Thanks,

Ram

3 REPLIES
Enthusiast

Re: finding min and max staus

Hi Chandu,

 

Please try the below query, hope it will work for you.

 

with temp(AID, PID, ms, ed, pre_s, cur_s) as
(
sel distinct I.Account_ID, I.Product_ID, start_date, end_date, case when start_date = min_sd then Prev_for_Status end as pre_s,
case when start_date = max_Sd then curr_for_Status end as curr_s
from status O
join (sel Account_ID, Product_ID, min(start_date) min_sd, max(start_date) max_Sd from status group by Account_ID, Product_ID) I on O.Account_ID = I.Account_ID and O.Product_ID = I.Product_ID
)

sel A.AID, A.PID, B.ms, B.ed, coalesce(A.pre_s, B.pre_s) as pre_s, coalesce(A.cur_s, B.cur_s) as cur_s from temp a join temp b on A.AID = B.AID and A.PID = B.PID
where B.pre_s is not null and A.cur_S is not null;

 

Enthusiast

Re: finding min and max staus

Thanks for your reply

but unfortunately i am not belongs to teradata

it is difficult to understand 

could you please simplified and send me 

New Member

Re: finding min and max staus

I believe this query will give you the required output :

 

sel
I.Account_ID, I.Product_ID, min_sd, max_ed,
max(case when start_date = min_sd then Prev_for_Status Else null end) as pre_s,
max(case when end_date = max_ed then curr_for_Status Else null end) as curr_s
from status O
join
(sel Account_ID, Product_ID, min(start_date) min_sd, max(end_date) max_ed from status group by Account_ID, Product_ID)
 I
 on O.Account_ID = I.Account_ID
 and O.Product_ID = I.Product_ID
 
 group by 1,2,3,4;