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)
;

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

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

CodePudding user response：

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

CodePudding user response：

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;