SQLize | PHPize | SQLtest

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, ISNULL((SELECT STRING_AGG(mtel_nr, ', ') AS PhoneNumbers FROM mbrship_phone WHERE mbr_ky = a.mbr_ky),'') AS PhoneNumbers, ISNULL((SELECT STRING_AGG(email_addr, ', ') AS EmailAddresses FROM mbr_email WHERE mbr_ky = a.mbr_ky),'') 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 ='ADDASSOC' THEN 'Associate Member Added' WHEN j.dly_trans_cd ='ADDPRMRY' THEN 'Primary Member Added' WHEN j.dly_trans_cd ='REIASSOC' THEN 'Associate Reinstate' WHEN j.dly_trans_cd ='REIASRDR' THEN 'Associate Rider Reinstate' WHEN j.dly_trans_cd ='REIPRMRY' THEN 'Primary Reinstate' WHEN j.dly_trans_cd ='REIPRRDR' THEN 'Primary Rider Reinstate' 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 ('ADDASSOC' ,'ADDPRMRY','REIASSOC' ,'REIASRDR' ,'REIPRMRY' ,'REIPRRDR') and --dly_mbrs_dt >= CAST('2023-04-24 11:37:34.814182' as DATETIME2) AND dly_mbrs_dt <= CAST('2023-04-25 12:39:12.151356' as DATETIME2); dly_mbrs_dt >= CAST('{0}' as DATETIME2) AND dly_mbrs_dt <= CAST('{1}' as DATETIME2);