Hi Team, below is the report view which is causing slowness , let me know any suggestions if you have
CREATE VIEW [REPORT].[View_MachinePerformanceBySlot] AS
SELECT
VA.SITE_NUM,
VA.SLOT_NUMBER,
VA.AREA_NAME,
VA.MANUFACTURER_NAME,
VA.ATYP_ID,
VA.HOLD_PERCENTAGE,
VA.SLOT_DENOM,
VA.SERIAL_NUM,
VA.AREA_ID,
a.THEM_NAME,
a.GAME_NAME,
a.TCAT_LONG_NAME,
a.TGRP_LONG_NAME,
VA.MTYP_NAME,
VA.OWNER_LABEL_KEY,
vsmr.SDS_Bets AS HANDLE,
vsmr.SDS_Plays AS HANDLE_PULL,
vsmr.Days_On AS DAYS_ACTIVE,
VA.GAME_TYPE,
Mtr_NamedAsstID,
Mtr_GameDay AS MVR_GDAY_DATE,
vsmr.PTYP_ID AS MVR_PTYP_ID,
Mtr_PeriodType AS PERIOD_TYPE,
CAST(SDS_Bets AS FLOAT) AS MVR_BETS,
CAST(SLIP_APJP_JACKPOT + SLIP_PROGRESSIVE_JAKPT
+ SLIP_MYST_JACKPOT + SLIP_CC_JACKPOT
+ SLIP_CELEBRATION_JACKPOT + vsmr.CASH_PROG+vsmr.NONCASH_PROG
AS FLOAT)AS JACKPOTS,
SDS_MachinePaidProgressiveWins,
MVR_THEORETICAL_WIN,
(CASE WHEN vsmr.SDS_Bets = 0 THEN 0
ELSE (MVR_THEORETICAL_WIN * 100) / vsmr.SDS_Bets
END) AS ACTUAL_PERCENTAGE,
((CASE WHEN vsmr.SDS_Bets = 0 THEN 0 ELSE (MVR_THEORETICAL_WIN * 100) / vsmr.SDS_Bets END)
- VA.HOLD_PERCENTAGE) AS VAR_HOLD_PERCENT,
Days_On AS MVR_DAYS_ONLINE_VAL,
(vsmr.SDS_1_bills + vsmr.SDS_5_bills + vsmr.SDS_10_bills + vsmr.SDS_20_bills
+ vsmr.SDS_50_bills + vsmr.SDS_100_bills + vsmr.SDS_CoinDrop) AS Bills_Coins,
CAST(vsmr.SDS_1_bills + vsmr.SDS_5_bills + vsmr.SDS_10_bills + vsmr.SDS_20_bills
+ vsmr.SDS_50_bills + vsmr.SDS_100_bills + vsmr.SDS_CoinDrop +
+ vsmr.SDS_EFTInCashablePromo + vsmr.SDS_EFTInNonCashable
+ vsmr.SDS_EFTInCashable + vsmr.SDS_TicketInCashable + vsmr.SDS_TicketInNonCashable
+ vsmr.SDS_TicketInPromoCashable
-( vsmr.SLIP_APJP_JACKPOT + vsmr.SLIP_PROG_JAKPT_ST + vsmr.SLIP_CC_JACKPOT +vsmr.SLIP_MYST_JACKPOT
+ vsmr.SLIP_DISPUTE + vsmr.SLIP_FILL+vsmr.SLIP_CELEBRATION_JACKPOT +vsmr.CASH_PROG
+vsmr.NONCASH_PROG - vsmr.SLIP_BLEED
)
-( vsmr.SDS_EFTOutCashablePromo+vsmr.SDS_EFTOutNonCashable+vsmr.SDS_EFTOutCashable+
SDS_TicketOutNonCashable+SDS_TicketOutCashable
)
AS FLOAT) AS SDS_WIN,
(vsmr.SLIP_APJP_JACKPOT + vsmr.SLIP_PROG_JAKPT_ST + vsmr.SLIP_CC_JACKPOT+vsmr.SLIP_MYST_JACKPOT
+ vsmr.SLIP_DISPUTE + vsmr.SLIP_FILL+vsmr.SLIP_CELEBRATION_JACKPOT - vsmr.SLIP_BLEED
)AS SLIP_EXPENSES,
-- WIN
CAST((vsmr.SDS_1_bills + vsmr.SDS_5_bills + vsmr.SDS_10_bills + vsmr.SDS_20_bills
+ vsmr.SDS_50_bills + vsmr.SDS_100_bills + vsmr.SDS_CoinDrop +
+ vsmr.SDS_EFTInCashablePromo + vsmr.SDS_EFTInNonCashable
+ vsmr.SDS_EFTInCashable + vsmr.SDS_TicketInCashable + vsmr.SDS_TicketInNonCashable
+ vsmr.SDS_TicketInPromoCashable
)AS FLOAT) AS WIN,
-- SHORTS
(vsmr.SLIP_APJP_JACKPOT +vsmr.SLIP_PROG_JAKPT_ST + vsmr.SLIP_CC_JACKPOT
+vsmr.SLIP_DISPUTE +vsmr.SLIP_CELEBRATION_JACKPOT
+(vsmr.SLIP_FILL - vsmr.SLIP_BLEED)
+(vsmr.SDS_EFTOutCashablePromo+vsmr.SDS_EFTOutNonCashable+vsmr.SDS_EFTOutCashable
+SDS_TicketOutNonCashable+SDS_TicketOutCashable)
)AS SHORTS,
-- MYSTERY_SHORT
vsmr.SLIP_MYST_JACKPOT + vsmr.CASH_PROG + vsmr.NONCASH_PROG AS MYSTERY_SHORT,
-- SLIP_LINK_PROG_JAKPT
ISNULL(SLIP_LINK_PROG_JAKPT, 0) AS SLIP_LINK_PROG_JAKPT,
-- ACTUAL_WIN
(ISNULL(ActualMtr.ACTUAL_CASH_COUPON_VAL, 0)
+ISNULL(ActualMtr.ACTUAL_NONCASH_COUPON_VAL, 0)
+ISNULL((ActualMtr.ACTUAL_1_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_5_BILLS), 0)
+ISNULL((ActualMtr.ACTUAL_10_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_20_BILLS), 0)
+ISNULL((ActualMtr.ACTUAL_50_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_100_BILLS), 0)
+ISNULL(vamcr.SCALE_AMT, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINCASH, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINNONCASH, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINPROMOCASH, 0)
+(vsmr.SDS_EFTInCashablePromo + vsmr.SDS_EFTInNonCashable + vsmr.SDS_EFTInCashable )
-(vsmr.SLIP_APJP_JACKPOT + vsmr.SLIP_PROG_JAKPT_ST + vsmr.SLIP_CC_JACKPOT+vsmr.SLIP_MYST_JACKPOT
+ vsmr.SLIP_DISPUTE + vsmr.SLIP_FILL +vsmr.SLIP_CELEBRATION_JACKPOT
+vsmr.CASH_PROG+vsmr.NONCASH_PROG - vsmr.SLIP_BLEED)
-(vsmr.SDS_EFTOutCashablePromo+vsmr.SDS_EFTOutNonCashable+vsmr.SDS_EFTOutCashable
+SDS_TicketOutNonCashable+SDS_TicketOutCashable)
-ISNULL(SLIP_LINK_PROG_JAKPT, 0)
) AS ACTUAL_WIN
,(ISNULL(ActualMtr.ACTUAL_CASH_COUPON_VAL, 0)
+ISNULL(ActualMtr.ACTUAL_NONCASH_COUPON_VAL, 0)
+ISNULL((ActualMtr.ACTUAL_1_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_5_BILLS), 0)
+ISNULL((ActualMtr.ACTUAL_10_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_20_BILLS), 0)
+ISNULL((ActualMtr.ACTUAL_50_BILLS), 0) + ISNULL((ActualMtr.ACTUAL_100_BILLS), 0)
+ISNULL(vamcr.SCALE_AMT, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINCASH, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINNONCASH, 0)
+ISNULL(ActualMtr.ACTUAL_TKTINPROMOCASH, 0)
+ISNULL(vsmr.SDS_EFTInCashablePromo,0 )
+ISNULL(vsmr.SDS_EFTInNonCashable, 0)
+ISNULL(vsmr.SDS_EFTInCashable, 0)) AS PHY_WIN
,(vsmr.CASH_PROG_SLOT_CONTRIBUTION + vsmr.CASH_PSR_ARV_AMT_VAL+vsmr.NON_CASH_PROG_SLOT_CONTRIBUTION
+vsmr.NON_CASH_PSR_ARV_AMT_VAL ) AS PROVISION,
ISNULL(a.PTBL_NO_OF_PAYLINES,0) AS PTBL_NO_OF_PAYLINES -- check with ARV
FROM ACCOUNTING.VIEW_SDS_METER_ROLLUP_WITH_PROG AS vsmr
JOIN REPORT.VIEW_ASSET VA
ON VA. NAMED_ASSET_ID = Mtr_NamedAsstID
LEFT JOIN Accounting.VIEW_ACTUAL_METER_PERIODIC_ROLLUP AS ActualMtr
ON (ActualMtr.NAMEDASSTID = vsmr.Mtr_NamedAsstID)
AND (ActualMtr.GAMEDAY = vsmr.Mtr_GameDay)
AND (ActualMtr.PTYP_ID = vsmr.PTYP_ID)
LEFT JOIN ACCOUNTING.VIEW_ACTUAL_METER_COIN_ROLLUP vamcr
ON vamcr.CN_NAMEDASSTID = vsmr.Mtr_NamedAsstID
AND vamcr.CN_GAMEDAY = vsmr.Mtr_GameDay
AND vamcr.CN_PTYP_ID = vsmr.PTYP_ID
LEFT JOIN
(SELECT NAGI_NAST_ID,AT.THEM_NAME,TC.TCAT_LONG_NAME,Tg.TGRP_LONG_NAME ,PTBL.PTBL_NO_OF_PAYLINES,aco.USER_CUSTOM10 AS GAME_NAME FROM
ACCOUNTING.NAMED_ASSET_GAME_INFO
LEFT JOIN ACCOUNTING.GAME_INFO gf
ON gf.GINFO_ID = NAGI_GINFO_ID
AND NAGI_IS_LATEST=1
JOIN ACCOUNTING.PAYTABLE PTBL
ON PTBL.PTBL_ID = gf.GINFO_PTBL_ID
JOIN ASSET.THEME AT
ON AT.THEM_ID= gf.GINFO_ASST_THME_ID
JOIN ASSET.THEME_CATEGORY tc
ON TC.TCAT_ID = AT.THEME_PARENT_ID
JOIN asset.THEME_GROUP tg
on tc.TCAT_TGRP_ID=tg.TGRP_ID
JOIN asset.THEME_TYPE TT
on TT.TTYP_ID=AT.TTYP_ID
JOIN ACCOUNTING.NAMED_ASSET na
on na.NAST_ID = NAGI_NAST_ID
JOIN ASSET.ASSET_CONFIGURATION ac
on ac.ACNF_NUMBER = na.NAST_NAME AND ac.ACNF_DELETED_TS is null
JOIN ASSET.ASSET_CONFIGURATION_OPTION aco
on aco.ACNF_ID = ac.ACNF_ID
) as a
ON vsmr.Mtr_NamedAsstID =a.NAGI_NAST_ID
Other recent topics
