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
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
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.
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.
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
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
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
