Verloren klanten berekenen met T-SQL

1
WITH KL
2
AS
3
-- Genereer een overzicht van alle datum/klant combinaties
4
 (SELECT DISTINCT DK.ACCOUNTNUM AS KlantID
5
 , CONVERT(VARCHAR, FH.InvoiceDate, 112) AS DatumID
6
 , FH.InvoiceDate AS Datum
7
 , DD.Jaar
8
 , FR.LINEAMOUNT AS Bedrag
9
 FROM dbo.CUSTINVOICETRANS AS FR -- FactuurRegel
10

11
 INNER JOIN dbo.CUSTINVOICEJOUR AS FH -- FactuurHeader
12
 ON (FR.INVOICEID = FH.INVOICEID)
13

14
 INNER JOIN dbo.CUSTTABLE AS DK -- Klant
15
 ON (FH.ORDERACCOUNT = DK.ACCOUNTNUM)
16

17
 INNER JOIN dbo.SMMBUSRELSALESDISTRICTGROUP AS SD -- SalesDistrict
18
 ON (DK.SALESDISTRICTID = SD.SALESDISTRICTID)
19
 AND (SD.DATAAREAID = 'pp')
20

21
 INNER JOIN DimDatum AS DD -- DimDatum
22
 ON ( CONVERT(VARCHAR, FH.INVOICEDATE, 112) = DD.DatumId)
23

24
 INNER JOIN INVENTTABLE AS DP -- DimProduct
25
 ON (FR.ITEMID = DP.ITEMID)
26

27
 WHERE FR.DATAAREAID = 'pp'
28
 AND FH.DATAAREAID = 'pp'
29
 AND DK.DATAAREAID = 'pp'
30
 AND DP.DATAAREAID = 'pp'
31
 )
32

33
-- Genereer een lijst van alle klanten en eerste dag van maand-combinaties
34
-- voor alle dagen in het huidige jaar en in het vorige jaar.
35
-- Later gaan we voor deze datums bepalen of de klant verloren is op deze datum
36
, KD -- KlantDatum
37
AS (SELECT DISTINCT ACCOUNTNUM AS KlantID,
38
 DK.NAME AS KlantNaam,
39
 SD.[DESCRIPTION] AS SalesDistrict,
40
 DD.DatumId,
41
 DD.DatumWaarde,
42
 DD.Jaar
43
 FROM CUSTTABLE AS DK
44
 INNER JOIN SMMBUSRELSALESDISTRICTGROUP AS SD
45
 ON (DK.SALESDISTRICTID = SD.SALESDISTRICTID)
46
 CROSS JOIN DimDatum AS DD
47

48
 WHERE DD.Jaar IN ( YEAR(DATEADD(YY,-1,GETDATE())), YEAR(GETDATE()) )
49
 AND DD.DagNummerVanMaand = 1
50
 AND DD.DatumId <= CONVERT( VARCHAR, GETDATE(), 112)
51
 -- evt aanzetten als je het hele jaar wilt tonen, ook de toekomst
52
 -- AND DD.Jaar <= YEAR(GETDATE())
53

54
 )
55

56
SELECT DISTINCT KLD.KlantID
57
 , KlantNaam
58
 , KLD.SalesDistrict
59
 , KLD.DatumId
60
 , DD.Jaar
61
 , DD.MaandNummerVanJaar
62
 , DD.MaandCode
63
 , (CASE WHEN KL.KlantID IS NULL THEN 1 ELSE 0 END) AS IsVerloren
64
FROM KD AS KLD
65
-- Met deze join controleer je of de klant in het huidige jaar gefactureerd is op een tijdstip
66
-- die voor de datum ligt waarop je kijkt. Als dit niet het geval is dan is de klant op dit moment
67
-- verloren. Op het moment dat hij gefactureerd is dan is hij niet meer verloren
68
LEFT OUTER JOIN KL AS KL ON KLD.KlantID = KL.KlantID
69
 AND KL.DatumID <= KLD.DatumId
70
 AND KLD.Jaar = KL.Jaar
71

72
INNER JOIN DimDatum AS DD
73
 ON (KLD.DatumId = DD.DatumId)
74

75
-- Controleer of hij vorig jaar gefactureerd is
76
WHERE EXISTS ( SELECT 1 FROM KL AS VJ WHERE VJ.Jaar= KLD.Jaar-1 AND KLD.KlantID = VJ.KlantID)
77

78
ORDER BY 1,2