Counting records by month based on two different date fields

[pgregory]

I have a set of records with two date fields--DateAssigned and DateCompleted--and I am trying to create a query that will separately count the number of records assigned and completed in each month. I'm attaching a screenshot with some sample data.



Based on the sample data in the screenshot below, for November 2012, the query would show that there are two records with a DateAssigned in that month and zero records with a DateCompleted in that month. It would show that two records were assigned in December 2012 and three completed that month. And so on.

Thanks for any help you can provide!!

Desired Query Output

Month	Count of DateAssigned	Count of DateCompleted
2012-11	2	0
2012-12	2	3

Sample Data

[isladogs]

I did this in 4 queries though someone else may have a better plan

a) Union query to get all the months from both date fields

SELECT Year([DateAssigned]) & "-" & Month([DateAssigned]) AS YearMonth
FROM Table1
GROUP BY Year([DateAssigned]) & "-" & Month([DateAssigned]);
UNION SELECT Year([DateCompleted]) & "-" & Month([DateCompleted]) AS YearMonth
FROM Table1
GROUP BY Year([DateCompleted]) & "-" & Month([DateCompleted]);

b) Count by month for assigned

SELECT Year([DateAssigned]) & "-" & Month([DateAssigned]) AS YearMonth, Count(Table1.DateAssigned) AS CountOfDateAssigned
FROM Table1
GROUP BY Year([DateAssigned]) & "-" & Month([DateAssigned]);

c) Count by month for completed

SELECT Year([DateCompleted]) & "-" & Month([DateCompleted]) AS YearMonth, Count(Table1.DateCompleted) AS CountOfDateCompleted
FROM Table1
GROUP BY Year([DateCompleted]) & "-" & Month([DateCompleted]);

d) final query joining the union query to the other two using outer joins to get all months & Nz function to get zeroes

SELECT qryYearMonth.YearMonth, Nz([CountOfDateAssigned],0) AS CountAssigned, Nz([CountOfDateCompleted],0) AS CountCompleted
FROM qryCountAssignedMonth RIGHT JOIN (qryCountCompletedMonth RIGHT JOIN qryYearMonth ON qryCountCompletedMonth.YearMonth = qryYearMonth.YearMonth) ON qryCountAssignedMonth.YearMonth = qryYearMonth.YearMonth;

Results: NOTE that I may not have copied your data correctly

YearMonth	CountAssigned	CountCompleted
2012-11	2	0
2012-12	2	3
2013-1	4	1
2013-2	0	3
2013-3	2	1
2013-4	0	2

[pgregory]

Thank you for this! I will take a look at it and let you know if I have any questions.

[Dehn0045]

I am fairly new to this, and haven't totally digested what Colin is doing with the four queries, but I will comment that this method doesn't respond as one might expect when there are months with neither assigned or completed tasks. For example, when I change all of the January dates to February, you get this result:

When working with a similar situation, I expected to see this:


In my case it was simpler because I was only looking at a single column. I was able to resolve the issue by building a table with possible months and then counting the UNION of the possible month table and the subject table (subtracting 1 from the count to account for the records from the possible month table).

I'm sorry that I can't offer a specific resolution, it was a bit of a struggle getting it to work for my simple case. If this is what you are looking for, maybe some of the more advanced folks can help you, otherwise it looks like Colin's approach should work.

[isladogs]

Hi Dehn & welcome to the forum

Thank you for your contribution
You're quite right. The solution I gave will only give data for months that appear in one of the two date columns.
That's because it's getting the months from the data provided.

If that's an issue, I would also use a reference table listing all the month and year values

UPDATE:
Attached is an amended version where I've done exactly that
a) added tblYearMonth including a couple of months with no data 2012-10 & 2013-5
b) created new final query qryFinalCountByMonthNEW with SQL:

SELECT tblYearMonth.YearMonth, Nz([CountOfDateAssigned],0) AS CountAssigned, Nz([CountOfDateCompleted],0) AS CountCompletedFROM qryCountAssignedMonth RIGHT JOIN (qryCountCompletedMonth RIGHT JOIN tblYearMonth ON qryCountCompletedMonth.YearMonth = tblYearMonth.YearMonth) ON qryCountAssignedMonth.YearMonth = tblYearMonth.YearMonth
ORDER BY tblYearMonth.YearMonth;

This now gives results including months with no data:

YearMonth	CountAssigned	CountCompleted
2012-10	0	0
2012-11	2	0
2012-12	2	3
2013-1	4	1
2013-2	0	3
2013-3	2	1
2013-4	0	2
2013-5	0	0

The original UNION query is no longer required using this approach

HTH