Mysql Question posted in
The official MySQL documentation can be found here.

Mysql query grouping AM and PM shift for days spanning 2 days

I have a business that is open from 11am to 2am and I am trying to show a monthly sales totals. I already get the daily sales with no problem but I am trying to do a monthly sales with a AM and PM break down. My am shift goes until 7pm and PM from 7:01-2am. I put 3am because they finish their paperwork late so i add in a buffer time just in case. My problem is that if I take me monthly report query and group by AM/PM it returns back 3 results for one day since I span 2 days. How do I have my query properly show the sales for each day from 11am-2am. THe status=’c’ is for invoices that are closed.
enter image description here

SELECT 
    DATE_FORMAT(a_invoices.closedate, '%m/%d')  AS sdate,
    CASE 
        WHEN HOUR(a_invoices.closedate) >= 8 AND HOUR(a_invoices.closedate) <= 19 THEN 'AM' 
        ELSE 'PM' 
    END as Shift,
    SUM(total) FROM a_invoices 
WHERE 
    closedate between "2024-01-05 09:00:00" and "2024-01-06 03:00:00"
    and status='c'
GROUP BY sdate, Shift
Tags:

2

Answers


  1. 0

    If you want to treat sales after midnight as the previous day, subtract 1 from the date when the hour is before 7am.

    SELECT
    DATE_FORMAT(IF(HOUR(a_invoices.closedate) < 7,
                   DATE_SUB(a_invoices.closedate, INTERVAL 1 DAY),
                   a_invoices.closedate), '%m/%d') AS sdate,
    Login or Signup to reply.
  2. 0

    I understand your situation, the problem is with a closedate spans across two days, and when you group by sdate (which is the date part of closedate).

    One way that I think is best to solve your problem is to adjust the closedate to the previous day if the house is greater than 19 (7 pm). By doing this the sales between 7:01 pm and 2 AM will be attributed to the previous day.

    Here is your query that I modified.

    SELECT 
    DATE_FORMAT(adj_closedate, '%m/%d') AS sdate,
    CASE 
        WHEN HOUR(a_invoices.closedate) >= 8 AND HOUR(a_invoices.closedate) <= 19 THEN 'AM' 
        ELSE 'PM' 
    END as Shift,
    SUM(total) 
FROM (
    SELECT 
        IF(HOUR(closedate) > 19, closedate - INTERVAL 1 DAY, closedate) AS adj_closedate,
        total,
        status
    FROM a_invoices
) AS subquery
WHERE 
    adj_closedate between "2024-01-05" and "2024-01-06"
    and status='c'
GROUP BY sdate, Shift;

    Changes I performed:

    1. I added a subquery that calculates the adjusted closedate using and IF statement.
    2. I replaced the original closedate with the adj_closedate in the SELECT and WHERE clauses.
    3. I removed the time component from the WHERE clause, as we are now working with the adjusted date.

    I hope this will help you to get the result you looking for.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search