4 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Compare service date to date span

    Mr. Recoupment

      I have been trying to find a way to link a line in the table view of a report that has a service date column to a database or report that has a date span.  The date span is currently in two different column's.

       

      I am able to link column's based a primary key like case number.  Each case has more than one  date span with an amount attached.

       

      Is the date in the date span?  If so then I use that amount on the line for the date of service.  Any suggestions?

        • Compare service date to date span
          Grant Perkins

          Hi,

           

          I can see how you could use the IF() function to 'filter' for the records where the date can be identified as being in the range but I can't see how you could successfully work the date as a Primary key link.

           

          Dates can be quite a challenge due to the several different internal formats that are possible and which are not obvious when looking at a  date displayed on the screen, it being an interpretation of some underlying data formatted for presentation purposes.

           

          If anyone does have a solution, or even an idea for an approach to the problem, I would love to hear about it.

           

           

          HtH

           

           

          Grant

          • Compare service date to date span
            Data Kruncher

            Let's see if I'm envisioning this correctly...

             

            You have a lookup table DATES (either a DB or a report) that has essentially this structure:

            [font="courier"]CASE #   START DATE   END DATE

            -


               -


               -


            997      01/01/06     01/31/06

            998      01/07/06     02/14/06

            999      01/21/06     02/28/06[/font][/quote]and the other report COSTS possibly like:

            [font="courier"]CASE #   CASE DATE     CASE COST

            -


               -


              -


            997      01/05/06       990.00

            998      03/01/06       800.00

            999      01/21/06       700.00 /font[/quote]If this is correct, big if, using an external lookup I would bring into your COSTS model both the Start Date and the End Date fields where the Case number is your lookup field.

             

            Then you can add a calculated Character field  named "Service Date". The formula would be:

            [font="courier"]IF((>=[Start Date]) .AND. (<=[End Date]),

            DTOC(),

            "")[/font][/quote]This would give you a date if the Case Date was within the date span, and nothing if the Case Date was outside the date span.

             

            Just an idea. Of course, my inital assumptions could be way off base, and all of this could be irrelevant.    :rolleyes:      smile.gif[/img]  

             

            Kruncher

             

            Edited to line up the headings a bit.

            • Compare service date to date span
              Mr. Recoupment

              I have a lookup table DATES (either a DB or a report) that has essentially this structure:

               

              DATES DB:

              -


               

              CASE #   START DATE   END DATE------   amount -


              997      01/01/05     12/31/06         $300

              997      01/01/03     12/31/04         $250

              997      01/01/00     12/31/02         $150

              998      01/01/05     12/14/06         $500

              998      01/01/03     12/31/04         $400

              999      01/21/06     02/28/06         $50

               

              -


               

              and the report CLAIMS like:

               

              CLAIMS TABLE:

              -


               

              CASE #   CASE DATE     amount----


              997      01/05/06        ?      

              997      01/01/03        ?

              998      01/21/06        ?

              998      01/01/04        ?

               

              -


               

              If you know the answer a second problem is if the end date is blank meaning through current date.  My initial table from the dates report references this as an invalid value and I cann't use it in a calculated field.  I quess my downside is I can filter these out and handle them seperately.

              • Compare service date to date span
                Grant Perkins

                Originally posted by Mr. Recoupment:

                If you know the answer a second problem is if the end date is blank meaning through current date.  My initial table from the dates report references this as an invalid value and I cann't use it in a calculated field.  I quess my downside is I can filter these out and handle them seperately. /b[/quote]From what I can tell (not tested but in principle) Kruncher's suggestion and formula should work unless you End Date is blank/reported as an error.

                 

                To deal with that you could change the IF formula to check the date it is exists or otherwise assume that if blank (or error 'message') then the date is OK. I assume that the case date cannot be after today.

                 

                An alternative approach would be to pre-handle the End Date issue using a calculated field that takes the end date if there is one or enters and acceptabel date - TODAY() for example? - if there is not. Then do the comparison with this 'work' field rather than the original. You may also want to consider whether you need to highlight that this was the approach used when outputting the results. It might seem a bit odd if a lot of claims occured on what appeared to be he last day of the range!

                 

                HTH.

                 

                Grant.