SQL Script help

[mcraighead]

I've created a sql statement that I'm try get the number of messages that will expire during a certain time period. I'm looking to purge messages according to mm/dd/yyyy. I'm having a world of difficulty figuring the statement format out assistence would be appriciate.

SQL script:

select originationtime, expirationtime, ((TO_DATE('01/01/1970', 'MM/DD/YYYY')) + (originationtime/(86400000.0)) - (5.0/24.0)), ((TO_DATE('01/01/1970', 'MM/DD/YYYY')) + (expirationtime/(86400000.0)) - (5.0/24.0)) from messages

gives me the following:

originationtime expirationtime
dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm

this will go on for 600,000 rows with different dates and times I need to obtain a count of the number of messages that with a expirationtime of say 06/29/2011 then do the same for messages with an expirationtime for 09/15/2007. Thanks...

[Nige Ridd]

Don't you just need to group on expirationtime?

Something like
select expirationtime, count(expirationtime)
from messages
group by expirationtime;

This will give you a list of the distinct expirationtime and the amount of times they occur.

Of course you'd have to replace the expirationtime in the above statement with your calculation of how it's derived from the database.

Nige