Current MTD vs Previous MTD

I can compare (please check / confirm) current month to date MTD to previous whole months, but I would like to compare current MTD to prevous MTD months.
Today or Now()'s data does not include data from today.
Therefore, this month will be comparing to previous month -1 day.
Maybe Month(DateAdd("d",-1,Now())) ???

PATIENT DB STRUCTURE
Admit date   Date/time Format m/d/yyyy
Pat Num Long Interger
Pat Type Text
Service Code   Text
Tot Charges  Number Double

 
CURRENT QUERY
SELECT IIf(Left([Pat Type],1)="I","IN","OUT") AS INOUT, Patients.[Service Code],
Sum(IIf(Year(Now())=Year([admit date]) And Month([admit date])=Month(Now()),[tot charges],0)) AS Adm_CURMONCHGS,
Sum(IIf(Month([admit date])=Month(DateAdd("m",-1,Now())) And Year([admit date])=Year(DateAdd("m",-1,Now())),[tot charges],0)) AS Adm_PRVMONCHGS,
Sum(IIf(Month([admit date])=Month(Now()) And Year([admit date])=Year(DateAdd("yyyy",-1,Now())),[tot charges],0)) AS Adm_PRVYRCHGS
FROM Patients
GROUP BY IIf(Left([Pat Type],1)="I","IN","OUT"), Patients.[Service Code];
avgplusguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
Try the applicable function syntax below in your query:

-------------------Month To Date--------------------
The current month-to-date
 DateSerial(Year(Date()), Month(Date()), 1)
 
The next month-to-date
 DateSerial(Year(Date()), Month(Date()) + 1, 1)


-------------------Entire Fiscal Month-----------------------
The last day of the current month
 DateSerial(Year(Date()), Month(Date()) + 1, 0)
 
The last day of the next month
 DateSerial(Year(Date()), Month(Date()) + 2, 0)
 
The first day of the previous month
 DateSerial(Year(Date()), Month(Date())-1,1)
 
The last day of the previous month
 DateSerial(Year(Date()), Month(Date()),0)
 
0
lwadwellCommented:
I am not sure I fully understand you question ... may I confirm please.  Your current query obtains
a) the current months MTD;
b) the entire total for the previous month;
c) the entire total for the same month in the previous year.

Are you try to get the b) and c) values to be 'equivalent' MTD figures?  I say equivalent as these would be approximate only - especially with b) as the number of days in the month can differ (think compare MTD 30 March with Feb).

However - if this is what you want - perhaps something like ...

And Day([admit date]) < Day(Now())
0
Dale FyeCommented:
Let me restate what I think you are trying to do.  You want to sum, by [Pat Type] and [Service Code], the total charges for patients that were admitted ([Admit Date]) for the previous month (day 1 through the current day) and for the current month (day 1 through the current day), so that you can do a comparison.

You understand that the challenge with this method is that there are a varying number of days between months, so how do you handle the months of Feb/March, where March has 31 days and Feb only has 28 (or 29 on leap year).  If you are doing MTD on the 30th of March, do you want to count just all of Feb, or the 30 days starting on Feb 1st.

Personally, rather than trying to put all of that logic in a query, I would put it in a function.
 
Public Function IsMonthToDate(DateValue, Optional WhichMonth As Integer = 0, _
                              Optional ComparedTo As Variant = Null) As Boolean
                              
    'Determines whether the DateValue passed falls within the first n days of the month defined
    'by the ComparedTo date and the WhichMonth argument.
    'Which month is a numeric value which can be added or subtracted from the Month in ComparedTo
    
    If IsNull(ComparedTo) Then ComparedTo = Date
    If Day(DateValue) > Day(ComparedTo) Then
        'First, check to see if the day is less than the ComparedTo Date
        IsMonthToDate = False
    ElseIf Format(DateValue, "yymm") <> Format(DateAdd("m", WhichMonth, ComparedTo), "yymm") Then
        'Then check to see if DateValue is in the desired month
        IsMonthToDate = False
    Else
        IsMonthToDate = True
    End If
    
End Function

Open in new window


With this function, you could modify your query to:

SELECT IIf(Left([Pat Type],1)="I","IN","OUT") AS INOUT
           , Patients.[Service Code]
           , SUM(IIf(IsMonthToDate([admit date], 0), [tot charges], 0)) AS Adm_CURMONCHGS
           , SUM(IIf(IsMonthToDate([admit date], -1), [tot charges], 0)) AS Adm_PREVMONCHGS
           , Sum(IIf(Format([admit date], "yy") = Format(DateAdd("yyyy",-1,Now())),[tot charges],0)) AS Adm_PRVYRCHGS
FROM Patients
GROUP BY IIf(Left([Pat Type],1)="I","IN","OUT") AS INOUT, Patients.[Service Code]

And if you wanted to, you could even pass a date parameter into this query, so that you could run it for any date.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

avgplusguyAuthor Commented:
Sorry, I did not explain enough. Current query compares all of the current month (as of yesterday) to all of the previous month and to all of the current month last year.

