Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-09-2013
11:41 PM

05-09-2013
11:41 PM

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!