MySQL populate empty date values

Question

I am running some query's against our incident logging database for our individual customers. What I want to return is all the month names from 2012 and a count of how many open incidents there have been. This works fine for a customer who has been with us for over 12 months but not for a customer who has been with us for a shorter period of time.

The query I have is this:

    SELECT DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y') as 'Year',
    DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%M') as 'Month',
    count(wo.workorderid) as 'Total Logged'
    FROM workorder_threaded wot
    inner join workorder wo
    on wo.workorderid = wot.workorderid
    and wo.siteid = 4806
    and DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y') <> '2011'
    where wot.workorderid = wot.thd_woid
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(createdtime/1000), '%Y%m')

The output I get is this:

    Year    Month       Total Logged
    2012    August      3
    2012    September   356
    2012    October     212
    2012    November    120

I need however the following:

    Year      Month       Total Logged
    2012      January     0 
    2012      February    0
    2012      March       0
    2012      April       0
    2012      May         0
    2012      June        0
    2012      July        0 
    2012      August      3
    2012      September   356
    2012      October     212
    2012      November    120
    2012      December    0

It doesn't have to be limited by year i.e. the call logging DB has data in this from 2011 so the query can be modified to filter the year range.

I know that this is not populating because the data does not exist and I have seen ways of creating a numbers \ date table and referencing this to populate the date but am struggling to do this. I have also changed around joins and where conditions to no avail.

Thanks in advance.

Germann Arlington · Accepted Answer · 2012-11-20 11:50:09Z

1

You need to use LEFT JOIN here

answered Nov 20, 2012 at 11:50

Germann Arlington

3,3532 gold badges19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Greg Over a year ago

If I use left join i get the following output:

Greg Over a year ago

Year Month Total Logged Null Null 0 2012 August 3 2012 September 356 2012 October 212 2012 November 120

Germann Arlington Over a year ago

Yes, and these Nulls correspond to the year and month that you will need to either generate or JOIN from another table. I am not going to write your query for you, you will have to work on it yourself. BTW: You can use standard SQL date functions to get Year and Month from your date and in fact you should use the generated Year and Month in your output because even your existing user may have no activity in some months. And it is better to compare to the actual date/year - i.e. and YEAR = '2012' instead of and YEAR <> '2011'

Greg Over a year ago

I don't think you completely understand what I am trying to do but thanks for the response and the tips about query structure.

Germann Arlington Over a year ago

As I understood your requirements you wanted a result for EVERY month in a given year (2012) with number of workorders per month. This is how I read your question...

Collectives™ on Stack Overflow

MySQL populate empty date values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related