SQL Command to Show Monthly Sales Including Months with Zero Sales

[dylim]

Hi Guys,

I guess the thread title is pretty self explanatory. I need to show sales summary of a customer by monthly totals, including months when there are zero sales.

Like so:

January - P34,000
February - P44,500
March - P0.00
April - P123,500
May - P0.00
...... etc....

Thanks in advance for your time and attention.

Love Live the Fox!

 

[mJindrova]

Because I have not a crystal ball and I do not know your table's structure then sql command can be:

SELECT Customer.Code, Customer.Name, Month.Name S MonthName, NVL(SUM(Entry.Amount), 0) AS Amount;
FROM Customer INNER JOIN Month ON 0=0; && month is cursor with 12 records (for each month)
LEFT JOIN Entry ON Customer.Code=Entry.CustomerCode AND Entry.Date BETWEEN Month.DateFrom AND Month.DateTo

 

[zazzi]

As an example

CREATE CURSOR months (ordering i, month c(15))
FOR nM = 1 TO 12
dDate = CTOD('10/' + IIF(nM < 10, '0' + ALLTRIM(STR(nM)), ALLTRIM(STR(nM))) + '/2025')
INSERT INTO months (ordering, month) VALUES (nM, CMONTH(dDate))
ENDFOR

CREATE CURSOR sales_per_month (month c(15), sales i)
INSERT INTO sales_per_month (month, sales) VALUES ('January', 200)
INSERT INTO sales_per_month (month, sales) VALUES ('February', 500)
INSERT INTO sales_per_month (month, sales) VALUES ('April', 1000)

SELECT m.month, NVL(s.sales, 0) as sales FROM months m LEFT JOIN sales_per_month s ON m.month = s.month ORDER BY m.ordering

 

[Chriss Miller]

zazzi, that fails when the date format is american (for example, you generate 12 "October" months.

Easy way to fix that aspect:

dDate = Date(2000,1,1) && any January date
CREATE CURSOR months (ordering i, month c(15))
FOR nM = 1 TO 12
   INSERT INTO months (ordering, month) VALUES (nM, CMONTH(dDate))
   dDate = Gomonth(dDate,1)
ENDFOR
*... rest of zazzi's code

btw, CMONTH() month names will be in the language you pick by resource DLL like VFP9resn.dll, so adjust the field width to fit names. You could of course also just generate records with the numeric months 1-12 or populate this from ALINES(laMonths,"Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec",[,]) or whatever.

But in short, overall, as you expect months without sale, well, you have to generate months yourself, and so the easiest is to generate all in advance, then turn NULL to 0 with the NVL() function. Those are the main ingredients.

 

[zazzi]

zazzi, that fails when the date format is american (for example, you generate 12 "October" months.

Easy way to fix that aspect:

dDate = Date(2000,1,1) && any January date
CREATE CURSOR months (ordering i, month c(15))
FOR nM = 1 TO 12
   INSERT INTO months (ordering, month) VALUES (nM, CMONTH(dDate))
   dDate = Gomonth(dDate,1)
ENDFOR
*... rest of zazzi's code

Excellent Chriss (as usual)

I forgot to mention I was suggesting my code based on British date but I learned the GOMONTH() function which I did not know... Thanks!

 

[Chriss Miller]

Fine, zazzi,

GOMONTH() has some specialties you should read about in the help.
You can determine the last days of month based on 31st January like this:

For nM=0 to 11
   ? Gomonth(Date(2025,1,31),nM)
Endfor

You can also get there from all month start dates-1, of course. But watch out:

? Gomonth(Date(2025,1,31),2), GoMonth(GoMonth(Date(2025,1,31),1),1)

When you use Gomonth(x,1) twice starting from last January you get to the 28th of March, not the last March day. Because the intermediate result 28th of February is the last February, but that does still only translate to the 28th of the next month with further GoMonth() calls.

So, dylim, if you would like to have last day of months in the months tables that's how you get there, too, by going 0-11 months from the last day of January.

 

[dylim]

Because I have not a crystal ball and I do not know your table's structure then sql command can be:

SELECT Customer.Code, Customer.Name, Month.Name S MonthName, NVL(SUM(Entry.Amount), 0) AS Amount;
FROM Customer INNER JOIN Month ON 0=0; && month is cursor with 12 records (for each month)
LEFT JOIN Entry ON Customer.Code=Entry.CustomerCode AND Entry.Date BETWEEN Month.DateFrom AND Month.DateTo

In MySQL or MariaDB, in lieu of NVL(), I can use IFNULL(), right?

Is it a good idea to just keep a "static table" of the 12 Months in my production database?

Thanks.

 

[dylim]

As an example

CREATE CURSOR months (ordering i, month c(15))
FOR nM = 1 TO 12
dDate = CTOD('10/' + IIF(nM < 10, '0' + ALLTRIM(STR(nM)), ALLTRIM(STR(nM))) + '/2025')
INSERT INTO months (ordering, month) VALUES (nM, CMONTH(dDate))
ENDFOR

CREATE CURSOR sales_per_month (month c(15), sales i)
INSERT INTO sales_per_month (month, sales) VALUES ('January', 200)
INSERT INTO sales_per_month (month, sales) VALUES ('February', 500)
INSERT INTO sales_per_month (month, sales) VALUES ('April', 1000)

SELECT m.month, NVL(s.sales, 0) as sales FROM months m LEFT JOIN sales_per_month s ON m.month = s.month ORDER BY m.ordering

Zazzi,

Thanks for your reply.

Pardon my ignorance, but the code is about inserting the actual sales totals per month?

 

[dylim]

Fine, zazzi,

GOMONTH() has some specialties you should read about in the help.
You can determine the last days of month based on 31st January like this:

For nM=0 to 11
   ? Gomonth(Date(2025,1,31),nM)
Endfor

You can also get there from all month start dates-1, of course. But watch out:

? Gomonth(Date(2025,1,31),2), GoMonth(GoMonth(Date(2025,1,31),1),1)

When you use Gomonth(x,1) twice starting from last January you get to the 28th of March, not the last March day. Because the intermediate result 28th of February is the last February, but that does still only translate to the 28th of the next month with further GoMonth() calls.

So, dylim, if you would like to have last day of months in the months tables that's how you get there, too, by going 0-11 months from the last day of January.

Noted on this sir.

Do you think it is a good idea to keep a table of MONTHS in the database to forego generating it each and everytime?

 

[mJindrova]

In MySQL or MariaDB, in lieu of NVL(), I can use IFNULL(), right?
Is it a good idea to just keep a "static table" of the 12 Months in my production database?
Thanks.

As I told... I have not a crystal ball

Yes, IFNULL() for MySQL/MariaDB is right function.

Ad Moth table - check if your MySQL/MariaDB version knows Recursive CTE.