CODEWITHSUNDEEP
sqlms-access
spacerobot
spacerobot

Reputation: 297

How to add date range filter to count query

I have the following query that combines counts from 2 separate tables. I want to be able to filter that based on a date range. Here is the query I am working with:

SELECT combined.name, Count(combined.id) AS CountOfid
FROM (select ecmreq as name, entryid as id from log
    union all
    select comauthor, comid from elcom
 )  AS combined
GROUP BY combined.name;

If log has a date field called logdate and elcom has field called comdate how would I set this up to get all counts in between a range like 08/21/2017 and 08/28/2017?

Upvotes: 0

Views: 280

Answers (3)

dave
dave

Reputation: 1550

SELECT combined.name, Count(combined.id) AS CountOfid
FROM (select ecmreq as name, entryid as id, logdate as recdate from log
    union all
    select comauthor, comid, comdate as recdate from elcom
 )  AS combined
where combined.recdate between '08/21/2017' and '08/28/2017'
GROUP BY combined.name;

Upvotes: 1

justiceorjustus
justiceorjustus

Reputation: 1965

You just include it in the WHERE of your subqueries.

SELECT combined.name, Count(combined.id) AS CountOfid
FROM (select ecmreq as name, entryid as id from log WHERE logdate >= '2017-08-21' AND logdate <= '2017-08-28'
    union all
    select comauthor, comid from elcom WHERE comdate >= '2017-08-21' AND comdate <= '2017-08-28'
 )  AS combined
GROUP BY combined.name;

Upvotes: 1

TextGeek
TextGeek

Reputation: 1237

Is

WHERE logdate >= '2017-08-21' AND logdate <= '2017-08-28'

more or less what you're looking for?

Of course, you can check elcom the same way, and you can tweak the comparison in case you don't want to include one or the other actual boundary dates.

Upvotes: 0

Related Questions