Need help finding correct start dates

Database
N/A

Need help finding correct start dates

Need help finding the correct start date for acct_2. Input is sorted by start_date. 

Please find below Input and Output:

Input:

acct_1 start_date end_date acct_2
101 4/28/2016 5/2/2016 0
101 5/2/2016 5/3/2016 201
101 5/3/2016 5/5/2016 0
101 5/5/2016 5/7/2016 0
101 5/7/2016 5/9/2016 202
101 5/9/2016 12/31/9999 203
102 4/29/2016 6/2/2016 0
102 6/2/2016 6/5/2016 0
102 6/5/2016 6/7/2016 0
102 6/7/2016 12/31/9999 205

Output:

acct_2 start_date
201 4/28/2016
202 5/3/2016
203 5/9/2016
205 4/29/2016
Tags (2)
2 REPLIES
Enthusiast

Re: Need help finding correct start dates

SEL DISTINCT A5,MIN(A2) OVER (PARTITION BY A1,A5 ORDER BY A2 ASC) AS A6 FROM 

(SEL A.*,SUM(ACCT_2)  OVER (PARTITION BY ACCT_1 ORDER BY START_DATE DESC RESET WHEN ACCT_2 <> 0 ROWS UNBOUNDED PRECEDING) AS A5 FROM T1 A) A

Junior Contributor

Re: Need help finding correct start dates

In TD14.10 you can also use FIRST/LAST_VALUE:

SEL ...
LAST_VALUE(NULLIF(ACCT_2, 0) IGNORE NULLS)
OVER (PARTITION BY ACCT_1
ORDER BY START_DATE DESC
ROWS UNBOUNDED PRECEDING)
FROM tab