iVend Database Database : vwAnalytics_SalesTransaction View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

BusinessDate

DBTimeStamp

4

Total

Numeric

9 (20,5)

TransactionId

VarWChar

50

Store

VarWChar

100

Customer

VarWChar

100

CustomerGroup

VarWChar

100

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

CusCustomerGroup

Table

Defines the groups in which the customers can be catagorised.

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

CREATE VIEW [dbo].[vwAnalytics_SalesTransaction]

AS

SELECT   convert(datetime,convert(nvarchar(10),T.BusinessDate, 101), 101) BusinessDate, T.Total,

     T.TransactionId,

     S.Description as Store, C.FirstName as Customer

     , CG.Description as CustomerGroup

FROM         dbo.TrxTransaction AS T

INNER JOIN dbo.RtlStore AS S ON T.StoreKey = S.StoreKey

INNER JOIN dbo.CusCustomer AS C ON T.CustomerKey = C.CustomerKey

INNER JOIN dbo.CusCustomerGroup CG ON C.CustomerGroupKey = CG.CustomerGroupKey

INNER JOIN (SELECT TOP 1 SiteId from CfgSiteInformation) As T5 on 1=1

LEFT OUTER JOIN RtlStore T6 ON T5.SiteId = t6.SiteId

WHERE T.Total > 0

AND T.IsVoided = 0

AND T.IsSuspended = 0

AND T.StoreKey = CASE WHEN T5.SiteId = 1 THEN T.StoreKey ELSE T6.StoreKey END