Finding gaps in weeks for unique customers and their weekly products

a_ahmed · November 15, 2017, 6:42pm

I pull data from SQL for all weeks where unique customers are represented and their products they own, purchase, get off of.

I may have product A, B, C for customer 1.

Customer 1 in week 27 up to week 44 has all three products but on week 45 cancels all three.

I would have to represent the changes. Week 27 is supposed to be an acquired line in this week, three products, three lines.

Week 44 is the last week they own the product. Week 45 they are considered deactivated (three lines for each product).

I don't want line 28 to 44 for the three products, I just want week 27 3 lines for each product and 'acquired'. Week 45 I want all 3 lines as deactivated.

Between these weeks a customer could become
Acquired
Deactivated
Reactivated

Acquired means they had nothing before and this is a new product
Deactivated means this week they have no product
Reactivated means they had the same product in a previous week but for a few weeks didn't use it.

How could I go about doing this?

I wish i could upload pictures of the tables.

a_ahmed · November 15, 2017, 6:43pm

I figured out how to upload pix, this is the simplified single customer example with identical products (products could vary):

SQL PULL:

a_ahmed · November 15, 2017, 6:44pm

MOVEMENT DETAILS:

a_ahmed · November 15, 2017, 6:44pm

MIGRATION DETAILS:

bitsmed · November 15, 2017, 8:03pm

Before going into "solve-mode", please respong to these items:

