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") 



    Zoho Marketing Automation

              Zoho Pagesense Resources

                Zoho SalesIQ Resources



                      Zoho TeamInbox Resources

                        Zoho DataPrep Resources



                          Zoho CRM Plus Resources

                            Zoho Books Resources


                              Zoho Subscriptions Resources

                                Zoho Desk Resources

                                  Zoho Projects Resources


                                    Zoho Sprints Resources


                                      Qntrl Resources


                                        Zoho Creator Resources


                                          Zoho WorkDrive Resources




                                              Zoho Campaigns Resources

                                                Zoho CRM Resources

                                                        Design. Discuss. Deliver.

                                                        Create visually engaging stories with Zoho Show.

                                                        Get Started Now