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:
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:
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!