Arch Linux

nadman10

Member

From: USA

Registered: 2005-10-15

Posts: 202

[SOLVED]Monthly Report Generation Logic

Hello,

I am trying to run a monthly report for the tickets in our ticketing system with a MySQL query. Basically, every ticket has a creation date. Once it is closed, it is assigned a closed date. Easy enough.

Let's say the current month is September and I want to run a report on tickets that were open at any point in July:

                                    | MAY | JUNE | JULY | AUG | SEP | OCT |

All I should have to to is look for tickets that were opened or closed in July. But what about a ticket that was opened in June(or any other previous month) and wasn't closed until August? This should be easier than I am making it out to be. Thanks in advance for any help.

*Edited to specify MySQL*

Last edited by nadman10 (2014-09-25 14:19:13)

Trilby

Inspector Parrot

Registered: 2011-11-29

Posts: 30,456

Website

Re: [SOLVED]Monthly Report Generation Logic

How is this data stored?  If it is in, or can be converted to 'unix time' then this would be trivial:

if (ticket_open < report_time && (!ticket_close || ticket_close > report_time))
   include_ticket

---

"UNIX is simple and coherent" - Dennis Ritchie; "GNU's Not Unix" - Richard Stallman

nadman10

Member

From: USA

Registered: 2005-10-15

Posts: 202

Re: [SOLVED]Monthly Report Generation Logic

I should have specified that this is a MySQL query stored in DATETIME format. I didn't see a sticky explaining that MySQL queries didn't belong here.

Last edited by nadman10 (2014-09-25 13:43:29)

nadman10

Member

From: USA

Registered: 2005-10-15

Posts: 202

Re: [SOLVED]Monthly Report Generation Logic

SELECT *
FROM tickets
WHERE openDate = '$year-$month%'
   OR closeDate = '$year-$month%'
   OR (openDate < '$year-$month%' AND closeDate = NULL);