Hi,

I'm having trouble with the following query within the "NEW QUERY REPORT" function of Service Desk Plus. Individually each of the 3 following subqueries run fine. When I union subquery 1 with subquery 2 no problem. But as soon as I union the 3rd subquery I get a failure. Also I cannot union subquery 3 individually with 1 or 2. What am I missing here?

Ps. This query worked fine with hard-coded dates (as long) in place of the functions.

Thanks,

Paul

SELECT rs.[Request Title], rs.[Request Count] FROM
(

SELECT '1. Requests Open At Start Of Previous Month' "Request Title", COUNT(wo.WORKORDERID) "Request Count" FROM

WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID WHERE  (((wo.CREATEDTIME <

<from_lastmonth>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))) AND

((wo.COMPLETEDTIME >= <from_lastmonth>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND

((wo.COMPLETEDTIME <= <to_lastmonth>) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)) AND

(wo.COMPLETEDTIME != -1))))  AND wot.THD_WOID=wot.WORKORDERID

UNION

SELECT '2. Requests Closed Within Previous Month' "Request Title", COUNT(wo.WORKORDERID) "Request Count" FROM

WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID WHERE  (((wo.COMPLETEDTIME >=

<from_lastmonth>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <=

<to_lastmonth>) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)) AND (wo.COMPLETEDTIME != -1)))) 

AND wot.THD_WOID=wot.WORKORDERID

UNION

SELECT '3. Requests Open At End Of Previous Month' "Request Title", COUNT(wo.WORKORDERID) "Request Count" FROM WorkOrder_Threaded

wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos

ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID

WHERE (((wo.CREATEDTIME < <from_thismonth>) AND (wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL) AND (wo.CREATEDTIME != -1)

AND (wo.COMPLETEDTIME >= <from_thismonth>) AND (wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)) 

OR ((wo.CREATEDTIME < <from_thismonth>) AND (wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL) AND (wo.CREATEDTIME != -1)

AND ((std.STATUSNAME != N'Closed') OR (std.STATUSNAME IS NULL)) AND ((std.STATUSNAME != N'Resolved') OR (std.STATUSNAME IS NULL))))

AND wot.THD_WOID=wot.WORKORDERID

) AS rs