# Thread: MTD + YTD calculation

1. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139

## Unanswered: MTD + YTD calculation

Hello All;

I have a table (tbl_MonthEndStats) that stores performance data for employees. One of the fields is Date. When the date data is entered, its entered as MM/30/31/YYYY. The monthly data is only entered once per month. I have a report, based on a query of the table, that I would like to list the current month’s performance, as well as the previous month’s, as well as the YTD data. I have created a query that retrieves the data for the current month, however I am stuck on how to get the Previous month and the YTD to populate to the form. I’m assuming I need a date calculation field in the query in order to populate the report, but I’m just stuck. Can anyone push me in the right direction?

Report Example:

Current Month Previous Month YTD

Metric 1 Metric 1 Metric 1
Metric 2 Metric 2 Metric2
Metric 3 Metric 3 Metric3

2. Registered User
Join Date
Apr 2003
Location
Alabama, USA
Posts
154
Say you have the following table design

tblData
dtDate (Date)
lngValue (Long)

You can use the following SQL to return the following:
PrevDate: - record with the most recent date that is less that the current record
PrevValue: - value for the sam record returned by PrevDate
YTD: Sume of values for all records that are equal to and less than the current record that are in the same year.

qyrPrevData
Code:
```SELECT tblData.dtDate, tblData.lngValue,
(SELECT TOP 1 a.dtDate
FROM tblData AS a
WHERE a.dtDate < tblData.dtDate
ORDER BY a.dtDate DESC) AS PrevDate,
(SELECT TOP 1 a.lngValue
FROM tblData AS a
WHERE a.dtDate < tblData.dtDate
ORDER BY a.dtDate DESC) AS PrevValue,
(SELECT Sum(a.lngValue)
FROM tblData AS a
WHERE (a.dtDate <= tblData.dtDate)
AND (Year(a.dtDate) = Year(tblData.dtDate))) AS YTD
FROM tblData;```
The above SQL will select the most recent date that is less than the current date. For the first record, it won't return anything since there is no date less it, assuming that it is the oldest date.

If there are breaks in data it will return the record with the closest previous date. That is if there is a date for January though July but is missing June. The previous date shown for June will be May.

If you want to only select the previous month, you can use DateDiff to return a value only if there was data for the preceeding month.

qryPrevMonth
Code:
```SELECT tblData.dtDate, tblData.lngValue,
(SELECT a.dtDate FROM tblData AS a
WHERE (DateDiff("m",a.dtDate, tblData.dtDate) = 1))
AS PrevMonth,
(SELECT a.lngValue
FROM tblData AS a
WHERE (DateDiff("m",a.dtDate, tblData.dtDate) = 1))
AS PrevValue,
(SELECT Sum(a.lngValue)
FROM tblData AS a
WHERE (a.dtDate <= tblData.dtDate)
And (Year(a.dtDate) = Year(tblData.dtDate)))
AS YTD
FROM tblData;```
Hope that helps!
Last edited by Cosmos75; 08-07-06 at 00:12.

3. Registered User
Join Date
Jul 2005
Posts
39
If you have tables like

tbl_MonthEndStats (empNo, date, empJob) and
tbl_FormDate (myDate) this is reference date for report query

would a query such as this help?

Code:
```SELECT tbl_MonthEndStats.empNo, Sum(IIf(Month([date])=Month([myDate]) And Year([date])=Year([myDate]),1,0)) AS jobThisMonth, Sum(IIf(Month([date])=Month([myDate])-1 And Year([date])=Year([myDate]),1,0)) AS jobLastMonth, Sum(IIf([date] Between [myDate] And [myDate]-366,1,0)) AS jobYearToDate
FROM tbl_MonthEndStats, tbl_FormDate
GROUP BY tbl_MonthEndStats.empNo;```

4. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Thanks for the replys Taurus and Casmos75. I will digest this and get back to you.

Larry

5. Registered User
Join Date
Apr 2003
Location
Alabama, USA
Posts
154
Larryg,

I created an article and sample, thought it might help.
- Previous Date & Sum of Month/Year to-date using a correlated subquery

Feel free to let me know if there is anything I can do to make it better or if you find any mistakes!

Hope that helps!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•