SQLTeam.com | Weblogs | Forums

Need help to form query to Calculate Missing Month

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 below format. Please help me to form a SQL query to find out how we can get missing month flag value.

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

SELECT
case when Column1 is null Then 1 else o end
+ case when Column2 is null Then 1 else o end
+ case when Column3 is null Then 1 else o end
as Any_Nulls
FROM
Table

case
when
Any_Nulls >0 then 'YES'
else
'NO' end
as Month_Skip

maybe

case when col1+col2+col3+........+ colmuN is null 
       then 'Yes' else 'No' end as [Month skip]
SELECT 
    Customer, 
    Month_Skip,
    CASE WHEN 
    CASE WHEN [Oct-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Sep-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Aug-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Jul-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Jun-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [May-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Apr-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Mar-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Feb-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Jan-20] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Dec-19] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Nov-19] IS NULL THEN '0' ELSE '1' END +
    CASE WHEN [Oct-19] IS NULL THEN '0' ELSE '1' END         
        LIKE '%1%0%1%' THEN 'Yes' ELSE 'No' END     
        AS Month_Skip_Calculated,
    [Oct-20], [Sep-20], [Aug-20], [Jul-20], [Jun-20], [May-20], 
    [Apr-20], [Mar-20], [Feb-20], [Jan-20], [Dec-19], [Nov-19],
    [Oct-19] 
FROM #CustData
3 Likes

I'm virtually certain that "month skipped" means one or months has a value, followed by one or more months that are NULL, followed by one or more months that have a non-NULL value again.

@ScottPletcher
LIKE '%1%0%1%

new to me and surely unable to fathom what it means.
Could you please explain what it means for my knowledge and understanding
Specially two extra % sign.

Thanks

If you look at the CASE expression - that is returning either a 1 or 0 for each column. If the column has a value it is a 1 and if it does not have a value it is 0.

The pattern check is then looking for a missing (null) value between 1 one values.

Pattern 000001111111 would not match and this is not 'missing' any months.
Pattern 111111000000 would also not match because there are no missing months.
Pattern 111100011111 matches and shows missing months

1 Like

That's the difference between a novice and an expert.
I completely misunderstood the question.
I didnt' got it even after when @ScottPletcher said
<<I'm virtually certain that "month skipped" >>

Basically I didn't match my answer with the @anuxps given data
which is clearly saying a month skip as NO even if the
last value is NULL

Hi Mateen

Welcome to Neuro Science

The art and science of connecting dots and making sense

PHD subject that ANY ONE can do .. like learning to cook or playing Cricket

Involves the same things ..

You are awesome. You understand the problem correctly and provide a great solution.

if this was to be done dynamically with unknown sets of columns. But not sure how to do this on the key Month Skip column.

use sqlteam
go

create table #customer(Customer varchar(50), sales int, salesdate date )

insert into #customer
select distinct name, system_type_id * 35, dateadd(yy, column_id, getdate())  
  from sys.all_columns
  where column_id between 1 and 3
union 
select distinct name, system_type_id * 35,  dateadd(yy, column_id*-1, getdate())  
  from sys.all_columns
  where column_id between 4 and 6
union 
select distinct name, null, dateadd(yy, column_id*-1, getdate())  
  from sys.all_columns
  where column_id between 7 and 9
union 
select top 10000 name, system_type_id * 35,  dateadd(yy, column_id*-1, getdate())  
  from sys.all_columns
  where column_id between 10 and 12

insert into #customer
select distinct '_Obi One Kanobi', isnull(sales,22),  salesdate 
  from #customer


declare @cols nvarchar(max), @query nvarchar(max);

SELECT  @cols = STUFF(( select  distinct ',' +  QUOTENAME(md) 
from (
		select left(datename(m, salesdate), 3) + '-' + right(datename(yy,salesdate), 2) as md
From #customer
) c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')


set @query = '
select customer,  1 [Month Skip],  ' + @cols + ' 
 from (
		select sales, a.customer,
		       left(datename(m, salesdate), 3) + ''-'' + right(datename(yy,salesdate), 2) as md
		From #customer  a

)x
pivot 
(
sum(sales)
for md in (' + @cols + ')
) p 
order by customer
'
exec(@query)
drop table #customer