We have created a new Universe and added two tables (call_req + act_log) from database to get the report.
Report got generated and saved. We got the below sql query after generating the report.
SELECT
call_req.ref_num,
ca_contact2.middle_name,
ca_contact2.first_name,
ca_location.location_name,
ca_site.name,
prob_ctg.sym,
call_req.description,
call_req.status,
DATEADD(ss,call_req.open_date,'1970/01/01 05:30:00')
,
DATEADD(ss,call_req.close_date,'1970/01/01 05:30:00')
,
ca_contact.last_name+' , '+ ca_contact.first_name,
act_log.type,
act_log.description
FROM
call_req,
ca_contact ca_contact2,
ca_location,
ca_site,
prob_ctg,
ca_contact,
act_log
WHERE
( act_log.call_req_id=call_req.persid )
AND ( call_req.category=prob_ctg.persid )
AND ( ca_contact.contact_uuid=call_req.assignee )
AND ( call_req.customer=ca_contact2.contact_uuid )
AND ( ca_contact2.location_uuid=ca_location.location_uuid )
AND ( ca_location.site_id=ca_site.id )
AND
act_log.type IN ( 'CL','ST' )
Now We want to schedule the daily report so we can get the previous day data. for that I have added below query in where clause;
AND
call_req.close_date > dateadd(day,datediff(day,1,getdate()),0)
and validated the query and got no error in query.
When again tried to run the query after adding the additional query got below error;
Kindly help with the addition query to schedule the report to get previous day close tickets.
Regards,
Kripesh