Combine 2 separate database queries in one foreach loop

I have this query to check the last 3 months sales figures and another query to check the refund figures

       
           // sales
            SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window


           // refunds
        SELECT DATE_FORMAT(credit_date, '%b') as month, SUM(`credit_value`) as totalCredit FROM
            (
               SELECT credit_value, credit_date
               FROM credit
               WHERE credit_date <= NOW()
               and credit_date >= Date_add(Now(),interval - 3 month)
               AND `reason` = 'Refund'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Now() AS credit_date, 0 as credit_value
            ) as test
            GROUP BY DATE_FORMAT(credit_date, '%m-%Y')
            ORDER BY DATE_FORMAT(credit_date, '%m-%Y') desc
            ");

Open in new window


I want to show the data in a table like:

Month          Sales          Refunds

Sep                100                 0
Aug                100                20
Jul                   50                   5

I thought of trying to combine the queries into one but that seems very complex. And trying to nest the foreach loops creates 6 rows instead of 3. What would be the best way to achieve this?
LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
To minimize the database roundtrip you can simply union both. Just add a discriminator (source) constant column.

Just a comment:

You're shifting too much work to the database level. You're seeking for a display format. Thus I would run both queries separately. Without the inner unions for the 0 rows, cause these 0 rows/cells can be easily created in your front-end.
0
Black SulfurAuthor Commented:
Thanks ste5an,

I am trying to do what you suggested

SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales, SUM(credit_value) as totalCredit FROM
(
   SELECT order_date, order_total
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'

   UNION ALL
   SELECT credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


But am getting an error:

Column not found: 1054 Unknown column 'credit_value' in 'field list' in

I could be wrong but I think the issue is coming from here:

SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales, SUM(credit_value) as totalCredit FROM

Open in new window


I am also only selecting order_date but I also actually need credit_date
0
Kent OlsenDBACommented:
Hi Black,

The UNION ALL operator takes the rows in the lower sub-query and appends them to the rows in the upper sub-query.  The row names come form the upper sub-query so all of the data is there, but you have to reference it by the correct name.

A ste5an noted, a comment column that identifies the row's source is probably needed.  Since your filter has an order_status or reason identifier, that would do nicely.

Try something like this:

SELECT DATE_FORMAT(order_date, '%b') as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
FROM
(
   SELECT order_status, order_date, order_total
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'

   UNION ALL
   SELECT reason, credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


Good Luck,
Kent
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Black SulfurAuthor Commented:
Thank you Kent, that seems to be working nicely. However, I am still missing the month with zero data. It should just show the month and a bunch of $0.00 values. ste5an pointed out that it would be easy to fix this in the frontend instead of trying to do it with the database as I was trying to do. But I am not really sure how that would be achieved. I could try wrap two sets of <tr> in if/else statements but that is maybe cumbersome?

Here is my foreach loop. Please note that the empty <tr> is where I plan to put more data.

<?php foreach($data['3months'] as $sales): ?>
<tr>
    <th scope="row">
        <?php echo $sales->month; ?>
    </th>
    <td>

    </td>
    <td>

    </td>
    <td>

    </td>
    <td>
         <?php echo $sales->totalCredit; ?>
    </td>
    <td>
         <?php echo $sales->totalSales; ?>
    </td>
</tr>
<?php endforeach; ?>

Open in new window

0
Kent OlsenDBACommented:
It appears to me that the report won't necessarily produce the results that you want.  By by selecting data generated withing the last 3 months, the starting point of the data is relative to the current date, not the first of the month.  The June data in the report, if run today, would include the data between June 12 and June 30.  Is that the intent, or do you want the entire month's data?
0
Black SulfurAuthor Commented:
I want the sales data for each month from the first of the month until last day of the month for previous months but for current month should just be from 1st of the month until current date.
0
Kent OlsenDBACommented:
I see that you had a similar question yesterday.  Did you not get a satisfactory answer?
0
Black SulfurAuthor Commented:
The question yesterday was just for the sales. Today I need sales and refunds.
0
Kent OlsenDBACommented:
I don't have a MySQL environment available to me right now so I can't debug this, but it should be pretty close...

WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (StartDate)
AS (
  SELECT StartDate Month
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month)
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months)
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months)
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.Month = t1.Month
  

Open in new window

0
Black SulfurAuthor Commented:
Thanks, Kent. That gives me an error:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BaseDate (StartDate)
AS (
  SELECT Date_sub
0
Kent OlsenDBACommented:
It looks like 'month' cant be used as a column name without quoting it.  So we'll change it!  :)

WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (MonthName)
AS (
  SELECT StartDate MonthName
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month)
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months)
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months)
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as MonthName, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.MonthName = t1.MonthName
  

Open in new window

0
Black SulfurAuthor Commented:
Thanks again Kent, still getting errors:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interva
0
Kent OlsenDBACommented:
WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (MonthName)
AS (
  SELECT StartDate MonthName FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month) FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months) FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months) FROM BaseDate
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as MonthName, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.MonthName = t1.MonthName

Open in new window


