Hi Team -

I am having difficulty getting a UNION ALL query to work correctly.

For the first part of my query, I need to returns all requests that were created in the previous month, which I tried like this with no luck:

1. SELECT
   
         "Site"
   
       , "Request Custom FIelds"."Request Type" AS "Request Type"
   
       , "Sub Category"
   
       , "Request Status"
   
       , "Requester"
   
   FROM "Request"
   
   INNER JOIN "Request Custom Fields" ON "Request Custom FIelds"."RequestID" = "Request"."RequestID"
   
   WHERE "Request"."Created Time" = last_nmonth("Request"."Created Time", 1)
   

It's returning entries back since inception, which is wrong. It needs to return all entries from the PREVIOUS month no matter what day I am in in the CURRENT month.

And then the UNION ALL needs to be similar as the above with a different WHERE clause.  This piece should return all entries that have a created time OLDER than the previous month AND have been closed in the previous month

OR

have a created time OLDER than the previous month have not be closed  or resolved yet.

1. UNION ALL
   
   SELECT
   
         "Site"
   
       , "Request Custom FIelds"."Request Type" AS "Request Type"
   
       , "Sub Category"
   
       , "Request Status"
   
       , "Requester"
   
   FROM "Request"
   
   INNER JOIN "Request Custom Fields" ON "Request Custom FIelds"."RequestID" = "Request"."RequestID"
   
   WHERE ("Request"."Created Time" OLDER than previous month AND closed/resolved within the PREVIOUS month)
   
       OR "Request"."Created Time" OLDER than the previous month and "Request Status" not in ('Closed', 'Resolved')
   

Can somone help me with that?

UPDATE: 2-24-2019 5:58PM

I think I figured it out:

1. SELECT
            "Site",
            "Request"."RequestID",
            "Request Custom FIelds"."Request Type" AS "Request Type",
            "Sub Category",
            "Request Status",
            "Requester",
            "Created Time",
            "Completed Time",
            "Resolved Time"
   FROM  "Request" JOIN "Request Custom Fields" ON "Request Custom FIelds"."RequestID"  = "Request"."RequestID" 
   WHERE     "Request"."Created Time"  >= previous_nmonth(subdate(Current_Date(), day(Current_Date()) -1), 1)
    AND    "Request"."Created Time"  < subdate(Current_Date(), day(Current_Date()) -1)
   UNION ALL
    SELECT
            "Site",
            "Request"."RequestID",
            "Request Custom FIelds"."Request Type" AS "Request Type",
            "Sub Category",
            "Request Status",
            "Requester",
            "Created Time",
            "Completed Time",
            "Resolved Time"
   FROM  "Request" JOIN "Request Custom Fields" ON "Request Custom FIelds"."RequestID"  = "Request"."RequestID" 
   WHERE     ("Request"."Created Time"  < previous_nmonth(subdate(Current_Date(), day(Current_Date()) -1), 1)
    AND    "Request"."Completed Time"  < subdate(Current_Date(), day(Current_Date()) -1)
    AND    "Request"."Completed Time"  > subdate(Current_Date(), day(Current_Date()) -2))
    OR    ("Request"."Created Time"  < previous_nmonth(subdate(Current_Date(), day(Current_Date()) -1), 1)
    AND    "Request"."Resolved Time"  < subdate(Current_Date(), day(Current_Date()) -1)
    AND    "Request"."Resolved Time"  > subdate(Current_Date(), day(Current_Date()) -2))
    OR    ("Request"."Created Time"  < previous_nmonth(subdate(Current_Date(), day(Current_Date()) -1), 1)
    AND    "Request"."Request Status"  NOT IN ( 'Closed'  , 'Resolved'  ))