SQL Techniques for Advanced Data Analysis

Let's break down and complete the SQL query step by step. The query is designed to analyze slot machine data over a specific date range, focusing on metrics like total games, total seconds, time per spin, and various other metrics by machine, property, and manufacturer.

Declare Variables: The first step is to declare and set the necessary date variables.

DECLARE@todayDATE=CONVERT(DATETIME, GETDATE() ATTIME ZONE 'UTC'ATTIME ZONE 'Pacific Standard Time');
DECLARE@startDATE, @endDATE;

SET@start=CAST('01/01/'+CAST(YEAR(@today) -2ASVARCHAR(4)) ASDATE);
SET@end= DATEADD(DAY, -2, @today);

Temporary Tables (CTEs): Temp: This CTE extracts distinct section codes from the machine_dim table.

WITH Temp AS (
    SELECTDISTINCT section_cd AS section
    FROM [slots_mart_vw].[machine_dim]
),

Temp1: This CTE calculates the total number of games played and the total seconds of play per machine, property, and year.

Temp1 AS (
    SELECTYEAR(start_dttm) ASYear,
           ent_prop_cd,
           gm_inst_id AS machine_num,
           CAST(SUM(gm_played_cnt) ASFLOAT) AS TotalGames,
           CAST(SUM(tm_played_secs_cnt) ASFLOAT) AS TotalSeconds
    FROM [slots_mart_vw].[slots_player_rating_trans_dtl_fct] player
    WHERE accounting_dt >=@startGROUPBYYEAR(start_dttm), ent_prop_cd, gm_inst_id
),

RatedUtlLtd: This CTE calculates the average time per spin by combining the Temp1 data with machine details from machine_dim and game type details from gm_type_dim.

RatedUtlLtd AS (
    SELECT r.Year AS _Year,
           r.ent_prop_cd,
           gm_type_desc,
           denom_amt,
           CAST(SUM(TotalSeconds) ASFLOAT) /CAST(SUM(TotalGames) ASFLOAT) AS TimePerSpin
    FROM Temp1 r
    INNERJOIN [slots_mart_vw].[machine_dim] machine 
            ON machine.ent_prop_cd = r.ent_prop_cd AND machine.machine_num = r.machine_num
    LEFTJOIN [slots_mart_vw].[gm_type_dim] gt 
            ON gt.ent_prop_cd = machine.ent_prop_cd AND gt.gm_type_id = machine.gm_type_id
    GROUPBY r.Year, r.ent_prop_cd, gm_type_desc, denom_amt
),

Temp2: This CTE collects detailed slot machine performance data by joining various metrics from different tables.