This would be so much easier if I had MySQL available to me here...  Apologies....
0
Black SulfurAuthor Commented:
Hi Kent, sorry for delayed response. Sorry for just posting errors but it is hard for me to troubleshoot as I am unfamiliar with some of the sql you are using like BaseDate and MonthRanges for example.

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interva
0
Kent OlsenDBACommented:
Hi Black,

One of the issues with MySQL that I keep forgetting is it's intolerance for spaces between names and left parens.  FN (x) and FN(x) don't always parse equally.

But since we're spinning our wheels here, let's try this from another direction.

SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT (Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub (order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub (order_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

0
Black SulfurAuthor Commented:
If this is getting too frustrating for you, I won't hold it against you if you abandon the question. But in case you want to go on,I fixed the missing 'n' in 'then' on line 3 of your code, but am still getting an error:


Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), '%m-%Y')
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 2 month'
0
Kent OlsenDBACommented:
I don't give up nearly that easily....  :)

The parser is some of the issue as the string you posted doesn't exist in the query.  

SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

0
Black SulfurAuthor Commented:
That's the spirit! Another error for you :P

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar'

So, this works for only orders:

            SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window


And this works for orders and refunds but doesn't show the months with zero:

SELECT DATE_FORMAT(order_date, '%b') as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
   SELECT order_status, order_date, order_total
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'

   UNION ALL
   SELECT reason, credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


They just need to be combined somehow...
0
Kent OlsenDBACommented:
Ahha!  Found a really glaring error select from the CREDIT table.

The two queries are fundamentally the same.  The new on just adds logic to create all of the periods and join the results to them.


SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

0
Black SulfurAuthor Commented:
:)

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar

Why do you have interval 1 day in there?
0
Kent OlsenDBACommented:
The query calls last_day() on the current date (now) and adds 1 day to it (interval 1 day) to get the first day of the following month.  Back up 4 months (interval 4 days) and that's the first date that the query will report.

I've had a heck of a time getting MySQL to install on my new desktop.  It requires a version of .NET that is not necessarily compatible with other tools.  :(

This shouldn't be this tough, even without a test environment!


SELECT t0.ReportPeriod
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

0
Black SulfurAuthor Commented:
Sorry for all the trouble. I appreciate your persistence!

New error:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar'
0
Kent OlsenDBACommented:
Let's break the query down into smaller pieces and see what happens.  :)

First the upper query.

  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 

Open in new window


Then the lower query:

  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window

0
Black SulfurAuthor Commented:
Hi Kent, sorry for the delayed response. Running the first block of code throws no errors. Running the second block however gives me:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar
0
Kent OlsenDBACommented:
Let's split up the offending SQL, too.  Try each of these.

  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'

Open in new window


  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window


  SELECT Date_sub(order_date, '%m-%Y') AS ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'

Open in new window

  SELECT Date_sub(credit_date, '%m-%Y') As ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window

0
Black SulfurAuthor Commented:
Hi there,

Okay, they all failed. Here is the error for the first one

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar' at line 1

and the last one:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%m-%Y') As ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHER' at line 1
0
Kent OlsenDBACommented:
Thanks!  The fact that none of them worked told me a lot.  Basically, the call to Date_sub at the start of each query should have been a call to Date_format!  It just kind of disappeared into the shadows as I looked at the code until that clue tied it all together.  Another issue is that the interval in MYSQL date functions is singular.  'interval 4 months' is invalid, but 'interval 4 month' is accepted.

Try this:

SELECT t0.ReportPeriod,
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 4 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_format(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 month)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_format(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 month)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod
ORDER BY ReportPeriod

Open in new window

0
Kent OlsenDBACommented:
Hi Black,

Testing SQL through PHP can be slow and laborious.  I wasn't able to modify either of my environments to install MySQL, but I found something to help me test and thought that I'd pass it along.

  http://sqlfiddle.com/

It creates a local session unique to you that allows you to build and test your SQL.  The left window is the semi-static DDL (create table, insert data, etc.) and the right window is your SQL.  You might want to give it a try.  It looks like it could make your SQL development easier than writing it into your PHP.  One caution is that I have no idea who runs the site or what happens to your declarations so don't enter anything that is sensitive or identifying.

To test the SQL, I entered this as the DDL (left window):

CREATE TABLE credit (credit_date date, reason varchar(20), credit_value float);
CREATE TABLE order_summary (order_date date, order_status varchar(20), order_total float);

INSERT INTO credit (credit_date, reason, credit_value) 
values ('2018-07-07', 'Refund', 123.45), ('2018-08-08', 'Refund', 567.89);                                                               

INSERT INTO order_summary (order_date, order_status, order_total) 
values ('2018-07-12', 'Approved', 123.45), ('2018-09-01', 'Approved', 567.89);                                                               

Open in new window


And this into the right:

SELECT t0.ReportPeriod,
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 4 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_format(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 month)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_format(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 month)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod
ORDER BY ReportPeriod

Open in new window


Good Luck!
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
Hi Kent, apologies for the delayed response. I have been rather busy!  Will test this all out ASAP and get back to you. Thanks again.
0
Black SulfurAuthor Commented:
Brilliant, that works! Thanks so much for all your effort and patience :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.