I am trying to get the month sales for a business where the time they are open is from 11am-2am the next day. I tried doing the following by grouping each day assuming that just for worse case they do sales until 3am. Assuming this is 16 hours of sales. I haven’t checked if the total is correct yet but I do notice that it only gives me back 29 days where there are 31 days in January. a_invoices is the master invoice for each and a_tabs is the details for each invoice.
Am I doing this correctly? I know basic MySql and this is a bit out of my pay grade. Thanks for any help.
SELECT DATE(a_invoices.startdate - INTERVAL 16 HOUR) as day , sum(a_tabs.Total)
FROM a_invoices
Right JOIN a_tabs on a_invoices.TabId = a_tabs.TabId
WHERE a_invoices.closedate BETWEEN '2022-01-01 11:00:00' and '2022-01-30 03:00:00'
AND a_invoices.status='c' and a_tabs.status<>'v'
GROUP BY day
>Solution :
You have written from the 1st to the 30th. Try from the 1st to the 1st. It will also be easier to update the query each month
SELECT DATE(a_invoices.startdate - INTERVAL 16 HOUR) as day , sum(a_tabs.Total)
FROM a_invoices
Right JOIN a_tabs on a_invoices.TabId = a_tabs.TabId
WHERE a_invoices.closedate BETWEEN '2022-01-01 11:00:00' and '2022-02-01 03:00:00'
AND a_invoices.status='c' and a_tabs.status<>'v'
GROUP BY day