All Forums Database
Bd1002 1 post Joined 05/16
08 May 2016
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:
yuvaevergreen 93 posts Joined 07/09
09 May 2016

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
 
 

dnoeth 4628 posts Joined 11/04
09 May 2016

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

 

Dieter

You must sign in to leave a comment.