2 Replies Latest reply on Apr 9, 2014 8:07 AM by Jason Guinn

    Calculate Number of Days Within Current Month Given Two Date Fields

    Jason Guinn
    Jason Guinn Apr 3, 2014 6:22 AM

      I have searched through the forums and unfortunately am still struggling with a DATEDIFF problem. I'll try to provide as much detail as possible below. Thanks in advance for any advice on how to elegantly calculate this data.

       

      We bill for some services on a per diem basis and I need to calculate the revenue that is being generated MTD for these services. I have two fields in the database - StartDt and EndDt - and either of these could be null.

       

      • If StartDt is less than the first day of the current month, I want to start counting at the first day of the current month
      • If StartDt is within the current month, I want to start counting at the StartDt
      • If StartDt is null, completely exclude - no MTD revenue generated
      • If EndDt is less than the first day of the current month, completely exclude - no MTD revenue generated
      • If EndDt is within current month, use EndDt
      • If EndDt is null, use today
      • If EndDt is greater than the last day of the current month, use the last day of the current month

       

      Ultimately, I'm looking to see something like a crosstab where a patient ID is in one column and the number of days within the current month that the patient was eligible for the per diem billing rate is in the detail for each row. Again, any and all advice is tremendously appreciated!

        • 1. Re: Calculate Number of Days Within Current Month Given Two Date Fields
          Enoch Soames
          Enoch Soames Apr 3, 2014 8:35 AM (in response to Jason Guinn)

          I think that that formulas that you'll want to become familiar with are:

          DATETRUNC which clips a date to a datepart - ie DATETRUNC('month',TODAY()) returns 4/1/2014.

          DATEADD which came in handy in obtaining the last day of the month (there has got to be a more elegant way to do this!).

          ISNULL - returns true if test returns null - used to look for null EndDt and replace with today per one of your conditions.

           

          I'm certain that there are folks here who could do this more elegantly, but I solved it as follows:

           

          Created 2 new Dimensions called StartDt_Calculated and EndDt_Calculated with the following formulas:

           

          StartDT_Calculated

          IF DATETRUNC('month',[StartDt]) <= today()

              THEN DATETRUNC('month',today())

          END

           

          EndDt_Calculated

          IF [EndDt] < DATETRUNC('month',NULL)

              THEN TODAY()

          ELSEIF DATETRUNC('month',[EndDt]) = DATETRUNC('month',TODAY())

              THEN [EndDt]

          ELSEIF DATETRUNC('month',[EndDt]) > DATETRUNC('month',TODAY())

              THEN DATETRUNC('month',DATEADD('month',1,TODAY()))-1

          ELSEIF ISNULL([EndDt])= TRUE

              THEN TODAY()

          END

           

          I believe that these two formulas satisfy all of the conditions that you outlined in your post. From there I used a DATEDIFF to compare the two calculated values:

           

          DATEDIFF ('day',[StartDt_Calculated],[EndDt_Calculated])

           

          This returns the number of days eligible in the current month.

           

          Attached is an example. Hope this helps!

           

          Enoch

          • 2. Re: Calculate Number of Days Within Current Month Given Two Date Fields
            Jason Guinn
            Jason Guinn Apr 9, 2014 8:07 AM (in response to Enoch Soames)

            Enoch,

            Thank you SO much for your assistance with this project. It appears that this works perfectly for the purpose intended. Can't thank you enough for your help. The calculations make much more sense now that I see them in a complex example.


            Tremendously appreciative,

            Jason