SQL window function with where clause

Question

I'm using teradata. I have a table with two date columns and a product code. Like so

Prod ID     StartDate       EndDate
123         1-1-2013        1-5-2013
123         1-3-2013        1-10-2013
123         1-4-2013        1-10-2013
321         1-4-2013        1-10-2013
321         1-6-2013        1-12-2013
321         1-5-2013        1-12-2013

I need to write a query which will give me the minimum start date of the following 'end date' for the same product. So in the above example, the first product would return '1-3-2013', and for the second product I would receive '1-5-2013' cause the next end date '1-12-2013 is a duplicate, and I just need to get the minimum instance of it's 'start date'.

I have a feeling it's something like

min(startDate) over (partition by prodID order by endDate rows between 1 following and 1 following)

However, I feel I need a qualify statement in here which will make sure that the startDate of the next row is greater than the endDate of the current row.

Can you show what you're expected output is as another table please? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 25, 2014 at 7:19

dnoeth · Accepted Answer · 2014-02-25 06:53:19Z

2

The minimum end_date of the following rows should be

min(startDate) 
over (partition by prodID 
order by endDate
rows between 1 following and unbounded following)

Maybe you need to add startDate to the ORDER BY.

And startDate of next row greater than endDate of current row:

qualify
   min(startDate) 
   over (partition by prodID 
   order by endDate
   rows between 1 following and 1 following) > endDate

answered Feb 25, 2014 at 6:53

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL window function with where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related