Temp2 AS (
    SELECT prop_nm AS Property,
           MONTH(accounting_dt) ASMonth,
           YEAR(accounting_dt) ASYear,
           fct.ent_prop_cd AS SiteID,
           fct.status_cd AS Status,
           fct.machine_type_id AS TypeID,
           fct.serial_num AS Serial,
           fct.machine_num AS Mnum,
           install_dt AS InstallDate,
           section_cd AS Section,
           ROW_NUMBER() OVER (ORDERBYCASEWHEN ISNUMERIC(section) =1THENCAST(section ASNUMERIC) ELSE99999999999999END, 
               CASEWHEN ISNUMERIC(section) =1THENNULLELSE section END) AS SectionSort,
           bank_cd AS Bank,
           denom_amt AS Denom,
           mfr_desc AS MFR,
           machine_type_desc AS Description,
           par_pct AS Par,
           model_cd AS Model,
           gm_type_desc AS GT,
           cabinet_type_cd AS Cab,
           pay_lines_num AS Lines,
           reel_stop_num AS Reels,
           max_coins_num AS MaxCoins,
           lease_flag AS Leased,
           lease_setting_nm AS LeaseSettingName,
           lease_type_desc AS Type,
           SUM(tot_dy_cnt) AS Days,
           SUM(rated_gm_cnt) AS Games,
           SUM(rated_handle_amt) AS Handle,
           SUM(rated_coin_out_amt) AS CoinOut,
           SUM(rated_free_play_amt) AS FP,
           SUM(rated_jackpot_amt) AS Jackpots,
           SUM(handle_fee_amt) AS HandleFee,
           SUM(win_fee_amt) AS WinFee,
           SUM(dly_fee_amt) AS DailyFee,
           high_limit_flag AS HighLimit,
           SUM(lease_fee_amt) AS LeaseFees,
           CASEWHEN gm_Cnt =0THENNULLWHEN handle_amt / gm_cnt <=3THEN'Low'WHEN handle_amt / gm_cnt >3AND handle_amt / gm_cnt <=10THEN'Mid'ELSE'High'ENDAS AvgBetSegment3,
           @todayAS RunDate,
           mfr_desc AS MFR2,
           DATEDIFF(DAY, install_dt, @today) /365.25AS Cab_age,
           CASEWHEN DATEDIFF(DAY, install_dt, @today) /365.25<3THEN'1-3 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=3AND DATEDIFF(DAY, install_dt, @today) /365.25<5THEN'3-5 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=5AND DATEDIFF(DAY, install_dt, @today) /365.25<7THEN'5-7 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=7AND DATEDIFF(DAY, install_dt, @today) /365.25<10THEN'7-10 year'ELSE'10+ year'ENDAS CabAge_cat,
           CASEWHEN mfr_desc IN ('Ainsworth', 'AGT') THEN'Ainsworth'WHEN mfr_desc IN ('Aristocrat', 'ATI') THEN'Aristocrat'WHEN mfr_desc IN ('Aruze', 'AGA') THEN'Aruze'WHEN mfr_desc IN ('Everi', 'MMG') THEN'Everi'WHEN mfr_desc IN ('Konami', 'KGI') THEN'Konami'WHEN mfr_desc IN ('BAL', 'Bally', 'Sci Games', 'SciGames', 'Sci', 'WMS') THEN'LNW'WHEN mfr_desc IN ('ITS', 'INC') THEN'INC'ELSE mfr_desc ENDAS MFR_,
           CASEWHEN prop_nm IN ('Bellagio', 'MGM Grand Las Vegas', 'New York New York', 'Excalibur', 'Luxor', 'Aria', 'Mandalay Bay', 'Park MGM', 'Mirage') THEN'Las Vegas'ELSE'Regional'ENDAS Market,
           accounting_dt AS date1,
           CASEWHEN rated_gm_cnt >=0AND rated_gm_cnt <10THEN'Y'ELSE'N'ENDAS OOS,
           'Rated'AS Rating_Type,
           rated_gross_win_amt AS Grosswin,
           rated_theo_win_amt AS Theowin,
           rated_net_win_amt AS Netwin,
           CASEWHEN gm_type_desc ='RE'THEN rated_gm_cnt / (8.35*NULLIF(tot_dy_cnt, 0) *24*60)
                WHEN gm_type_desc ='VR'THEN rated_gm_cnt / (8.16*NULLIF(tot_dy_cnt, 0) *24*60)
                WHEN gm_type_desc ='VP'THEN rated_gm_cnt / (7.43*NULLIF(tot_dy_cnt, 0) *24*60)
                WHEN gm_type_desc ='OT'THEN rated_gm_cnt / (6.99*NULLIF(tot_dy_cnt, 0) *24*60)
                WHEN gm_type_desc ='ET'THEN rated_gm_cnt / (2.07*NULLIF(tot_dy_cnt, 0) *24*60)
                ELSE0ENDAS utl,
           rated_comp_earned_amt AS Rated_CompEarned,
           rated_pt_earn_amt AS Rated_PointsEarned,
           player_cnt AS Players,
           rated_trip_cnt AS Trips,
           CASEWHENYEAR(accounting_dt) =YEAR(@today) ANDMONTH(accounting_dt) =MONTH(@today) THEN DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0), @today) -1WHENMONTH(accounting_dt) =1THEN31WHENMONTH(accounting_dt) =2ANDYEAR(accounting_dt) %4=0THEN29WHENMONTH(accounting_dt) =2ANDYEAR(accounting_dt) %4!=0THEN28WHENMONTH(accounting_dt) =3THEN31WHENMONTH(accounting_dt) =4THEN30WHENMONTH(accounting_dt) =5THEN31WHENMONTH(accounting_dt) =6THEN30WHENMONTH(accounting_dt) =7THEN31WHENMONTH(accounting_dt) =8THEN31WHENMONTH(accounting_dt) =9THEN30WHENMONTH(accounting_dt) =10THEN31WHENMONTH(accounting_dt) =11THEN30WHENMONTH(accounting_dt) =12THEN31ELSE30ENDAS DIP,
           CONCAT(prop_nm, accounting_dt) AS PropertyDate,
           SUM(handle_amt) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) /SUM(NULLIF(tot_dy_cnt, 0)) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) AS S_HPU,
           SUM(gross_win_amt) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) /SUM(NULLIF(tot_dy_cnt, 0)) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) AS G_WPU
    FROM [slots_mart_vw].[slots_player_rating_trans_dtl_fct] fct
    INNERJOIN [slots_mart_vw].[machine_dim] md
            ON fct.ent_prop_cd = md.ent_prop_cd AND fct.machine_num = md.machine_num
    LEFTJOIN [slots_mart_vw].[gm_type_dim] gt 
            ON md.ent_prop_cd = gt.ent_prop_cd AND md.gm_type_id = gt.gm_type_id
    WHERE accounting_dt BETWEEN@startAND@endGROUPBY prop_nm, MONTH(accounting_dt), YEAR(accounting_dt), fct.ent_prop_cd, fct.status_cd, fct.machine_type_id, fct.serial_num, fct.machine_num, install_dt, section_cd, bank_cd, denom_amt, mfr_desc, machine_type_desc, par_pct, model_cd, gm_type_desc, cabinet_type_cd, pay_lines_num, reel_stop_num, max_coins_num, lease_flag, lease_setting_nm, lease_type_desc, high_limit_flag, rated_gross_win_amt, rated_theo_win_amt, rated_net_win_amt, rated_comp_earned_amt, rated_pt_earn_amt, player_cnt, rated_trip_cnt
)

