SQLTeam.com | Weblogs | Forums

Really stuck and need help


#1

I am using SQL 2012

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 end result I want:
image


#2

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)


#3

These are the movement types:

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

#4

This is how I get MOVED IN

--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 #])

#5

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]

#6

And now I am stuck on getting "REACTIVATED" correctly... Someone please help me...