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

MySQL how to get query with 17 month date interval which shows current month plus 5 months ahead

I’m quite new to SQL and i have issues with a query i find quite complex.

So i’m trying to create a query that shows a 17 month date interval.
In that interval it has to show the what the current month and current date is.
Then it has to compare that date with the months before current date.

I’ll then use the data i get in a Highcharts chart.

This is what my current query and table looks like.

SELECT
    MONTH(s_order_main.added_date)                  AS iMonth,
    s_order_main.channel                            AS strChannel,
    COUNT(DISTINCT s_order_styles.s_order_style_id) AS iOrderCount,
    SUM(DISTINCT s_order_styles.sales_price)        AS fTurnover
FROM
    s_order_main
INNER JOIN
    s_order_styles ON
        s_order_styles.s_order_main_id = s_order_main.id
WHERE
    s_order_main.channel
    && s_order_main.order_type = 'pre'
    && YEAR(s_order_main.added_date) = YEAR(CURDATE() - INTERVAL 17 MONTH)
GROUP BY
    MONTH(s_order_main.added_date)
;
iMonth strChannel iOrderCount fTurnover
1 normal 2234 33048.66
2 normal 6638 66711.96
3 normal 4266 30742.70
4 normal 171 766.10
5 normal 90 926.55
6 normal 1254 12334.04
7 normal 921 2990.35
8 normal 9469 46407.63
9 normal 5837 31623.17
10 normal 70 305.03
11 normal 323 2726.99
12 normal 370 6693.94

This is what i want my table to look like – Not sure if current month is supposed to look like that though.

iMonth strChannel iOrderCount fTurnover iCurrentMonth strCurrentDate
12 normal 2234 33048.66 0 2021-12-22 13:54:09
1 normal 2234 33048.66 0 2022-01-22 13:54:09
2 normal 6638 66711.96 0 2022-02-22 13:54:09
3 normal 4266 30742.70 0 2022-03-22 13:54:09
4 normal 171 766.10 0 2022-04-22 13:54:09
5 normal 90 926.55 0 2022-05-22 13:54:09
6 normal 1254 12334.04 0 2022-06-22 13:54:09
7 normal 921 2990.35 0 2022-07-22 13:54:09
8 normal 9469 46407.63 0 2022-08-22 13:54:09
9 normal 5837 31623.17 0 2022-09-22 13:54:09
10 normal 70 305.03 0 2022-10-22 13:54:09
11 normal 323 2726.99 1 2022-10-22 13:54:09
12 normal 370 6693.94 0 2022-12-22 13:54:09
1 b2b 370 6693.94 0 2023-01-22 13:54:09
2 normal 370 6693.94 0 2023-02-22 13:54:09
3 b2b 370 6693.94 0 2023-03-22 13:54:09
4 normal 370 6693.94 0 2023-04-22 13:54:09

Hope it makes sense, but if i need to elaborate something then please ask 🙂

Tags:

2

Answers


  1. 0

    My guess is you want some date arithmetic in your WHERE, kind of

     ..
   && s_order_main.added_date >= DATE_ADD(last_day(DATE_ADD(curdate(), interval -12 month)), interval 1 day)
   && s_order_main.added_date < DATE_ADD(last_day(DATE_ADD(curdate(), interval 5 month)), interval 1 day)
 ..

    Demo

    Login or Signup to reply.
  2. 0

    So if the current date/time is 2022-10-31 13:00:00 what should the end date/time for Feb 2022 grouping be? Obviously, it cannot be 2022-02-31 13:00:00. Should it be 2022-02-28 23:59:59?

    Here is my attempt at your query. If you have a reliable (and/or dedicated) sequence table then you can get rid of the seq derived table.

    SELECT
    MONTH(cal.monthStart)                           AS iMonth,
    s_order_main.channel                            AS strChannel,
    COUNT(DISTINCT s_order_styles.s_order_style_id) AS iOrderCount,
    SUM(DISTINCT s_order_styles.sales_price)        AS fTurnover,
    IF(DATE(cal.monthEnd) = CURRENT_DATE, 1, 0)     AS iCurrentMonth,
    cal.monthEnd                                    AS strCurrentDate
FROM (
    SELECT
        seq.n period,
        (CURRENT_DATE - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL (11 - seq.n) MONTH) monthStart,
        IF(
            DAY(CURRENT_DATE) > DAY((CURRENT_DATE - INTERVAL (11 - seq.n) MONTH)),
            LAST_DAY(CURRENT_DATE - INTERVAL (11 - seq.n) MONTH) + INTERVAL 1 DAY - INTERVAL 1 SECOND,
            (NOW() - INTERVAL (11 - seq.n) MONTH)
        ) monthEnd
    FROM (
        SELECT  0 n UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6 UNION ALL SELECT  7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10   UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
    ) seq
) cal
LEFT JOIN s_order_main
    ON s_order_main.added_date BETWEEN cal.monthStart AND cal.monthEnd
    AND s_order_main.order_type = 'pre'
LEFT JOIN s_order_styles
    ON s_order_styles.s_order_main_id = s_order_main.id
GROUP BY cal.period, strChannel;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search