MySQL query to find missing values in time series

Asked Mar 04 '15 at 13:37

Active Mar 04 '15 at 13:37

Viewed 226 times

I have a table containing thousands of customer records, with customer ids ideally repeating for every month.

Customer Org | CustomerID | Month
001          | 111        | Jan-15
001          | 112        | Jan-15
001          | 113        | Jan-15
001          | 111        | Feb-15
001          | 112        | Feb-15
001          | 111        | Mar-15
001          | 113        | Mar-15

I would like to write a SQL query that gives me 'inconsistent' customers within a customer org, i.e those who do not have records every month.

For eg, if I pass the customer org '001', the expected output needs to be

Jan-15 | Feb-15 | Mar-15
112    | 112    | -
113    | -      | 113

Thanks for your help in advance

asked Mar 04 '15 at 13:37

Arvind S

Oracle? SQL Server? MySQL? In my opinion you need use PIVOT. – starko Mar 04 '15 at 13:38
MySQL. I have edited the qtn and tag to reflect MySQL. Thanks. – Arvind S Mar 04 '15 at 13:39
Look for that. It's look similar: http://stackoverflow.com/questions/7674786/mysql-pivot-table – starko Mar 04 '15 at 13:40
is it possible that multiple entry for a customer can exist in the same month? – 1000111 Mar 04 '15 at 14:18
@SubrataDey yes, I have the same customer ids repeating per month for accounting purposes and would like to identify the missing ones. – Arvind S Mar 04 '15 at 14:56

MySQL query to find missing values in time series

0 Answers0