SQL for adding data linked to the filtering dimension

Avatar

Avatar
Validate 1
Employee
Linda_Stinson
Employee

Likes

51 likes

Total Posts

250 posts

Correct reply

77 solutions
Top badges earned
Validate 1
Ignite 5
Ignite 3
Ignite 20
Ignite 10
View profile

Avatar
Validate 1
Employee
Linda_Stinson
Employee

Likes

51 likes

Total Posts

250 posts

Correct reply

77 solutions
Top badges earned
Validate 1
Ignite 5
Ignite 3
Ignite 20
Ignite 10
View profile
Linda_Stinson
Employee

07-06-2014

This is from the Recipient RFM example where all Recipients are pulled in the initial Query and then we add data linked to the filtering dimension to compute recency, frequency and monetary.

INSERT INTO wkf505995918_2_2 (dPurchaseHistoryMonetary,tsPurchaseHistoryRecency,iPurchaseHistoryFrequency,iPurchaseHistoryKeysPKey0) SELECT DISTINCT   Sum(P0.dAmount), Max(P0.tsDate), Count(P0.iPurchaseHistoryId), P0.iRecipientId FROM NeoPurchaseHistory P0 JOIN wkf505995918_2_1 W ON (W.iLinksKey1_0=P0.iRecipientId)  WHERE (P0.tsDate >= add_months(CAST((GetDate()) AT TIME ZONE 'America/New_York' AS date), -(18))) AND ((P0.iPurchaseHistoryId > 0 OR P0.iPurchaseHistoryId < 0)) GROUP BY P0.iRecipientId

Note the JOIN between the linked NeoPurchaseHistory table and the wkf505995918_2_1 table populated by the initial query. The JOIN is ON the Recipient ID.