How to find the missing month in a column in mysql?

Question

This is my view_remit. I need to determine the months missing in the column month_paid.

I used this code to get this table but I don't know how to determine the missing months.

SELECT RemitNo, PEN, Employer, month_paid, Total_PBR_Amt
FROM view_remit
JOIN (    
        SELECT 'JAN' as month_paid,'2016-01-01' as start,'2016-01-31' as end UNION ALL
        SELECT 'FEB' as month_paid,'2016-02-01' as start,'2016-02-29' as end UNION ALL
        SELECT 'MAR' as month_paid,'2016-03-01' as start,'2016-03-31' as end UNION ALL
        SELECT 'APR' as month_paid,'2016-04-01' as start,'2016-04-30' as end UNION ALL
        SELECT 'MAY' as month_paid,'2016-05-01' as start,'2016-05-31' as end UNION ALL
    SELECT 'JUN' as month_paid,'2016-06-01' as start,'2016-06-30' as end UNION ALL
    SELECT 'JUL' as month_paid,'2016-07-01' as start,'2016-07-31' as end UNION ALL
    SELECT 'AUG' as month_paid,'2016-08-01' as start,'2016-08-31' as end UNION ALL
        SELECT 'SEP' as month_paid,'2016-09-01' as start,'2016-09-30' as end UNION ALL
        SELECT 'OCT' as month_paid,'2016-10-01' as start,'2016-10-31' as end UNION ALL
        SELECT 'NOV' as month_paid,'2016-11-01' as start,'2016-11-30' as end UNION ALL
        SELECT 'DEC' as month_paid,'2016-12-01' as start,'2016-12-31' as end 
    ) M
    ON view_remit.AP_From <= M.end 
   AND view_remit.AP_To >= M.start
ORDER BY PEN, AP_From

I need to get this output.

|PEN|Employer|month|
| 1 |   a    | MAR |
| 1 |   a    | JUN |
| 1 |   a    | JUL |
| 1 |   a    | SEP |
| 1 |   a    | OCT |
| 1 |   a    | NOV |

Any help is greatly appreciated. Thank you in advance.

check this link may be you will get idea http://stackoverflow.com/questions/27600863/mysql-monthly-sale-of-last-12-months-including-months-with-no-sale — krishn Patel, Jan 24 '17 at 10:42
Problems like this are generally best handled in application level code. — Strawberry, Jan 24 '17 at 11:04

score 1 · Answer 1 · answered Jan 24 '17 at 11:02

I still cannot comment due to my reputation level. I will correct this answer as soon as you provide the answer to my questions:

Can you place a more complete sample of your table: column AP_From and AP_To are missing.
I don't understand how you link columns Pen and employer to the missing months. Anyhow to get the missing month based on those available on your table I would proceed as follow:



    -- Just for sample query purpose
    IF OBJECT_ID('tempdb..#view_remit') is not null

    DROP TABLE #view_remit
    CREATE TABLE #view_remit (
        RemitNo             INT,
        PEN                 INT,
        Employer            VARCHAR(10),
        wmonth              VARCHAR(3),
        Total_PBR_Amt       FLOAT,
        AP_Date             DATE
    )

    INSERT INTO #view_remit VALUES
    (1,1,'a','JAN',200, '2016-01-20'),
    (1,1,'a','FEV',200, '2016-02-12'),
    (1,1,'a','APR',200, '2016-04-25'),
    (1,1,'a','AUG',200, '2016-08-02'),
    (1,1,'a','DEC',200, '2016-12-24')

    -- Your working month table
    IF OBJECT_ID('tempdb..#table_month') is not null
        DROP TABLE #table_month
    CREATE TABLE #table_month (
        wmonth              VARCHAR(3),
        DateBegin           DATE,
        EndBegin            DATE
    )
    INSERT INTO #table_month VALUES 
    ('JAN', '2016-01-01', '2016-01-31'),
    ('FEV', '2016-02-01', '2016-02-29'),
    ('MAR', '2016-03-01', '2016-03-31'),
    ('APR', '2016-04-01', '2016-04-30'),
    ('MAI', '2016-05-01', '2016-05-31'),
    ('JUN', '2016-06-01', '2016-06-30'),
    ('JUL', '2016-07-01', '2016-07-31'),
    ('AUG', '2016-08-01', '2016-08-31'),
    ('SET', '2016-09-01', '2016-09-30'),
    ('OCT', '2016-10-01', '2016-10-31'),
    ('NOV', '2016-11-01', '2016-11-30'),
    ('DEC', '2016-12-01', '2016-12-31')


    SELECT PEN, Employer, tm.wmonth
    FROM #table_month tm
    LEFT JOIN #view_remit vr
    ON vr.wmonth = tm.wmonth
    WHERE vr.wmonth is null

I'm guessing those are Portuguese abbreviations, but the OP's are in English. It's worth noting that [`upper(date_format(start, '%b'))`](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) avoids the need to have a hard coded and possibly incorrect `month` column and will honor localization. — Schwern, Mar 02 '17 at 03:32

score 0 · Answer 2 · answered Mar 02 '17 at 03:26

0

Normally you'd use the EXCEPT set operator for this. Set up a table called months with all the months in it, then select all those months except the ones in view_remit.

select month from months
except
select distinct month from view_remit

But MySQL doesn't support EXCEPT. Instead, use a left outer join to get the months which are not in view_remit.

select distinct months.month
from months
left join view_remit vr on months.month = vr.month
where vr.month is null;

answered Mar 02 '17 at 03:26

Schwern

153,029
25
195
336

is it because there are many `PEN` i need to get the months which are notint he view remit for each `PEN` when I used group by, nothing happened – Clorae Mar 03 '17 at 14:59
@Clorae Sorry, I'm not sure what that all means. – Schwern Mar 03 '17 at 15:32
What I need is that I need to get the months which are not in view remit per `PEN`. in my example. – Clorae Mar 03 '17 at 16:29
@Clorae I see. `PEN` and `Employer` will be null because you're only getting back the months that don't match. I'm not sure how to do what you're asking for in SQL, but I'm sure it's possible. – Schwern Mar 03 '17 at 16:43

How to find the missing month in a column in mysql?

2 Answers2