Get query result of every month between two dates

Question

create table link : https://drive.google.com/file/d/1EEqpW2Y8UkplfQcp_fw0j2byXAxBQXOW/view?usp=sharing

I have a query

SELECT 
    DATE_FORMAT(seized_date,'%Y-%m') as 'Seized Date',
    sum(case when seized_remarks = 'Temporary Seized' then 1 else 0 end) AS seized,
    sum(case when (DATE_FORMAT(release_date, '%Y-%m') BETWEEN '2021-01' AND '2021-07') then 1 else 0 end) AS released,
    sum(case when (DATE_FORMAT(stock_return_date, '%Y-%m') BETWEEN '2021-01' AND '2021-07') then 1 else 0 end) AS stock_return
FROM mahindra
where 
(DATE_FORMAT(seized_date, '%Y-%m') BETWEEN '2021-01' AND '2021-07')
GROUP BY DATE_FORMAT(seized_date,'%Y-%m')

which gives result as

Date    Seized  Release Stock Return
2021-01    1      0         0
2021-03    1      0         0
2021-04    1      0         0
2021-05    5      1         0
2021-06    6      0         1
2021-07    2      0         0

here i didn't get the result of february 2021. I want to get the result of all months between this two dates even if the seized_date does not exist

If the table does not contain any data for any year/month then you myust generate dynamically a table which cintains all year-month combinations for needed period and leftjoin your data table to it. — Akina, Jul 29 '21 at 09:52
As Akina said, you will need to create a calendar table, as shown in this link: https://stackoverflow.com/questions/14105018/generating-a-series-of-dates — Aleix CC, Jul 29 '21 at 09:57
*Link to my table(mahindra)* Convert your CSV to CREATE TABLE + INSERT INTO scripts. Remove unnesessary columns and data. Show desired output for this data. — Akina, Jul 29 '21 at 10:01
here is the link to create table https://drive.google.com/file/d/1EEqpW2Y8UkplfQcp_fw0j2byXAxBQXOW/view?usp=sharing — Tanvir Nabil, Jul 29 '21 at 10:10

score 0 · Answer 1 · answered Jul 29 '21 at 09:59

Looks like you need in something like

SELECT 
    dates.`Seized Date`,
    COALESCE(SUM(mahindra.seized_remarks = 'Temporary Seized'), 0) AS seized,
    COALESCE(COUNT(mahindra.release_date), 0) AS released,
    COALESCE(COUNT(mahindra.stock_return_date), 0) AS stock_return
FROM ( SELECT '2021-01' `Seized Date` UNION ALL
       SELECT '2021-02' UNION ALL
       SELECT '2021-03' UNION ALL
       SELECT '2021-04' UNION ALL
       SELECT '2021-05' UNION ALL
       SELECT '2021-06' UNION ALL
       SELECT '2021-07' ) dates
LEFT JOIN mahindra ON DATE_FORMAT(mahindra.seized_date, '%Y-%m') = dates.`Seized Date`
GROUP BY dates.`Seized Date`

`COUNT()` never returns `NULL`, so `COALESCE()` is unnecessary. — Gordon Linoff, Jul 29 '21 at 11:01
@GordonLinoff True. Simply I add COALESCE firstly, and replace SUM with COUNT() later... — Akina, Jul 29 '21 at 11:07

jimiclapton · Answer 2 · 2021-07-29T10:24:38.887

As @Akina says, if the seized_date value does not exist anywhere in your table, you cannot expect it to be present in your results at all. You need to create a column containing all required dates and then you can do something like perform a join onto that column.

Here's an example of how I might do it.

 SELECT  TO_CHAR(DATEADD('MONTH', -n, (CURRENT_DATE+1)),'YYYY-MM') AS seized_date
   FROM (
        SELECT ROW_NUMBER() OVER () AS n 
        FROM mahindra LIMIT 10
        ) 
   ORDER BY seized_date DESC

This is creates the following output.

Code explanation:

The inner query is based on a window function. The window function itself is simple. We're basically telling the computer to assign a value of 10 to n. To a human, "10" is a numeric value assigned to a specific thing or count of things (eg the temperature is 10 degrees, or I have 10 apples), but to the processor/computer, it doesn't mean much on its own. At least not in our scenario. So we simply tell the processor to count some rows ROW_NUMBER and when it finds 10 rows, that's what 10 looks like. You can use LIMIT to make this greater or fewer than 10 months.

In the outer query we just take today's date CURRENT_DATE and subtract n months from it as in (DATEADD('MONTH', -n, (CURRENT_DATE+1). In our case, n is 10 months.

Now you have a column of dates, formatted as you per your requirements YYYY-MM.

You can write the query such that you LEFT JOIN your precessed data set to these dates on their corresponding values.

The reason why this is a good way of doing things is that you don't have to manually enter any dates or use a UNION join. You let the window function do the work for you, meaning you can go back in time as far as you need/or want very easily by changing the LIMIT value. This allows for greater efficiency in the event where you need to go back over multiple years, for example.

@Tanvir Nabil Did either of these answers help you achieve your end goal? If so please select one, it helps the community. — jimiclapton, Aug 11 '21 at 15:55

Get query result of every month between two dates

2 Answers2