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

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

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())

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

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