UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Using Calendar Table Without Future Dates, MySQL    
 
   
KantWin
post Jun 25 2019, 07:14 PM
Post#1



Posts: 605
Joined: 1-August 02
From: Alabama


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


--------------------
KantWin
Go to the top of the page
 
ITguaranteed
post Jun 25 2019, 07:23 PM
Post#2



Posts: 29
Joined: 19-June 19
From: Tasmania, Australia


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()) )
Go to the top of the page
 
KantWin
post Jun 25 2019, 07:25 PM
Post#3



Posts: 605
Joined: 1-August 02
From: Alabama


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

Works exactly as I hoped it would.

Thank you!

--------------------
KantWin
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd July 2019 - 11:44 PM