iVend Database Database : Loyalty_ActiveTransactionCount View

Creation Date

6/5/2015 6:37 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

LoyaltyId

VarWChar

50

MemberOrder

Integer

4

LoyaltyMemberId

VarWChar

20

LoyaltyMemberDesc

VarWChar

100

SlabID

VarWChar

20

SlabDescription

VarWChar

100

StoreId

VarWChar

20

StoreDesc

VarWChar

100

CustomerID

VarWChar

20

RegistrationDate

DBTimeStamp

4

RegistrationMethod

Integer

4

LoyaltyProgram

VarWChar

40

LoyaltyProgramDesc

VarWChar

200

FirstName

VarWChar

100

LastName

VarWChar

40

Gender

VarWChar

50

BirthDate

DBTimeStamp

4

AnniversaryDate

DBTimeStamp

4

MobilePhone

VarWChar

50

Email

VarWChar

100

ActualDate

DBTimeStamp

4

WeekDay

VarWChar

30

TransactionCount

Integer

4

Database Object

Object Type

Description

Dep Level

CfgLoyaltyConfig

Table

Stores the configuration information for the loyalty

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

LoyLoyaltyCardInformation

Table

Store the Details of Loyalty Customer in the system.

1

LoyMembershipGroup

Table

Stores the Header detail of Loyalty Membership Group in the system.

1

LoySlab

Table

Stores Loyalty Slab configured in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

---Declare @Store NVarchar(400) = '', @FromDate Varchar(8) = '20150201', @ToDate Varchar(8) = '20150531'

CREATE View [dbo].[Loyalty_ActiveTransactionCount]

AS

With R(N)

AS(

Select N=1

UNION ALL

Select N+1

From R

Where N<7

)

, Q AS

(

Select

Distinct LCI.LoyaltyId, 1 AS MemberOrder, 'No Member Group' AS LoyaltyMemberID, 'No Member Group' AS LoyaltyMemberDesc,

 LS.Id AS SlabID, LS.Description AS SlabDescription, ISNULL(Store.Id, 'HO') AS StoreId, ISNULL(Store.Description, 'Enterprise') AS StoreDesc,

 Cust.Id AS CustomerID, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id As LoyaltyProgram, Cg.Description AS LoyaltyProgramDesc,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.FirstName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.FirstName,'') END AS FirstName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.LastName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.LastName,'') END AS LastName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Gender WHEN LCI.RegistrationMethod=2 THEN LCI.Gender END AS Gender,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.BirthDate WHEN LCI.RegistrationMethod=2 THEN LCI.BirthDate END AS BirthDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.AnniversaryDate WHEN LCI.RegistrationMethod=2 THEN LCI.AnniversaryDate END AS AnniversaryDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.MobilePhone WHEN LCI.RegistrationMethod=2 THEN LCI.MobilePhone END AS MobilePhone,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

 Trx.ActualDate,DateName(dw,Trx.ActualDate)[WeekDay] ,Count(Trx.TransactionKey)TransactionCount

From

LoyLoyaltyCardInformation LCI WITH (NOLOCK)

LEFT OUTER JOIN LoySlab LS WITH (NOLOCK) ON LCI.SlabKey = LS.SlabKey

LEFT OUTER JOIN TrxTransaction Trx WITH (NOLOCK) ON Trx.LoyaltyId = LCI.LoyaltyId AND Trx.IsVoided =0 AND Trx.IsSuspended=0

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON LCI.CustomerKey = Cust.CustomerKey AND ISNULL(LCI.IsDeleted,0) =0

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON Trx.StoreKey = Store.StoreKey

INNER JOIN CfgLoyaltyConfig Cg ON LCI.LoyaltyConfigKey = Cg.LoyaltyConfigKey

Where

--LCI.RegistrationMethod = (Select RegistrationMethod From CfgLoyaltyConfig) AND

LCI.HasMembershipGroup=0

AND LCI.LoyaltyId IN (Select LoyaltyId From TrxTransaction )

Group BY

LCI.LoyaltyID, LS.Id, LS.Description, Store.Id, Store.Description, Cust.Id, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id, Cg.Description,

