Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ PHP, Perl, MySQL and Postgres _ Query Using Calendar Table Without Future Dates

Posted by: KantWin Jun 25 2019, 07:14 PM

Good evening,

Using MySQL
I have a calendar table that I'm using as a join to an Orders table to derive months without any orders.
The calendar table isn't a true calendar table, as it has only month and year.
Without the calendar table, I could not figure out how to show the months and years without sales.
My problem is that this process also shows zero sales for the rest of the year.
How can I restrict it to show only up to the current month, or even last month, as a limit?

CODE
SELECT concat(a.MonthNumber, ', ', a.YearNumber) as MonthYearNoSale, a.SalesNoCount
FROM (
SELECT  Months.MonthTextLong as MonthNumber, calendar_date.YearNumbers as YearNumber, COUNT(Orders.PaymentDate) AS SalesNoCount
FROM calendar_date
LEFT JOIN  Orders on calendar_date.YearNumbers=year(Orders.PaymentDate) and calendar_date.MonthNumbers=month(Orders.PaymentDate)
LEFT JOIN Months on calendar_date.MonthNumbers=Months.MonthNumber
WHERE calendar_date.YearNumbers BETWEEN 2005 AND year(CURDATE())

GROUP BY calendar_date.YearNumbers, calendar_date.MonthNumbers
ORDER BY calendar_date.YearNumbers, calendar_date.MonthNumbers ) a
having a.SalesNoCount = 0


RESULTS -
CODE
MonthYearNoSale    SalesNoCount    
January, 2005    0    
April, 2005    0    
November, 2013    0    
July, 2017    0    
November, 2017    0    
June, 2019    0    
July, 2019    0    
August, 2019    0    
September, 2019    0    
October, 2019    0    
November, 2019    0    
December, 2019    0


Posted by: ITguaranteed Jun 25 2019, 07:23 PM

Untested

up to last month

CODE
WHERE (calendar_date.YearNumbers BETWEEN 2005 AND year(CURDATE())-1) or ( calendar_date.YearNumbers = year(CURDATE()) and calendar_date.MonthNumbers < month(CURDATE()) )


or up to and including current month

CODE
WHERE (calendar_date.YearNumbers BETWEEN 2005 AND year(CURDATE())-1) or ( calendar_date.YearNumbers = year(CURDATE()) and calendar_date.MonthNumbers <= month(CURDATE()) )

Posted by: KantWin Jun 25 2019, 07:25 PM

I just could not get my head wrapped around the WHERE.

Works exactly as I hoped it would.

Thank you!