Daily month sales separate AM and PM-CodePudding

I have my php mysql currently that get the entire months sales and groups it by days. I am now trying to take that further and separate am vs pm sales. The AM shift is 10am-7pm and PM shift is 7pm-2am. I Know I can group by day then by hour and iterate through and get the am that way but I am sure their is a better way directly in sql. Thanks for any insight.

SELECT DATE(a_tabs.strDate - INTERVAL 16 HOUR) as day , 
       DATE_FORMAT(a_tabs.strDate, '%h') AS hour , 
       sum(a_invoices.Total) as total 
FROM a_tabs 
Right JOIN a_invoices on a_tabs.TabId = a_invoices.TabId 
WHERE a_tabs.strDate BETWEEN '2022-03-01 09:00:00' and '2022-03-31 18:00:00' 
AND a_invoices.status='c' 
and a_tabs.status<>'v' 
GROUP BY day , hour

result from this query

CodePudding user response：

Given both your group by clauses are related to the time. Start by shifting the date so that AM is truly AM and PM is truly PM.

a_tabs.strDate - INTERVAL 7 HOUR

(7 chosen as 7pm end of AM shift).

Use UNIX_TIMESTAMP to get this down to a second value (hours would be better but there isn't a function for that). And then div by a 12 hr interval.

SELECT ...
GROUP BY UNIX_TIMESTAMP(a_tabs.strDate - INTERVAL 7 HOUR) DIV (60*60*12)

CodePudding user response：

So in a given day you have 3 periods:

02:00 to 10:00 [8h, no shift]
10:00 to 19:00 [9h, AM shift]
19:00 to 02:00 [7h, PM shift]

But the trouble with a naieve solution is that the PM shift crosses over the date boundary.

Assuming a simplified table like:

CREATE TABLE sales (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rep_id INTEGER UNSIGNED,
  dt DATETIME,
  amount INTEGER
);

We can correctly align the shifts with the date boundary with dt - INTERVAL 2 HOUR and then something like:

SELECT
  DATE(dt - INTERVAL 2 HOUR) 'day',
  IF(   HOUR(dt - INTERVAL 2 HOUR) BETWEEN  0 AND  7, 'UN', 
    IF( HOUR(dt - INTERVAL 2 HOUR) BETWEEN  8 AND 16, 'AM', 'PM' )
  ) AS 'shift',
  SUM(amount) AS 'sales'
FROM sales
GROUP BY day, shift;

Sample data omitted for brevity, see on sqlfiddle: http://sqlfiddle.com/#!9/d4fbcb/22/0, but for a sale every 15 minutes on the dot, beginning at 2022-01-01 00:00:00:

|        day | shift | sales |
|------------|-------|-------|
| 2021-12-31 |    PM |     8 |
| 2022-01-01 |    AM |    36 |
| 2022-01-01 |    PM |    28 |
| 2022-01-01 |    UN |    32 |
| 2022-01-02 |    AM |    36 |
| 2022-01-02 |    PM |    28 |
| 2022-01-02 |    UN |    32 |

You can see that it correctly assigns the sales between 00:00 and 02:00 to the previous day's PM shift, and sales outside the defined shifts as "UN" for undefined.

However, with regard to maintainability, extensibility, and performance: I would not really recommend this approach of calculating the shift duringreport generation at all.

Maintainability: At some point in the future the shift boundary changes, now this query is returning incorrect shift data going forward. The naieve fix is to just change the hours in the query, but now it returns incorrect results for past data.
Extensibilty: At some point in the future a "cover" shift is added for 4PM to 10PM to account for demand. It is not possible to compute using a query like this anymore.
Performance: All of those dt - INTERVAL 2 HOUR and IF() statements add overhead and make it difficult or impossible to use indexes depending on what the requirements off your query are.

What I would suggest is making the "shift" into metadata that is associated with the sale record and calculated at insert time. Depending on your particular requirements, it might just be a string in the sale record, eg: 20220101_AM, or a foreign key relation into more robust schema.