Hi all.

I need to create a report where i can have the total activity (new created tickets, plus, tickets with activity but created before the creation date) on a given schedule (one week, 3 days, all year, whatever).

Since i cannot create just 1 reports with all this information i am creating 2 separated reports:

1 - Report with the total tickets created in a given time:

Columns: ID Ticket + Creation time:

SELECT wo.WORKORDERID "ID de la solicitud",wo.CREATEDTIME "Hora de creación" FROM WorkOrder wo WHERE  (((wo.CREATEDTIME >= datetolong('1586124000000') AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= datetolong('1586383199000') AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))  AND wo.ISPARENT='1'

This is OK. Gives ALL new tickets created in Servide Desk between 2020-04-06 to 2020-06-08 (3 days). This is OK.

Now i need to get ALL tickets where any response was given by any technicians inside this same period time, but the creation time was before 2020-04-06. The date Filter only allows to choose:

-Answer Date

-Expiration Time

-Ending Time

-Response expiration time

-Last Update Time

-Resolution time

So i believe i have to choose "Last Update Time" and get tickets which last update time in inside the given date.:

SELECT wo.WORKORDERID "ID de la solicitud",wos.LAST_TECH_UPDATE "Hora de la última actualización" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID WHERE  (((wo.RESPONDEDTIME >= datetolong('2020-04-06') AND ((wo.RESPONDEDTIME != 0) AND (wo.RESPONDEDTIME IS NOT NULL))) AND ((wo.RESPONDEDTIME <= 1586383199000) AND (((wo.RESPONDEDTIME != 0) AND (wo.RESPONDEDTIME IS NOT NULL)) AND (wo.RESPONDEDTIME != -1))))  AND wo.ISPARENT='1'

But how can i express NOT TO INCLUDE  tickets created in this given time? How to specify to only count does tickets with activity but with creation date BEFORE given time?

Can someone help?

The ideal solution is to make only one request that gives me all that i need, But my SQL is very very rusty so i'm trying to do it in 2 stpes.

Many thanks.