WITH tm AS
(
SELECT DISTINCT *
FROM ##tbresult
WHERE [Customer Id] = '1011707'
UNION ALL
SELECT DISTINCT *
FROM ##tbresult
WHERE [Customer Id] = '1473125'
UNION ALL
SELECT [Month Name],
[Year Name],
[Month End Date] = Eomonth((CONVERT(DATETIME,[Month End Date]) + 466), 0),
[Customer Id],
[Customer Name],
[Product Level 2],
[Product Level 3]
FROM ##tbresult
WHERE [Customer Id] = '1473125' ), tablebasecross AS
(
SELECT DISTINCT dt.[Month Name],
dt.[Year Name],
[Month End Date] = Eomonth(dt.[Month Date], 0),
[Customer Id],
[Product Level 2],
[Product Level 3]
FROM [SalesDriversDatamart].[rpt].[Dim Date] DT
CROSS JOIN tm
WHERE (
Eomonth(dt.[Month Date], 0) BETWEEN
(
SELECT Min(tm.[Month End Date])
FROM tm)
AND
(
SELECT Max(Eomonth((Getdate()+466), 0))
FROM tm)) -- Currenty Date
), tablebase AS
(
SELECT DISTINCT dt.[Month Name],
dt.[Year Name],
dt.[Month End Date],
dt.[Customer Id],
tm.[Product Level 2],
tm.[Product Level 3],
active =
CASE
WHEN (
tm.[Month End Date] IS NULL) THEN 0
ELSE 1
END
FROM tablebasecross DT
LEFT JOIN tm
ON tm.[Month End Date] = dt.[Month End Date]
AND dt.[Customer Id] = tm.[Customer Id]
WHERE (
dt.[Month End Date] BETWEEN
(
SELECT Min(tm.[Month End Date])
FROM tm)
AND
(
SELECT Max(Eomonth((Getdate()+466), 0))
FROM tm)) -- Currenty Date
), tablebasemonthid AS
(
SELECT [Month ID] = Row_number() OVER (partition BY [Customer Id] ORDER BY [Customer Id], [Month End Date]),
*
FROM tablebase ), tablebaseactiveid AS
(
SELECT [Active Group] = ([Month ID] - (sum(active) OVER (partition BY [Customer Id] ORDER BY [Customer Id], [Month ID] rows BETWEEN 9999 PRECEDING AND CURRENT row))),
*
FROM tablebasemonthid ), tablebasegroup AS
(
SELECT [Active Group ID] = row_number() OVER (partition BY [Customer Id], [Active Group] ORDER BY [Customer Id], [Month ID]),
*
FROM tablebaseactiveid )
SELECT
--T.*,
[Month ID],
[Active Group ID],
[Month Name],
[Year Name],
[Month End Date],
[Customer Id],
[Product Level 2],
[Product Level 3],
[Active],
[Existing Customer] = isnull(lag(active) OVER(partition BY [Customer Id] ORDER BY [Customer Id], [Month ID]),0),
[Customer Churn] =
CASE
WHEN (
active= 1) THEN 0
ELSE 1
END,
[Level Swapped] =
CASE
WHEN (
lag([Product Level 2]) OVER(partition BY [Customer Id] ORDER BY [Month ID]) = [Product Level 2]) THEN 0
ELSE
CASE
WHEN (
(
[Month ID]=1)
OR (
active=0
AND [Month ID]<>0)) THEN 0
ELSE 1
END
END,
[Customer Returned] =
CASE
WHEN (
isnull(lag(active) OVER(partition BY [Customer Id] ORDER BY [Month ID]),0) <> active) THEN
CASE
WHEN (
(
active=0
AND [Month ID]<>1)
OR (
[Month ID]=1)) THEN 0
ELSE 1
END
ELSE 0
END,
[Growth of Customer] =
CASE
WHEN (
lag(active) OVER(partition BY [Customer Id] ORDER BY [Month ID]) = 1) THEN 0
ELSE 1
END,
[Growth of New Customer] =
CASE
WHEN (
lag(active) OVER(partition BY [Customer Id] ORDER BY [Month ID]) = 1) THEN 0
ELSE
CASE
WHEN (
active=1
AND [Month ID]<>1) THEN 0
ELSE 1
END
END,
[Growth of Old Customer] =
CASE
WHEN (
lag(active) OVER(partition BY [Customer Id] ORDER BY [Month ID]) = 0) THEN 1
ELSE 0
END,
[Active Group ID],
[Short Term Customer] =
CASE
WHEN (
active = 1)
AND (
[Active Group ID] <= 16) THEN 1
ELSE 0
END,
[Long Term Customer] =
CASE
WHEN (
active = 1)
AND (
[Active Group ID] > 16) THEN 1
ELSE 0
END,
[Short Term Churn] =
CASE
WHEN (
active = 0)
AND (
lag([Active Group ID]) OVER(partition BY [Customer Id] ORDER BY [Month ID]) <= 16) THEN 1
ELSE 0
END,
[Long Term Churn] =
CASE
WHEN (
active = 0)
AND (
lag([Active Group ID]) OVER(partition BY [Customer Id] ORDER BY [Month ID]) > 16) THEN 1
ELSE 0
END
FROM tablebasegroup AS t
ORDER BY [Customer Id],
[Month ID]
