Reputation: 153
I have a query which I got help with last night but I am stuck on another bit.
The code I have is
SELECT a.name, COUNT(*) AS num FROM table2 b
INNER JOIN table1 a
ON b.status_id=a.id
GROUP BY status_id
What I would like to do now is only show results if they have been entered on today's date? The date column is in table2
. The format for the date column is date and time (eg 1341241153) but I only need to check if the date matches the current day. I hope that is clear.
Any help will be gratefully received!
EDIT: this is a MySQL database. I have tried all the solutions so far and nothings worked. Sorry for not being clearer!!
FINAL EDIT: The question was not clear enough so I posted again in order to get a quick response. The new question with complete solution can be seen here a link
Upvotes: 0
Views: 432
Reputation: 3435
On SQL Server 2008 you can use this:
SELECT a.name, COUNT(*) AS num FROM table2 b
INNER JOIN table1 a
ON b.status_id=a.id
WHERE dateValue BETWEEN
CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
GROUP BY status_id
Upvotes: 0
Reputation: 387
Not sure which database type this is, if it is Oracle you just need to truncate the date:
SELECT a.name, COUNT(*) AS num FROM table2 b
INNER JOIN table1 a
ON b.status_id=a.id
WHERE TRUNC(SYSDATE) = TRUNC(b.dateColumn)
GROUP BY status_id
If it is MySQL or MSSQL the syntax is slightly different, but similarly simple.
Upvotes: 0
Reputation: 204924
if you group
you need a having
instead of a where
:
SELECT a.name, COUNT(*) AS num
FROM table2 b
INNER JOIN table1 a
ON b.status_id=a.id
GROUP BY status_id
having b.date = 1341241153
Upvotes: 1
Reputation: 3705
You could create your own function to do this if you do it quite often, but here's some code that flattens any date passed in to it:
SELECT a.name, COUNT(*) AS num
FROM table2 b
INNER JOIN table1 a ON b.status_id=a.id
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, a.DateColumn)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
GROUP BY status_id
Upvotes: 0