Get Missing Month Information

Question

I want to know what are the customers having missing month data based on the following data pattern. My data is present in table in the below format. Please help me to form a SQL query to find out how we can get the missing month flag value.

Condition is: If a customer is having any single or multiple months missing in between of start and end date then that customer would be considered as having missing month data. Start Date and end date both could be vary based on the customer to customer data. Here is a sample of data:

Answer 1

Hi @Anubhav Saxena , are you open to changing the layout of your table? The current layout doesn't scale well. Here is an example of a format that might work better for you and would be easier to query.

Answer 2

If NULL means "missing data", try this:

SELECT Customer,
CASE WHEN SUM(
CASE WHEN Col1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Col2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Col3 IS NULL THEN 0 ELSE 1 END +
......
) = Number_Of_Date_Columns THEN 'No' ELSE 'Yes' END AS [Month Skip]
FROM YourTable

Answer 3

Hi @Anubhav Saxena ，

If the missing value after the end date can be represented by -1 instead of null, then the problem becomes simple：

declare @test table (customer char(15),Oct20 int,Sep20 int,Aug20 int,Jul20 int,Jun20 int  
,May20 int,Apr20 int,Mar20 int,Feb20 int,Jan20 int,Dec20 int,Nov20 int,Oct19 int)  
insert into @test values('A',29,24,21,18,22,23,24,20,20,17,14,-1,-1)  
,('B',80,73,0,0,null,null,null,null,null,null,null,0,0)  
,('C',48,39,38,36,35,35,35,28,28,27,27,27,26)  
,('D',1,1,4,2,2,3,3,null,null,null,null,4,4)  
  
SELECT Customer,  
CASE WHEN SUM(  
CASE WHEN Oct20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Sep20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Aug20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Jul20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Jun20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN May20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Apr20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Mar20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Feb20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Jan20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Dec20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Nov20 IS NULL THEN 0 ELSE 1 END +  
CASE WHEN Oct19 IS NULL THEN 0 ELSE 1 END   
) over(partition by Customer ) = 13 THEN 'No' ELSE 'Yes' END [Month Skip]  
,Oct20,Sep20,Aug20,Jul20,Jun20  
,May20,Apr20,Mar20,Feb20,Jan20,Dec20,Nov20,Oct19  
FROM @test  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi,

Did you perform any join operation on the table?