Monthly Reports

[rjblanch]

Hi All,

I currently have a table that is holding records with names, date/time and costings based on a phone system.

My issue is that I need to run a monthly report so that I can grab the costs of the phone users for the last month.

I have worked out the queries except for 2 small things - 1) How do I write a query so that I grab only the last month data, knowing that months could have 28, 29, 30 or 31 days
2) How would be the easies way to automate this. Would be running off a windows 2k3 server or XP.

Thanks.

 

[TonyGroves]

When you say "the last month", do you mean since the same day in the previous month, during the current calendar month, or during the previous calendar month?

For the last 28/31 days, you could use:[tt]
WHERE datefield > CURDATE() - INTERVAL 1 MONTH
[/tt]
For the current calendar month, you could use:[tt]
WHERE datefield LIKE CONCAT(LEFT(CURDATE(),7),'%')
[/tt]
For the previous calendar month, you could use:[tt]
WHERE datefield LIKE CONCAT(LEFT(CURDATE() - INTERVAL 1 MONTH,7),'%')
[/tt]
I'm sure there are more efficient ways of doing those last 2 expressions, but
those should work.

 

[johnwm]

... WHERE MONTH(monthfield) = (MONTH(NOW)-1)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[TonyGroves]

johnwm:

Have you tested your suggestion? Have you checked your syntax? Have you considered the fact that the month might be January? Are you assuming the table contains only current-year records?

 

[johnwm]

Sorry Tony, I don't generally feel the need to give full-code answers. I was merely pointing out use of the MONTH function - it's up to OP to turn that into a full implementation if he feels it's useful.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller