I've been struggling with a problem of identifying movements week by week for customers and their products... I started another thread but I think it confused people...
There is data in SQL which I generate in a precount table (it is readied). It has all the weeks where there is some kind of customer/product change, we run this report each week for customer movement.
I will use one customer as an example because it's less data (there are 500,000+ records in the raw for all customers and all their week by week product movements):
This is the raw data this specific customer has. Notice how there is no week 38, I generate line 38 once I see that line 37 doesn't have a subsequent line 38.
i've been able to also identify Acquired anytime there is no previous record week with the same customer # and product name (group desc1)
I am stuck on correctly identifying Reactivations. If there is a record week <12 it is a reactivation, if it's >12 weeks then it is considred acquired (been too long so its treated as a new acquirement of the customer due to the vast difference, basically at 13 weeks it considered acquired 12 weeks or less it's reactivated)
ACQUIRED - No previous weeks or >13 weeks since last time particular customer had exact product
DEACTIVATED - Last week customer had a product, this week no longer the same prodcut
REACTIVATED - Customer had record of particular product 12< weeks ago (when it was last deactivated).
MOVED OUT - Customer last week had a product, this week doesn't, but has other products still
MOVED IN - Last week the customer did not have this product so it's new in this week
This is my solution for finding DEACTIVATED and MOVED OUT:
--DEACTIVATED and MOVED OUT
DROP TABLE #CountDeactAndOut
SELECT DISTINCT
a.[Week Num] + 1 as [Week Num],
a.[Fiscal Year],
a.[Fiscal Week] + 1 as [Fiscal Week],
a.[Customer #],
a.[Group Description],
a.[Customer Type],
a.[Sales ORG],
CASE
WHEN a.[Week Num]+1 = c.[Week Num] THEN 'MOVED OUT' ELSE 'DEACTIVATED' END
as [Movement]
INTO #CountDeactAndOut
FROM #PreCountTable as a
LEFT JOIN #PreCountTable as c
ON a.[Customer #] = c.[Customer #] AND a.[Week Num]+1 = c.[Week Num]
WHERE NOT EXISTS(
SELECT *
FROM #PreCountTable 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
--MOVED IN
DROP TABLE #CountingIN
SELECT DISTINCT
a.[Week Num],
a.[Fiscal year],
a.[Fiscal Week],
a.[Customer #],
a.[Group Description],
a.[Customer Type],
a.[Sales ORG],
'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 #])
This is the continuation of how I get ACQUIRED and MOVED IN
--ACQUIRED AND MOVED IN
DROP TABLE #AcqAndin
SELECT DISTINCT
AcqInit.[Week Num],
AcqInit.[Fiscal Year],
AcqInit.[Fiscal Week],
AcqInit.[Customer #],
AcqInit.[Group Description],
AcqInit.[Customer Type],
AcqInit.[Sales ORG],
CASE
WHEN INtb.[Movement]='MOVED IN' THEN INtb.[Movement]
WHEN AcqInit.[Movement]='ACQUIRED' THEN AcqInit.[Movement]
END AS [Movement]
INTO #AcqAndIn
FROM
(SELECT DISTINCT
minT.[Week Num],
preCT.[Fiscal Year],
preCT.[Fiscal Week],
minT.[Customer #],
minT.[Group Description],
preCT.[Customer Type],
preCT.[Sales ORG],
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 #] = '123456'
ORDER BY AcqInit.[Fiscal Week], AcqInit.[Group Description]