is "group description" the product?
your "sql pull" looks like a result from a view/query - if so, the query might be faster, querying directly on the tables used in the view/query (we'd need consumable data from those tables then)
please post consumable data (create table and insert statement - not pics)
please explain your fiscal start, so we can design a "week tally thingie" than suite your case
please explain what would you want to see, if (lets say) week 27 a customer has product a+b+c, in week 28 they have a+c, in week 29 they have b+c?
do you have a table containing product line?

a_ahmed · November 17, 2017, 1:32pm

Ok I've been able to build a query that gets me 'Deactivated' (Nothing in next week) and 'Moved Out' (Client remains client but product change) working.

I will still need to do 'Acquired' (First time this product is received), 'Moved In' (What the new product is that wasn't in previous week). And 'Reactivated' (Meaning the client was inactive, but the last time they were active, they had the same product that they were reactivated into).

The criteria are [customer #] and [Group Description] (Which is the product).

--DEACTIVATED and MOVED OUT
SELECT
	[Fiscal Year],
	[Fiscal Week],
	[Customer #],
	[Customer Full Name],
	[DG Name],
	[Group Description],
	[Customer Type],
	[Sales ORG],
	[Sales Person],
	[Sales Manager],
	[Sales Director],
		[Movement],
	[Fiscal Week1],
	[Fiscal Week2]
INTO #DEACTnOUT
FROM (
	SELECT
		a.[Fiscal Year],
		--a.[Week Num] + 1 as [Week Num],
		a.[Fiscal Week] + 1 as [Fiscal Week],
		a.[Customer #],
		BaseTb.[Customer Full Name],
		IsNull(BaseTb.[DG Name],'') as [DG Name],
		a.[Group Description],
		a.[Customer Type],
		a.[Sales ORG],
		BaseTb.[Sales Person],
		BaseTb.[Sales Manager],
		BaseTb.[Sales Director],
		CASE
			WHEN a.[Week Num]+1  = c.[Week Num] THEN 'MOVED OUT' ELSE 'Deactivated' END
		
		as [Movement],
		a.[Fiscal Week] as [Fiscal Week1],
		c.[Fiscal Week] as [Fiscal Week2]
	FROM #Viewoa as a
		LEFT JOIN #CustomerMovementCountBaseTable as BaseTb
		ON a.[Customer #] = BaseTb.[Customer #] AND a.[Week Num] = BaseTb.[Week Num]

		LEFT JOIN #Viewoa as c
		ON a.[Customer #] = c.[Customer #] AND a.[Week Num]+1 = c.[Week Num]
	WHERE NOT EXISTS(
		SELECT *
		FROM #Viewoa as b
		WHERE b.[Week Num]  = a.[Week Num] + 1
		AND a.[Group Description] = b.[Group Description]
		AND a.[Customer #] = b.[Customer #])
	AND a.[Fiscal Week] < 45 )aaa
	--AND a.[Customer #]='07488701' ) a
GROUP BY
	[Fiscal Year],
	[Fiscal Week],
	[Customer #],
	[Customer Full Name],
	[DG Name],
	[Group Description],
	[Customer Type],
	[Sales ORG],
	[Sales Person],
	[Sales Manager],
	[Sales Director],
	[Movement],
	[Fiscal Week1],
	[Fiscal Week2]
HAVING [Customer #] = '07490551' AND [Fiscal Week] = @RunWeek
ORDER BY [Fiscal Week], [Customer #], [Group Description], [Movement]

I need to exclude anything that is in the previous week from the main table with the same group description but give it the value of MOVED IN the current week in the movement field.

a_ahmed · November 17, 2017, 1:34pm

The above code is still incomplete with some test items like restricting week and customer # (for testing purposes)

So for example I have:

a_ahmed · November 17, 2017, 1:41pm

So what the source table has is this:

So because Lead Tracker is missing in the next week you get the "MOVED OUT" because a week after the week 44 exists.

Now what kind of query could I write that takes the MOVED OUT results and allows me to generate a line for week 45 which makes V3 MIN DIAMOND as "MOVED IN".

As you can say between week 44 and 45 there exists the same products, I want to exclude those.

I tried to do table1.[Fiscal Week] -1 = table2.[Fiscal Week] and to match Customer # and to say <> to [Group Description] but it still outputs all the week 45 rows. It makes sense since it's comparing the one line to all the lines.... so how do I work around that?

a_ahmed · November 17, 2017, 4:03pm

Basically what I need to do is find the week of the "MOVED OUT" which in this case is 44, then in the table where both 44 and 45 records are found, I want week 45 records with [Group Description] that do not exist for week 44. In this case it would be V3 MIN DIAMOND

How would I do that?

a_ahmed · November 17, 2017, 4:30pm

if I only have one record in the table this works... but if I have more records this obviously won't work... I hope someone is following along

SELECT DISTINCT
a.[Fiscal year],
a.[Fiscal Week],
a.[Customer #],
a.[Group Description],
'MOVED IN' as [Movement]
FROM #Viewoa as a
WHERE
a.[Fiscal Week]= (SELECT [Fiscal Week] FROM #DEACTnOUT)
AND
a.[Customer #]= (SELECT [Customer #] FROM #DEACTnOUT WHERE [MOVEMENT]='MOVED OUT')
AND
NOT a.[Group Description] IN
(SELECT [Group Description] FROM #Viewoa WHERE
[Fiscal Week]=
(SELECT [Fiscal Week]-1 FROM #DEACTnOUT)
AND
[Customer #] =
(SELECT [Customer #] FROM #DEACTnOUT)
)

a_ahmed · November 20, 2017, 2:31pm

I managed this to work to get moved in working...

--MOVED IN
DROP TABLE #CountingIN
SELECT DISTINCT
	a.[Week Num],
	a.[Fiscal year],
	a.[Fiscal Week],
	a.[Customer #], 
	--deact.[Customer Full Name],
	--deact.[DG Name],
	a.[Group Description],
	a.[Customer Type],
	a.[Sales ORG],
	--deact.[Sales Person],
	--deact.[Sales Manager],
	--deact.[Sales Director],
	--deact.[Fiscal Week],
	--a.[Fiscal Week]-1,
	'MOVED IN' as [Movement]
INTO #CountingIN
FROM #PreCountTable as a
	LEFT JOIN #CountDeactAndOut as deact ON
	deact.[Fiscal Week] = a.[Fiscal Week]
WHERE
	a.[Fiscal Week]= Deact.[Fiscal Week]
AND
	a.[Customer #]= Deact.[Customer #]
AND
NOT a.[Group Description] IN
	(SELECT [Group Description]
	FROM #PreCountTable
	WHERE
	[Fiscal Week]=  a.[Fiscal Week]-1 AND [Customer #] = a.[Customer #])

However this does not account for Reactivated and Acquired.

a_ahmed · November 20, 2017, 4:59pm

I also got Acquired and Moved in working correctly together as they are different, the problems I now need to figure out 'reactivated' and 'acquired' which is not in these...

--ACQUIRED AND MOVED IN
SELECT DISTINCT
	AcqInit.[Week Num],
	AcqInit.[Fiscal Year],
	AcqInit.[Fiscal Week],
	AcqInit.[Customer #],
	--preCT.[Customer Full Name],
	--preCT.[DG Name],
	AcqInit.[Group Description],
	AcqInit.[Customer Type],
	AcqInit.[Sales ORG],
	--preCT.[Sales Person],
	--preCT.[Sales Manager],
	--preCT.[Sales Director],
	CASE
		WHEN INtb.[Movement]='MOVED IN' THEN INtb.[Movement]
		WHEN AcqInit.[Movement]='ACQUIRED' THEN AcqInit.[Movement]
		END AS [Movement]
FROM
(SELECT DISTINCT
	minT.[Week Num],
	preCT.[Fiscal Year],
	preCT.[Fiscal Week],
	minT.[Customer #],
	--preCT.[Customer Full Name],
	--preCT.[DG Name],
	minT.[Group Description],
	preCT.[Customer Type],
	preCT.[Sales ORG],
	--preCT.[Sales Person],
	--preCT.[Sales Manager],
	--preCT.[Sales Director],
	minT.[Movement]
FROM (
SELECT
min([Week Num]) as [Week Num],
[Customer #],
[Group Description],
'ACQUIRED' as [Movement]
FROM #PreCountTable
GROUP BY [Customer #], [Group Description]) as minT
	LEFT JOIN #PreCountTable as preCT ON
	minT.[Week Num] = preCT.[Week Num]
	AND
	minT.[Customer #] = preCT.[Customer #]
) as AcqInit
	LEFT JOIN #CountingIN as INtb ON
	AcqInit.[Week Num] = INtb.[Week Num]
	AND
	AcqInit.[Customer #] = INtb.[Customer #]
WHERE  AcqInit.[Customer #] = '07071473'
ORDER BY AcqInit.[Fiscal Week], AcqInit.[Group Description]

For example: If I have Product A acquired in week 5 and until week 10 the records for Product A appear, week 11 is considered Deactivated, however if few weeks later the same product is reintroduced if it's 12<= week difference it would be considered 'reactivated' but if it's 13>= it is considered acquired.

How would I be able to do that best?

#Precount table has all weeks records.
#CountDeactAndOut has MOVED OUT and DEACTIVATED

The latest query uses #CountingIN which included MOVED IN and also generates properly MOVED IN and Acquired but will miss those reactivatoins and acquired that are 13> weeks.

a_ahmed · November 22, 2017, 1:30pm

There has to be someone who understands what I am trying to do...