|
I been racking my brain on this, one of our department decided they wanted to pull from the last effective date of each member to send them a letter of coverage. I been trying to figure out how to do this in SQL, but it has become far too complex to do. Is there a way to do this in Crystal ? Below is sample date, So basically my understand I am going to take the trm date and then figure out if there was break in the date and report off of the date that does not break from the term. In this example, my term date is 2013-12-31, and the correct eff date should be 2012-06-01. Any suggestion or ideas ?
mem_id1 eff trm lname fname
-------- ----------------------- ----------------------- -------------------- ----------
0000 2007-06-04 00:00:00.000 2012-12-31 00:00:00.000 Doe Jon
0000 2007-07-01 00:00:00.000 2007-12-31 00:00:00.000 Doe Jon
0000 2008-01-01 00:00:00.000 2008-12-31 00:00:00.000 Doe Jon
0000 2009-01-01 00:00:00.000 2009-01-31 00:00:00.000 Doe Jon
0000 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 Doe Jon
0000 2009-02-01 00:00:00.000 2009-05-31 00:00:00.000 Doe Jon
0000 2009-06-01 00:00:00.000 2009-10-31 00:00:00.000 Doe Jon
0000 2009-06-01 00:00:00.000 2009-12-31 00:00:00.000 Doe Jon
0000 2009-11-01 00:00:00.000 2009-12-31 00:00:00.000 Doe Jon
0000 2010-01-01 00:00:00.000 2010-02-28 00:00:00.000 Doe Jon
0000 2010-01-01 00:00:00.000 2010-04-30 00:00:00.000 Doe Jon
0000 2010-03-01 00:00:00.000 2010-04-30 00:00:00.000 Doe Jon
0000 2012-06-01 00:00:00.000 2012-07-31 00:00:00.000 Doe Jon
0000 2012-08-01 00:00:00.000 2012-08-31 00:00:00.000 Doe Jon
0000 2012-09-01 00:00:00.000 2012-12-31 00:00:00.000 Doe Jon
0000 2013-01-01 00:00:00.000 2012-12-31 00:00:00.000 Doe Jon
0000 2013-01-01 00:00:00.000 2013-12-31 00:00:00.000 Doe Jon
|
IP Logged