LCI.FirstName, LCI.LastName, LCI.Gender, LCI.BirthDate, LCI.AnniversaryDate, LCI.MobilePhone, LCI.Email, Cust.FirstName, Cust.LastName,

Cust.Gender, Cust.BirthDate, Cust.AnniversaryDate, Cust.MobilePhone, Cust.Email, Trx.ActualDate, DATENAME(Dw, Trx.ActualDate)

UNION ALL

Select

Distinct LCI.LoyaltyId, 2 AS MemberOrder, LM.Id AS LoyaltyMemberID, LM.Description AS LoyaltyMemberDesc,

 LS.Id AS SlabID, LS.Description AS SlabDescription, ISNULL(Store.Id, 'HO') AS StoreId, ISNULL(Store.Description, 'Enterprise') AS StoreDesc,

 Cust.Id AS CustomerID, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id As LoyaltyProgram, Cg.Description AS LoyaltyProgramDesc,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.FirstName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.FirstName,'') END AS FirstName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.LastName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.LastName,'') END AS LastName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Gender WHEN LCI.RegistrationMethod=2 THEN LCI.Gender END AS Gender,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.BirthDate WHEN LCI.RegistrationMethod=2 THEN LCI.BirthDate END AS BirthDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.AnniversaryDate WHEN LCI.RegistrationMethod=2 THEN LCI.AnniversaryDate END AS AnniversaryDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.MobilePhone WHEN LCI.RegistrationMethod=2 THEN LCI.MobilePhone END AS MobilePhone,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

 Trx.ActualDate,DateName(dw,Trx.ActualDate)[WeekDay] ,Count(Trx.TransactionKey)TransactionCount

From

LoySlab LS WITH (NOLOCK)

LEFT OUTER JOIN LoyMembershipGroup LM WITH (NOLOCK) ON LS.SlabKey=LM.SlabKey

LEFT OUTER JOIN LoyLoyaltyCardInformation LCI WITH (NOLOCK) ON LM.MembershipGroupKey = LCI.MembershipGroupKey AND ISNULL(LCI.IsDeleted,0) = 0

LEFT OUTER JOIN TrxTransaction Trx WITH (NOLOCK) ON Trx.LoyaltyId = LCI.LoyaltyId AND Trx.IsVoided =0 AND Trx.IsSuspended=0

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON LCI.CustomerKey = Cust.CustomerKey AND ISNULL(LCI.IsDeleted,0) =0

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON Trx.StoreKey = Store.StoreKey

INNER JOIN CfgLoyaltyConfig Cg ON LCI.LoyaltyConfigKey = Cg.LoyaltyConfigKey

Where

--LCI.RegistrationMethod = (Select RegistrationMethod From CfgLoyaltyConfig) AND

LCI.HasMembershipGroup=1

AND LCI.LoyaltyId IN (Select LoyaltyId From TrxTransaction )

Group BY

LCI.LoyaltyID, LM.Id, LM.Description, LS.Id, LS.Description,Store.Id, Store.Description, Cust.Id, LCI.RegistrationDate, LCI.RegistrationMethod,

Cg.Id, Cg.Description, LCI.FirstName, LCI.LastName, LCI.Gender, LCI.BirthDate, LCI.AnniversaryDate, LCI.MobilePhone, LCI.Email, Cust.FirstName,

Cust.LastName, Cust.Gender, Cust.BirthDate, Cust.AnniversaryDate, Cust.MobilePhone, Cust.Email, Trx.ActualDate, DATENAME(Dw, Trx.ActualDate)

)

Select

LoyaltyId, MemberOrder, LoyaltyMemberId, LoyaltyMemberDesc, SlabID, SlabDescription, StoreId, StoreDesc, CustomerID, RegistrationDate,

RegistrationMethod, LoyaltyProgram, LoyaltyProgramDesc, FirstName, LastName, Gender, BirthDate, AnniversaryDate, MobilePhone, Email, ActualDate,

DATENAME(Dw, R.N) AS 'WeekDay', TransactionCount

From R LEFT OUTER JOIN Q ON Q.[WeekDay] = DATENAME(DW, N)