Need help with date conversion - extract Month()

Need help with date conversion - extract Month()

Need help with date conversion/math.

Here's my code. The "Predicted Receive" is a simple CRM date field in a custom module.

current_date() must be return a date-time and extracting "month" from "Predicted Receive" is incorrect...I think.

  1. SELECT
  2. Vendors."Vendor Name" as "Vendor",
  3. Vendors."Monthly Capacity" as "Monthly Capacity",
  4. sum("Production Tickets".Quantity) as "In Production",
  5. Vendors."Monthly Capacity" -sum("Production Tickets".Quantity) as "Open Capacity"
  6. FROM  Vendors
  7. JOIN "Production Tickets" ON Vendors.Id  = "Production Tickets".Vendor
  8. AND "Production Tickets".Vendor  IS NOT NULL  
  9. WHERE NOT Vendors."Monthly Capacity"  IS NULL
  10.  AND Vendors."Monthly Capacity"  > 0
  11.  AND ((Month(current_date())  = Month("Production Tickets"."Predicted Receive")
  12.  AND Year(current_date())  = Year("Production Tickets"."Predicted Receive"))
  13.  OR (Month(previous_nmonth(current_date(), 1))  = Month("Production Tickets"."Predicted Receive")
  14.  AND Year(previous_nmonth(current_date(), 1))  = Year("Production Tickets"."Predicted Receive")))
  15. GROUP BY Vendors."Vendor Name",
  16. Vendors."Monthly Capacity",
  17. Month("Production Tickets"."Predicted Receive"),
  18.   Year("Production Tickets"."Predicted Receive")