Getting a MAX value of a column for date intersection

Database

Getting a MAX value of a column for date intersection

Hello,

I've been working on this problem for a while and need some advice. I'm not sure if what I'm trying to do is achievable without using a programming language. I can only use SQL in a BTEQ and no stored procedures are allowed.

Here's the problem:

Original data:







A1 1 11/08/2012 8/04/2013
A1 2 11/08/2012 31/12/9999
A1 3 9/04/2013 31/12/9999
A2 4 9/04/2013 5/05/2013
A2 6 9/04/2013 31/12/9999
A2 5 6/05/2013 31/12/9999
A3 7 2/06/2012 31/12/9999
A3 8 13/08/2012 31/12/9999
A3 9 4/11/2012 31/12/9999
A3 10 15/11/2012 31/12/9999

For each of A1, A2 and A3 I need to find a MAX value of the second column that is effective over an intersected period and also insert an extra row to cover the rest of the effective period during which another value of second column is a MAX for a given Ax. The row with MAX value will be flagged as a 'Y', the rest - 'N'. The idea is that for each of A1, A2 and A3 at any point in time there can be only 1 row flagged as 'Y'.

Sounds a bit messy so here is the desired result:








A1 1 11/08/2012 8/04/2013 N
A1 2 11/08/2012 8/04/2013 Y
A1 2 9/04/2013 31/12/9999 N
A1 3 9/04/2013 31/12/9999 Y
A2 4 9/04/2013 5/05/2013 N
A2 6 9/04/2013 31/12/9999 Y
A2 5 6/05/2013 31/12/9999 N
A3 7 2/06/2012 12/08/2012 Y
A3 7 13/08/2012 31/12/9999 N
A3 8 13/08/2012 3/11/2012 Y
A3 8 4/11/2012 31/12/9999 N
A3 9 4/11/2012 14/11/2012 Y
A3 9 15/11/2012 31/12/9999 N
A3 10 15/11/2012 31/12/9999 Y

If anyone could suggest a solution, I will be really grateful. Temporary or volatile tables are OK but no stored procs.

Thank you in advance!