Query to check for a CERTAIN amount of rows

[taimysho0]

Hello i have a table with two columns, ID and Date. There needs to be 24 rows of the same date (1 row per hour) for every day of the current month. My query will need to see if there are any missing intervals. For example, find where there are <24 rows for every day of the current month. This is how my table looks for ex:


ID Date
123 5/29/2012
123 5/29/2012
456 5/30/2012
456 5/30/2012

^There should be 24 rows of each ID for that particular day, so ID 123 should appear 24 times for 5/29/2012. If it is less than that, then it should appear on the query. Thanks so much!

[recyan]

Just check if below gives some guidelines : The code tags are not working. Check the next reply.

[recyan]

SELECT Table1.intID, Table1.dtDate, Count(Table1.dtDate) AS CountOfdtDateFROM Table1GROUP BY Table1.intID, Table1.dtDateHAVING (((Count(Table1.dtDate))

[taimysho0]

thanks for the help! my only question is where in this code designates that there should be 24 rows? thanks!

[recyan]

Sorry, there is some trouble going on :The missing end part ..................Fed up trying to add;Add Less Than symbol 24 and 2 Closing Brackets at the end of code.

[taimysho0]

something like this?

SELECT Table1.intID, Table1.dtDate, Count(Table1.dtDate) AS CountOfdtDateFROM Table1GROUP BY Table1.intID, Table1.dtDateHAVING (((Count(Table1.dtDate)) <24))

[recyan]

Yes,

but
a space before FROM
AND
a space before GROUP
AND
a space before HAVING.

Perhaps you have it already that way, but is appearing differently here.


Trying once again,

SELECT 
    Table1.intID, 
    Table1.dtDate, 
    Count(Table1.dtDate) AS CountOfdtDate
FROM 
    Table1
GROUP BY 
    Table1.intID, 
    Table1.dtDate
HAVING 
    (((Count(Table1.dtDate))<24));

Edit : Thank God, things are back to normal again
Thanks

[taimysho0]

his works great, the only thing is how do i get it to only pick up dates that are of the current month???thanks!!

[recyan]

Have you tried something like this :

..............
..............
HAVING (((Month([dtDate]))=Month(Date())) AND ((Count(Table1.dtDate))<24));

Thanks