iVend Database Database : vwAnalytics_UpSellSales 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

Store

VarWChar

100

Net Sales

Numeric

9 (22,5)

UpSell Sales

Numeric

9 (22,5)

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

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

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE VIEW [dbo].[vwAnalytics_UpSellSales]

as

SELECT convert(datetime,convert(nvarchar(10),T.BusinessDate, 101), 101) BusinessDate, S.Description as Store,

CASE WHEN TSI.Type = 1 THEN -1 * TSI.TotalPostSaleDiscount ELSE TSI.TotalPostSaleDiscount END AS [Net Sales]

, CASE WHEN IsUpsellItem = 1 THEN CASE WHEN TSI.Type = 1 THEN -1 * TSI.TotalPostSaleDiscount ELSE TSI.TotalPostSaleDiscount END ELSE 0 END [UpSell Sales]

FROM TrxTransactionSaleItem TSI

INNER JOIN TrxTransaction T ON TSI.TransactionKey = T.TransactionKey

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

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

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

WHERE TSI.Type IN (0, 1, 3, 5)

AND T.IsVoided = 0

AND T.IsSuspended = 0

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