GOTCHAS
I do not have today's data, only up until yesterday, Current date is really current date - 1
Months have different number of days.

NORMAL DAYS
December 29, 2011
Current Month Dec 1 -28. 2011
Current Month Previous Year Dec 1 -28, 2010
Previous Month Nov 1-28, 2011

If the report is for the last day of the month, then I want the previous month.
WORSE CASE SENERIO
On March 1, 2012, I want
Current month to be Feb 1 - 29
Current month previous year to be Feb 1-28
Previous month to be January 1 - 31

My data has to be reloaded to test fyed suggestion,
How hard is it to pass a date into a function? ie hit enter for today or  mm/dd/yy for some other day?

0
avgplusguyAuthor Commented:
HARD DAYS
March 31, 2011
Current Month  Mar 1 - 30, 2011
Current Month Prev Year Mar 1 - 30, 2010
Previous Month Feb 1 - 28, 2011

March 31, 2012
Current Month  Mar 1 - 30, 2012
Current Month Prev Year Mar 1 - 30, 2011
Previous Month Feb 1 - 29, 2011

August 1, 2011
Current Month  July 1 - 31, 2011
Current Month Prev Year July 1 - 31, 2010
Previous Month June 1 - 30
0
Dale FyeCommented:
OK,

I misunderstood your original post slightly.  To get current month, last year, you would need to use:

SELECT IIf(Left([Pat Type],1)="I","IN","OUT") AS INOUT
           , Patients.[Service Code]
           , SUM(IIf(IsMonthToDate([admit date], 0), [tot charges], 0)) AS Adm_CURMONCHGS
           , SUM(IIf(IsMonthToDate([admit date], -1), [tot charges], 0)) AS Adm_PREVMONCHGS
           , SUM(IIf(IsMonthToDate([admit date], -12), [tot charges], 0)) AS Adm_CURMONPREVYRCHGS
FROM Patients
GROUP BY IIf(Left([Pat Type],1)="I","IN","OUT") AS INOUT, Patients.[Service Code]

That is the nice thing about this function, you can pass any value for the WhichMonth argument to get a specific range.

Also, based on your input above, you would need to add a line of code between lines 8 and 9 which would subtract a day from the Compared To date

8:    If IsNull(ComparedTo) Then ComparedTo = Date
        ComparedTo = DateAdd("d", -1, ComparedTo)
9:    If Day(DateValue) > Day(ComparedTo) Then

>>How hard is it to pass a date into a function?
ANS: Easy, just pass in the field name, as I did above
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Will have to relook the logic of the function to account for the "Worst Case" above.

So, on March 1 of 2012, you want?

Current Month  Feb 1-29, 2012
Current Month Prev Year Feb 1-28, 2011
Previous Month Jan 1 - 31, 2012
0
avgplusguyAuthor Commented:
yes. I believe March 1, 2012 will produce the worse case serenio you described
 
0
Dale FyeCommented:
I think the following modifications to the function will meet your needs:

To test this, I would create a test table with two date fields [TestDate] and [ComparedTo].  I would then fill these in with a bunch of combinations of dates from your examples above and from your database.  Then I would run a query that looks like:

SELECT TestDate, ComparedTo, IsMonthToDate([TestDate], -1, [ComparedTo]) as WillBeIncluded
FROM tbl_TestData

If some combination of testdate/comparedTo date does not return the value you expect, then send me the table and I'll take another look.

Dale

Public Function IsMonthToDate(DateValue, Optional WhichMonth As Integer = 0, _
                              Optional ComparedTo As Variant = Null) As Boolean

    'Determines whether the DateValue passed falls within the first n days of the month defined
    'by the ComparedTo date and the WhichMonth argument.
    'Which month is a numeric value which can be added or subtracted from the Month in ComparedTo

    If IsNull(ComparedTo) Then ComparedTo = Date
    ComparedTo = DateAdd("d", -1, ComparedTo)
    If Format(DateValue, "yymm") <> Format(DateAdd("m", WhichMonth, ComparedTo), "yymm") Then
        'First check to see if DateValue is in the desired month, if not, reject
        IsMonthToDate = False
    ElseIf Day(DateValue) <= Day(ComparedTo) Then
        'Then check to see if the day value of DateValue is <= the day value of the ComparedTo date.
        'If it is, then it passes the test
        IsMonthToDate = True
    ElseIf Day(ComparedTo) > Day(DateSerial(Year(ComparedTo), Month(ComparedTo) + WhichMonth + 1, 0)) Then
        'If the Day of ComparedTo date is >= last day of the desired month, then it passes the test
        IsMonthToDate = True
    Else
        IsMonthToDate = False
    End If

End Function

Open in new window

0
avgplusguyAuthor Commented:
This was harder than I originally thought and then it was even harder.
Thank you all for your help  and prompting me to truly define the problem.
0
avgplusguyAuthor Commented:
I meant to check the last one, but you get the idea.
0
Dale FyeCommented:
Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.