How to reduce an query of query for each month

Question

I'm using My Sql 5.7 and CF11. I want to get the total qTotalPrice & quoteKeyID for each month from the below query. But not a full year. I want to get it from current month to last six month data ( From July to FEB). If there is no data for an particular month, I need to display as an 0 for the particular month.

<cfquery name="thisYearsQuotes" datasource="myDB">
    SELECT * , MONTH(qDateTime) as qMonth
    FROM Quotes
    INNER JOIN Accounts
    ON Quotes.aID = Accounts.aID
    WHERE Accounts.aID = <cfqueryparam value="#VNAI.aID#" cfsqltype="cf_sql_clob" maxlength="255">
    AND Quotes.qDateTime > DATE_ADD(NOW(), INTERVAL -365 DAY)
</cfquery>

Result of an these query

For getting each month records I've write a query of query like below.

For getting current month

<cfquery name="SalesTotalThisMonth" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(NOW())#
</cfquery>

For getting one month back

<cfquery name="SalesTotal1MonthAgo" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(DateAdd("m",-1,NOW()))#
</cfquery>

For getting two month back,

<cfquery name="SalesTotal2MonthAgo" dbtype="query">
    SELECT SUM(qTotalPrice) as DollarTotal , COUNT(quoteKeyID) as QuoteCount
    FROM thisYearsQuotes
    WHERE qMonth = #MONTH(DateAdd("m",-2,NOW()))#
</cfquery>

etc... Like wise I've to get last six month . So I've wrote a number of query of query for each month.

Is there any possibility to reduce the code?

Are you trying to return a *single* total for the last 12 months. Ex. $750 - or do you want the individual totals for *each* of the last 12 months. Ex. Jan 2018 - $200, Feb 2018 - Total $175, Mar - Total $375, etc... — SOS, Jul 31 '18 at 13:26
As an aside, the column data type probably isn't `cf_sql_clob` and typically it's a good practice to include both year and month to differentiate in case of overlapping time periods (though there won't be any here...). — SOS, Jul 31 '18 at 13:41
@Ageax. Yes. But not an full year. I want to get it from current month to last six month data ( From July to FEB). If there is no data for an particular month , I need to display as an 0 for the particular month. — Kannan.P, Jul 31 '18 at 14:09

rrk · Answer 1 · 2018-08-01T06:08:55.223

2

UPDATE

The solution will be to create a query for all the months you need and then use a left join.

SELECT 8 AS monthNum UNION SELECT 7 AS m UNION SELECT 6 AS m UNION SELECT 5 AS m UNION SELECT 4 AS m UNION SELECT 3 AS m

So to make it automated, I added some CF code to create the above query.

<cfset today=now()>
<cfset start = 0>
<cfset numberOfMonths = 6>
<cfset listOfMonths = ''>
<cfloop condition="numberOfMonths+start GT 0">
  <cfset listOfMonths = listappend(listOfMonths, ' SELECT ' & Month(DateAdd('m', start, today)) & ' AS m ')>
  <cfset start-->
</cfloop>
<cfoutput>
<cfquery>
  SELECT SUM(O.order_Total) AS totalOrder, COUNT(O.order_ID) AS numOrders, MONTH(O.order_Date) AS qMonth
  FROM tbl_orders O 
    RIGHT JOIN (
      #ListChangeDelims(listOfMonths, 'UNION')#
    ) monthList ON monthList.m = MONTH(O.order_Date) AND O.order_Date > DATE_ADD(NOW(), INTERVAL -6 MONTH)
  GROUP BY qMonth;
</cfquery>
<cfoutput>

INITIAL

I think you can do it in a single query with proper group by.

<cfquery name="thisYearsQuotesPerMonth" datasource="mySQL_MILESTONEBMDB">
    SELECT 
      SUM(qTotalPrice) AS DollarTotal, COUNT(quoteKeyID) AS QuoteCount, MONTH(qDateTime) as qMonth
    FROM 
      Quotes
      INNER JOIN Accounts ON Quotes.aID = Accounts.aID
    WHERE 
      Accounts.aID = <cfqueryparam value="#VNAI.aID#" cfsqltype="cf_sql_clob" maxlength="255">
      AND Quotes.qDateTime > DATE_ADD(NOW(), INTERVAL -6 MONTH)
    GROUP BY qMonth
</cfquery>

edited Aug 01 '18 at 06:08

answered Jul 31 '18 at 13:36

rrk

15,677
4
29
45

It's return those month having the values of one year. I want to get last six month from the current month. If the month having the data then it's should be calculate total as you mentioned above query.If the month does not having records then should display as 0 in query. – Kannan.P Jul 31 '18 at 14:11
The group by will return only which month having records in DB. But I want to return last six month whether it's having records or not. Hope you understand the my scenario. – Kannan.P Jul 31 '18 at 14:14
So that I've wrote as 1st sub query as WHERE qMonth = #MONTH(NOW())# 2nd sub query as WHERE qMonth = #MONTH(DateAdd("m",-2,NOW()))# 3rd sub query as WHERE qMonth = #MONTH(DateAdd("m",-3,NOW()))# etc.. up to WHERE qMonth = #MONTH(DateAdd("m",-5,NOW()))#. So I wish to improve my query as much as possible. Thank in advance . – Kannan.P Jul 31 '18 at 14:18
@Kannan.P I have updated my answer with some changes. Check that out. The query will return a row for each month even if there was no records for that month. – rrk Aug 01 '18 at 06:10
1

That works, though if this is for more than a single query, again I'd recommend a calendar table. It's a one time set up and is very useful for reporting. We use them in most of our databases. (Side joke - RRK - RIGHT JOIN? Ohhh, my eyes, my eyes! ;-) – SOS Aug 01 '18 at 14:07
@Ageax calendar table sounds like a good idea. Also right join :D LOL – rrk Aug 01 '18 at 14:16
I got the solution with right join but without using Calendar table. I've posted my an query here. Thank you for time @RRK. – Kannan.P Aug 03 '18 at 05:55

score 1 · Answer 2 · answered Aug 03 '18 at 05:53

I got the solution by adding an master table name as reportmonths. Which is have an data of 12 rows with last one year as Month & Year columns. So achieve it by Right joining that table with my query. So now I can get last six month records by using LIMIT 6.

SELECT RP.ReportYear AS `year`, RP.ReportMonth AS `month`, A.DollarTotal, A.QuoteCount
    FROM
        (   
            SELECT
                YEAR(qDateTime) AS qYear, MONTH(qDateTime) AS qMonth,
                SUM(qTotalPrice) AS DollarTotal , COUNT(quoteKeyID) AS QuoteCount
            FROM Accounts A
                INNER JOIN Quotes Q ON A.aID = Q.aID
            WHERE A.aID = 216
            GROUP BY YEAR(qDateTime), MONTH(qDateTime)
        ) A
    RIGHT JOIN ReportMonths RP ON  RP.ReportYear = qYear 
    AND  RP.ReportMonth =  qMonth 
    ORDER BY `year` DESC, `month` DESC LIMIT 6

Yep, that's a variation on the calendar table concept. The one issue with using only months is if there's no data for certain periods, the ordering of the results will be off because the `year` value will be null. For example, if the current month is March, the order may not be (10-Oct,11-Nov,12-Dec,01-Jan,02-Feb,03-Mar) Not sure if that matters in your case. — SOS, Aug 06 '18 at 15:13

How to reduce an query of query for each month

2 Answers2