Final Select Statement: The final select statement would combine the data from these CTEs (common table expressions) to produce the desired output. However, the original query is incomplete and doesn't have a final SELECT statement. Here's an example of how it might look:

SELECT*FROM Temp2;

This will give you a comprehensive view of slot machine metrics across various properties, machines, and time periods, with the data categorized and calculated in multiple ways for deeper analysis.

Full code

DECLARE@todayDATE=CONVERT(DATETIME, GETDATE() ATTIME ZONE 'UTC'ATTIME ZONE 'Pacific Standard Time');
DECLARE@startDATE, @endDATE;
SET@start=CAST('01/01/'+CAST(YEAR(@today) -2ASVARCHAR(4)) ASDATE);
SET@end= DATEADD(DAY, -2, @today);

WITH Temp AS (
    SELECTDISTINCT section_cd AS section
    FROM [slots_mart_vw].[machine_dim]
),
Temp1 AS (
    SELECTYEAR(start_dttm) ASYear,
        ent_prop_cd,
        gm_inst_id AS machine_num,
        CAST(SUM(gm_played_cnt) ASFLOAT) AS TotalGames,
        CAST(SUM(tm_played_secs_cnt) ASFLOAT) AS TotalSeconds
    FROM 
        [slots_mart_vw].[slots_player_rating_trans_dtl_fct] player
    WHERE 
        accounting_dt >=@startGROUPBYYEAR(start_dttm), 
        ent_prop_cd, 
        gm_inst_id
),
RatedUtlLtd AS (
    SELECT 
        r.Year AS _Year,
        r.ent_prop_cd,
        gm_type_desc,
        denom_amt,
        CAST(SUM(TotalSeconds) ASFLOAT) /CAST(SUM(TotalGames) ASFLOAT) AS TimePerSpin
    FROM 
        Temp1 r
    INNERJOIN 
        [slots_mart_vw].[machine_dim] machine 
        ON machine.ent_prop_cd = r.ent_prop_cd AND machine.machine_num = r.machine_num
    LEFTJOIN 
        [slots_mart_vw].[gm_type_dim] gt 
        ON gt.ent_prop_cd = machine.ent_prop_cd AND gt.gm_type_id = machine.gm_type_id
    GROUPBY 
        r.Year, 
        r.ent_prop_cd, 
        gm_type_desc, 
        denom_amt
),
Temp2 AS (
    SELECT 
        prop_nm AS Property,
        MONTH(accounting_dt) ASMonth,
        YEAR(accounting_dt) ASYear,
        fct.ent_prop_cd AS SiteID,
        fct.status_cd AS Status,
        fct.machine_type_id AS TypeID,
        fct.serial_num AS Serial,
        fct.machine_num AS Mnum,
        install_dt AS InstallDate,
        section_cd AS Section,
        ROW_NUMBER() OVER (ORDERBYCASEWHEN ISNUMERIC(section) =1THENCAST(section ASNUMERIC) ELSE99999999999999END, 
            CASEWHEN ISNUMERIC(section) =1THENNULLELSE section END) AS SectionSort,
        bank_cd AS Bank,
        denom_amt AS Denom,
        mfr_desc AS MFR,
        machine_type_desc AS Description,
        par_pct AS Par,
        model_cd AS Model,
        gm_type_desc AS GT,
        cabinet_type_cd AS Cab,
        pay_lines_num AS Lines,
        reel_stop_num AS Reels,
        max_coins_num AS MaxCoins,
        lease_flag AS Leased,
        lease_setting_nm AS LeaseSettingName,
        lease_type_desc AS Type,
        SUM(tot_dy_cnt) AS Days,
        SUM(rated_gm_cnt) AS Games,
        SUM(rated_handle_amt) AS Handle,
        SUM(rated_coin_out_amt) AS CoinOut,
        SUM(rated_free_play_amt) AS FP,
        SUM(rated_jackpot_amt) AS Jackpots,
        SUM(handle_fee_amt) AS HandleFee,
        SUM(win_fee_amt) AS WinFee,
        SUM(dly_fee_amt) AS DailyFee,
        high_limit_flag AS HighLimit,
        SUM(lease_fee_amt) AS LeaseFees,
        CASEWHEN gm_Cnt =0THENNULLWHEN handle_amt / gm_cnt <=3THEN'Low'WHEN handle_amt / gm_cnt >3AND handle_amt / gm_cnt <=10THEN'Mid'ELSE'High'ENDAS AvgBetSegment3,
        @todayAS RunDate,
        mfr_desc AS MFR2,
        DATEDIFF(DAY, install_dt, @today) /365.25AS Cab_age,
        CASEWHEN DATEDIFF(DAY, install_dt, @today) /365.25<3THEN'1-3 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=3AND DATEDIFF(DAY, install_dt, @today) /365.25<5THEN'3-5 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=5AND DATEDIFF(DAY, install_dt, @today) /365.25<7THEN'5-7 year'WHEN DATEDIFF(DAY, install_dt, @today) /365.25>=7AND DATEDIFF(DAY, install_dt, @today) /365.25<10THEN'7-10 year'ELSE'10+ year'ENDAS CabAge_cat,
        CASEWHEN mfr_desc IN ('Ainsworth','AGT') THEN'Ainsworth'WHEN mfr_desc IN ('Aristocrat','ATI') THEN'Aristocrat'WHEN mfr_desc IN ('Aruze','AGA') THEN'Aruze'WHEN mfr_desc IN ('Everi','MMG') THEN'Everi'WHEN mfr_desc IN ('Konami','KGI') THEN'Konami'WHEN mfr_desc IN ('BAL', 'Bally', 'Sci Games', 'SciGames', 'Sci', 'WMS') THEN'LNW'WHEN mfr_desc IN ('ITS', 'INC') THEN'INC'ELSE mfr_desc 
        ENDAS MFR_,
        CASEWHEN prop_nm IN ('Bellagio','MGM Grand Las Vegas','New York New York','Excalibur','Luxor','Aria','Mandalay Bay','Park MGM','Mirage') THEN'Las Vegas'ELSE'Regional'ENDAS Market,
        accounting_dt AS date1,
        CASEWHEN rated_gm_cnt >=0AND rated_gm_cnt <10THEN'Y'ELSE'N'ENDAS OOS,
        'Rated'AS Rating_Type,
        rated_gross_win_amt AS Grosswin,
        rated_theo_win_amt AS Theowin,
        rated_net_win_amt AS Netwin,
        CASEWHEN gm_type_desc ='RE'THEN rated_gm_cnt / (8.35*NULLIF(tot_dy_cnt, 0) *24*60) 
            WHEN gm_type_desc ='VR'THEN rated_gm_cnt / (8.16*NULLIF(tot_dy_cnt, 0) *24*60) 
            WHEN gm_type_desc ='VP'THEN rated_gm_cnt / (7.43*NULLIF(tot_dy_cnt, 0) *24*60)
            WHEN gm_type_desc ='OT'THEN rated_gm_cnt / (6.99*NULLIF(tot_dy_cnt, 0) *24*60) 
            WHEN gm_type_desc ='ET'THEN rated_gm_cnt / (2.07*NULLIF(tot_dy_cnt, 0) *24*60) 
            ELSE0ENDAS utl,
        rated_comp_earned_amt AS Rated_CompEarned,
        rated_pt_earn_amt AS Rated_PointsEarned,
        player_cnt AS Players,
        rated_trip_cnt AS Trips,
        CASEWHENYEAR(accounting_dt) =YEAR(@today) ANDMONTH(accounting_dt) =MONTH(@today) THEN DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0), @today) -1WHENMONTH(accounting_dt) =1THEN31WHENMONTH(accounting_dt) =2ANDYEAR(accounting_dt) %4=0THEN29WHENMONTH(accounting_dt) =2ANDYEAR(accounting_dt) %4!=0THEN28WHENMONTH(accounting_dt) =3THEN31WHENMONTH(accounting_dt) =4THEN30WHENMONTH(accounting_dt) =5THEN31WHENMONTH(accounting_dt) =6THEN30WHENMONTH(accounting_dt) =7THEN31WHENMONTH(accounting_dt) =8THEN31WHENMONTH(accounting_dt) =9THEN30WHENMONTH(accounting_dt) =10THEN31WHENMONTH(accounting_dt) =11THEN30WHENMONTH(accounting_dt) =12THEN31ELSE30ENDAS DIP,
        CONCAT(prop_nm, accounting_dt) AS PropertyDate,
         SUM(handle_amt) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) /SUM(NULLIF(tot_dy_cnt, 0)) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) AS S_HPU,
           SUM(gross_win_amt) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) /SUM(NULLIF(tot_dy_cnt, 0)) OVER (PARTITIONBY fct.ent_prop_cd, section_cd, denom_amt, gm_type_desc, MONTH(accounting_dt), YEAR(accounting_dt)) AS G_WPU
    FROM [slots_mart_vw].[slots_player_rating_trans_dtl_fct] fct
    INNERJOIN [slots_mart_vw].[machine_dim] md
            ON fct.ent_prop_cd = md.ent_prop_cd AND fct.machine_num = md.machine_num
    LEFTJOIN [slots_mart_vw].[gm_type_dim] gt 
            ON md.ent_prop_cd = gt.ent_prop_cd AND md.gm_type_id = gt.gm_type_id
    WHERE accounting_dt BETWEEN@startAND@endGROUPBY prop_nm, MONTH(accounting_dt), YEAR(accounting_dt), fct.ent_prop_cd, fct.status_cd, fct.machine_type_id, fct.serial_num, fct.machine_num, install_dt, section_cd, bank_cd, denom_amt, mfr_desc, machine_type_desc, par_pct, model_cd, gm_type_desc, cabinet_type_cd, pay_lines_num, reel_stop_num, max_coins_num, lease_flag, lease_setting_nm, lease_type_desc, high_limit_flag, rated_gross_win_amt, rated_theo_win_amt, rated_net_win_amt, rated_comp_earned_amt, rated_pt_earn_amt, player_cnt, rated_trip_cnt
)
SELECT*FROM Temp2;

how to add sqlagentuserrole to user in sql server

USE msdb;
GO

CREATEUSER [your_user_name] FOR LOGIN [your_login_name];
GO

EXEC sp_addrolemember 'SQLAgentUserRole', 'your_user_name';
GO

Debug School

SQL Techniques for Advanced Data Analysis

Full code

how to add sqlagentuserrole to user in sql server

Top comments (0)