SQLize Online / PHPize Online  /  SQLtest Online

SELECT DISTINCT dly_mbrs_dt as LastUpdateDateTime, j.dly_trans_cd as EventCode, c.mbrs_ky AS MembershipKey, a.mbr_ky AS MemberKey, c.mbrs_id AS MembershipNumber, a.mbr_assoc_id AS AssociateNumber, ISNULL(a.mbr_fst_nm,'') AS FirstName, ISNULL(a.mbr_lst_nm,'') AS LastName, a.mbr_sts_cd AS MemberStatus, ISNULL(( SELECT TOP 1 ride_comp_cd FROM RIDER WHERE mbrs_ky = c.mbrs_ky AND ride_CANC_DT IS NULL ORDER BY (DENSE_RANK() OVER (PARTITION BY MBRS_KY ORDER BY CASE WHEN ride_comp_cd= 'BS' THEN 1 WHEN ride_comp_cd= 'PL' THEN 2 WHEN ride_comp_cd= 'RP' THEN 3 WHEN ride_comp_cd= 'MO' THEN 4 WHEN ride_comp_cd= 'EP' THEN 5 WHEN ride_comp_cd= 'EV' THEN 5 END DESC)) ),'BS') AS MembershipType, CASE WHEN FORMAT(COALESCE(MBRS_EXPIR_DT,''), 'yyyy-MM-dd')='1900-01-01' THEN '' ELSE FORMAT(MBRS_EXPIR_DT, 'yyyy-MM-dd') END ExpirationDate, --cast(a.mbr_canc_dt as date) AS CancelDate, CASE WHEN FORMAT(COALESCE(MBR_CANC_DT,''), 'yyyy-MM-dd')='1900-01-01' THEN '' ELSE FORMAT(MBR_CANC_DT, 'yyyy-MM-dd') END CancelDate, CASE WHEN FORMAT(COALESCE(mbr_eff_dt,''), 'yyyy-MM-dd')='1900-01-01' THEN '' ELSE FORMAT(mbr_eff_dt, 'yyyy-MM-dd') END EffectiveDate, NULL AS PhoneNumbers, NULL AS EmailAddresses, ISNULL(e.madr_bsc_ad ,'') AS AddressLine1, ISNULL(e.madr_supl_ad ,'') AS AddressLine2, e.madr_cty_nm AS City, e.madr_st_prov_cd AS State, e.madr_zip_cd AS ZipCode, Year(a.mbr_jn_aaa_dt) AS JoinAAAYear, Year(a.mbr_jn_clb_dt) AS JoinClubYear, a.mbr_relat_cd AS RelationshipCode, a.mbr_slct_cd AS SolicitationCode, a.mbr_typ_cd AS MemberTypeCode, CASE WHEN j.dly_trans_cd ='ADDASRDR' THEN 'Associate Rider Upgraded' WHEN j.dly_trans_cd ='ADDPRRDR' THEN 'Primary Rider Upgraded' WHEN j.dly_trans_cd ='CNLASSOC' THEN 'Cancel Associate' WHEN j.dly_trans_cd ='CNLASRDR' THEN 'Associate Rider Downgraded' WHEN j.dly_trans_cd ='CNLNAASS' THEN 'Cancel Associate' WHEN j.dly_trans_cd ='CNLNAASR' THEN 'Membership Downgraded' WHEN j.dly_trans_cd ='CNLNAPRY' THEN 'Membership Cancelled' WHEN j.dly_trans_cd ='CNLNAPRR' THEN 'Membership Downgraded' WHEN j.dly_trans_cd ='CNLPRMRY' THEN 'Membership Cancelled' WHEN j.dly_trans_cd ='CNLPRRDR' THEN 'Primary Rider Downgraded' WHEN j.dly_trans_cd ='RENASSOC' THEN 'Membership Renewed' WHEN j.dly_trans_cd ='RENASRDR' THEN 'Membership Rider Renewed' WHEN j.dly_trans_cd ='RENPRMRY' THEN 'Membership Renewed' WHEN j.dly_trans_cd ='RENPRRDR' THEN 'Membership Rider Renewed' WHEN j.dly_trans_cd ='RVPAYASS' THEN 'Reverse Payment' WHEN j.dly_trans_cd ='RVPAYARD' THEN 'Reverse Rider Payment' WHEN j.dly_trans_cd ='RVPAYPRY' THEN 'Reverse Payment' WHEN j.dly_trans_cd ='RVPAYPRD' THEN 'Reverse Rider Payment' WHEN j.dly_trans_cd ='SWTASSOC' THEN 'Switch Associate' WHEN j.dly_trans_cd ='SWTASRDR' THEN 'Switch Rider' WHEN j.dly_trans_cd ='SWTPRMRY' THEN 'Switch Primary' WHEN j.dly_trans_cd ='SWTPRRDR' THEN 'Switch Rider' END AS EventType, CONCAT(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,15), CAST( (CASE WHEN ( CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT) *2 >9 THEN 1+(CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT)*2 END +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),2,1) AS INT) + (CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT) *2 >9 THEN 1+(CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),4,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),6,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),8,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),10,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),12,1) AS INT) + (CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),14,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT)*2 END))%10= 0 THEN 0 ELSE 10 - (CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),1,1) AS INT)*2 END +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),2,1) AS INT) + (CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),3,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),4,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),5,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),6,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),7,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),8,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),9,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),10,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),11,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),12,1) AS INT) + (CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),13,1) AS INT)*2 END ) +CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),14,1) AS INT) +(CASE WHEN CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT) *2 >9 THEN 1+ (CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT)*2%10) ELSE CAST(SUBSTRING(CONCAT('429005',TRIM(a.MBRS_ID),TRIM(a.mbr_assoc_id)),15,1) AS INT)*2 END))%10 END ) AS VARCHAR)) AS MemberNumber FROM daily_event j JOIN mbr a ON a.mbrs_id = j.dly_mbrs_id JOIN mbrship c ON a.mbrs_ky = c.mbrs_ky JOIN mbrship_address e ON c.mbrs_ky = e.mbrs_ky JOIN rider r ON a.mbrs_ky = r.mbrs_ky WHERE a.mbrs_id+a.mbr_assoc_id = j.dly_mbrs_id+j.dly_assoc_id AND e.madr_typ_cd = 'H' AND j.dly_trans_cd IN ('ADDASRDR' ,'ADDPRRDR' ,'CNLASSOC','CNLASRDR','CNLNAASS' ,'CNLNAASR' ,'CNLNAPRY','CNLNAPRR','CNLPRMRY' ,'CNLPRRDR','RENASSOC' , 'RENASRDR' ,'RENPRMRY' ,'RENPRRDR' ,'RVPAYASS' ,'RVPAYARD' ,'RVPAYPRY','RVPAYPRD','SWTASSOC','SWTASRDR','SWTPRMRY','SWTPRRDR') AND --dly_mbrs_dt >= CAST('2023-04-19 11:37:34.814182' as DATETIME2) AND dly_mbrs_dt <= CAST('2023-04-20 12:39:12.151356' as DATETIME2); dly_mbrs_dt >= CAST('{0}' as DATETIME2) AND dly_mbrs_dt <= CAST('{1}' as DATETIME2);