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.

 

[Chriss Miller]

Zazzi,

Thanks for your reply.

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

he final query is the actual working horse. But to test it zazzi had to have some sample data, right?
Is it really that hard to understand demonstration code?

I mean, besides the fact that only once you would specify your database design, table and column names, data types, etc. you could get an answer you don't have to adapt to your situation.

 

[Chriss Miller]

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?

Your choice, actually. It's good practice to have meta data, a table of 12 months could be counted as that. It's also good practice to actually generate what's called tally tables, because you can use them in many occasions and not only about 12 months. And even just for this case, would it be feasible to have results in quarters? Or for multiple years, so with 24,36,48,.. records including a year column? All just diversions from a simple 12 month table would make an actual table less valuable than generating it on the fly for the use case at hand.

 

[wOOdy-Soft]

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

As a saver alternative for that:

Function LastDayOfMonth (dDate)
Return GOMONTH( DATE(YEAR(dDate), MONTH(dDate), 1), 1) - 1

The trick is to go to the first day of the following month and then 1 day back.

 

[Chriss Miller]

Based on the input of a date, yes.
But generating all last days of month of a year GoMonth from last of January is safe, also in leap years.

 

[mjcmkrsr]

Hi,

... and my contribution

PUBLIC goFormSales

goFormSales=NEWOBJECT("clsForm")
goFormSales.Show

Read Events

Close all
Clear All

RETURN


**************************************************
DEFINE CLASS clsForm AS form
    AutoCenter = .T.
    Caption = "Grid with calculated columns"
    Width = 660
    Height = 420
    MinHeight = This.Height
    MinWidth = This.Width
    MaxWidth = This.Width
 
    ADD OBJECT grdSales AS grid WITH ;
        Left = 10, ;
        Top = 36, ;
        Width = 300, ;
        Height = ThisForm.Height - 42, ;
        TabIndex = 4, ;
        ColumnCount = -1, ;
        RecordSource = "csrSalesData", ;
        Anchor = 15, ;
        ReadOnly =.T.
 
        PROCEDURE grdSales.Init
             WITH This.Column1
                .Header1.Caption = "ID"
             ENDWITH

             WITH This.Column2
                .Header1.Caption = "Date"
             ENDWITH

             WITH This.Column3
                .Header1.Caption = "Amount"
             ENDWITH
         ENDPROC
        
    ADD OBJECT grdMonthlySales AS grid WITH ;
        Left = 324, ;
        Top = 36, ;
        Width = 318, ;
        Height = 240, ;
        TabIndex = 5, ;
        DeleteMark = .F., ;
        RecordMark = .F., ;
        ScrollBars = 0, ;
        ReadOnly =.T., ;
        Visible = .F.
 
    ADD OBJECT lblDate as Label WITH ;
        Left = 12, Top = 9, Caption = "Enter date :", AutoSize = .T.

    ADD OBJECT txtDate as TextBox WITH ;
        Left = 98, Top = 6, Width = 84, TabIndex = 1, Value = DATE()

    ADD OBJECT lblSalesMonth as Label WITH ;
        Left = 324, Top = 282, Caption = "Sales", Autosize = .T., FontBold = .T., Visible = .F.

     ADD OBJECT cmdUndo AS CommandButton WITH ;
        Left = 430, Top = 6, Height = 24, TabIndex = 3, Caption = "O-Data"
    
        PROCEDURE cmdUndo.Click()
            With ThisForm.grdSales
                .Visible = .T.
                .ColumnCount = -1
                .Recordsource = "csrSalesData"
                
                .Column1.Header1.Caption = "ID"

                .Column2.Header1.Caption = "Date"

                .Column3.Header1.Caption = "Amount"
            ENDWITH

            WITH ThisForm
                .lblSalesMonth.Caption = ""
                .grdMonthlySales.Visible = .F.
                .Refresh()   
            ENDWITH
        ENDPROC

    ADD OBJECT cmdDoit AS CommandButton WITH ;
        Left = 324, Top = 6, Height = 24, TabIndex = 2, Caption = "Calculate"
    
        PROCEDURE cmdDoit.Click()
            LOCAL ARRAY laYearlyAmount[1]
            
            Select iRefMonth, cRefMonth, NVL(yMonthlyAmount, 0) ;
                FROM csrMonths ;
                LEFT JOIN ;
                    (SELECT MONTH(dSalesDate) as iMonth, SUM(ySalesAmount) as yMonthlyAmount ;
                        FROM csrSalesData ;
                        WHERE YEAR(dSalesDate) = YEAR(ThisForm.txtDate.Value) ;
                        GROUP BY 1 ) csrSumValues ;
                    ON iRefMonth = iMonth ;
                ORDER BY 1 ;   
                INTO CURSOR csrTemp               
                
            SELECT SUM(ySalesAmount) ;
                    FROM csrSalesData ;
                    WHERE YEAR(dSalesDate) = YEAR(ThisForm.txtDate.Value) ;
                    INTO ARRAY laYearlyAmount
            
            LOCATE
            
            WITH ThisForm.grdMonthlySales
                    .ColumnCount = -1
                    .RecordSource = "csrTemp"
                    .SetAll("DynamicBackColor", "ICASE(MOD(iRefMonth, 2) = 1, RGB(0,255,255), RGB(0, 255, 0))", "Column")
                    .Visible = .T.
                                    
                    .Column1.Header1.Caption = "Month"
                    
                    .Column2.Header1.Caption = "Month"
                    
                    .Column3.Sparse = .F.
                    .Column3.Header1.Caption = "Monthly Sales"
                    .Column3.Text1.Inputmask = "999,999,999.9999"
             ENDWITH
                
                 WITH ThisForm.lblSalesMonth
                     .Caption = "Sales in " + ALLTRIM(STR(YEAR(ThisForm.txtDate.Value))) + " : " + TRANSFORM(NVL(laYearlyAmount, 0), "999,999,999.9999")
                     .Visible = .T.
                 ENDWITH
        ENDPROC

PROCEDURE Destroy
    Thisform.Release()
    CLOSE ALL
    Clear Events
ENDPROC

PROCEDURE Load
    LOCAL lnI
    
    CREATE CURSOR csrMonths (iRefMonth I, cRefMonth C(10))
        FOR lnI = 1 TO 12
            INSERT INTO csrMonths VALUES (lni, CMONTH(DATE(2000, lni, 1)))
        ENDFOR
    
    CREATE CURSOR csrSalesData (Id I AUTOINC, ;
       dSalesDate D DEFAULT DATE(YEAR(DATE()), 1, 1) + RAND() * 3600, ;
       ySalesAmount Y DEFAULT Rand() * $500.00)
  
        For lnI = 1 To 7200
           Append Blank
        EndFor
    
    LOCATE
    
ENDPROC

ENDDEFINE
*********************************************

hth

MarK