Untitled
unknown
sql
3 years ago
7.3 kB
3
Indexable
-------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE TEMPORARY TABLE DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS AS SELECT distinct lower(m.EMAIL) as member_email FROM "DEV_WAREHOUSE_DB"."DWH"."RPT_CS_MT2_PARTICIPANTS_DEDUPED" m Where m.TACurrent = 'Y' -- MemberMembershipStatus = 'Active' - and m.EMAIL like '%_@_%.%'; ------- II - Select all CH bookers that are not current members AND who have not played in last 24 months since start of year -------------------------------------------------------------------------------------------------------------------------------------- --SET StartDate = current_Date-7; -- Make it first of Jan --SET EndDate = current_date-1 ; --(select Convert(date, getdate())) ; SET StartDate = date(2022-02-05); -- Make it first of Jan SET EndDate = date(2022-02-11) ; --(select Convert(date, getdate())) ; -- report weekly CREATE OR REPLACE TEMPORARY TABLE DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS1 AS select distinct Contact_ID, Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_COURT_HIRE_CASUALBOOKINGS" where BookingSessionDate BETWEEN $StartDate AND $EndDate EXCEPT select Contact_ID, Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_COURT_HIRE_CASUALBOOKINGS" where BookingSessionDate >= (Select DATEADD(year, -2, CAST($StartDate as date))) and BookingSessionDate < $StartDate ; -- Remove all OCS emails from the above -- drop table #newCH_Participants CREATE OR REPLACE TEMPORARY TABLE DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS AS select distinct ch.Contact_ID, ch.Contact_EmailAddress from DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS1 ch where ch.Contact_EmailAddress is not null AND ch.Contact_EmailAddress not in ( select distinct Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_OCS_SESSION_REGISTRATIONS" where Contact_EmailAddress like '%_@_%.__%' ); ------------********************************************************************************************************************************************------------ -- New CH Particpants = Those who booked a tennis court who are NOT members (and remove emails if they booked OCS as they will be counted there) /** select count(distinct Contact_EmailAddress) as NewCHParticpants FROM DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS where Contact_EmailAddress not in ( select member_email from DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS ); **/ ------------********************************************************************************************************************************************------------ -- find split by state -- NEW_PARTICIPANTS_COURT_HIRE /** insert into "DEV_WAREHOUSE_DB"."DWH"."RPT_NEW_PARTICIPANTS_COURT_HIRE_WEEKLY" Select $EndDate as Date,State, Count(distinct Contact_EmailAddress) as NewCHParticanpts FROM( Select r.Contact_EmailAddress, MAX(r.State) as State FROM "DEV_WAREHOUSE_DB"."DWH"."VW_COURT_HIRE_CASUALBOOKINGS" r inner join DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS n on n.Contact_EmailAddress = r.Contact_EmailAddress Where r.State IS NOT NULL AND n.Contact_EmailAddress not in ( select member_email from DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS ) GROUP BY r.Contact_EmailAddress ) A GROUP BY State ORDER BY Count(distinct Contact_EmailAddress) desc ; */ -- select top 100 * from #newCH_Participants ------- III - Select all curent FY OCS players that are not current members [[[[and not CH participants]]]] -------------------------------------------------------------------------------------------------------------------------------------- --drop table #new_OCS_session_registrants; SET StartDate = date(2022-02-05); -- Make it first of Jan SET EndDate = date(2022-02-11) ; --(select Convert(date, getdate())) ; --select o.Registrant_ID, o.Registrant_ContactID, o.Registrant_GuardianContactID, Contact_ID, ISNULL(o.Contact_EmailAddress, 'XX@XX.XX') as Contact_EmailAddress, o.Contact_FirstName, o.Contact_LastName, RegistrantType CREATE OR REPLACE TEMPORARY TABLE DEV_WAREHOUSE_DB.DWH.NEW_OCS_SESSION_REGISTRANTS AS (Select Contact_ID, IFNULL(o.Contact_EmailAddress, 'XX@XX.XX') as Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_OCS_SESSION_REGISTRATIONS" o where o.Contact_EmailAddress not in ( select Contact_EmailAddress from DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS c) AND SessionDate BETWEEN $StartDate AND $EndDate AND Contact_ID IS NOT NULL union select Contact_ID, IFNULL(o.Contact_EmailAddress, 'XX@XX.XX') as Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_OCS_SESSION_REGISTRATIONS" o where Contact_ID IS NOT NULL AND o.Contact_EmailAddress is null and SessionDate BETWEEN $StartDate AND $EndDate) EXCEPT Select Contact_ID, IFNULL(o.Contact_EmailAddress, 'XX@XX.XX') as Contact_EmailAddress from "DEV_WAREHOUSE_DB"."DWH"."VW_OCS_SESSION_REGISTRATIONS" o where SessionDate >= (Select DATEADD(year, -2, CAST($StartDate as date))) AND SessionDate < $StartDate ; -- NEW OCS PARTICIPANTS ------------********************************************************************************************************************************************------------ /** Select count(distinct Contact_ID) as OCS_NEW_PARTICIPANTS FROM ( select distinct Contact_ID, Contact_EmailAddress --, Contact_FirstName, Contact_LastName --, Contact_EmailAddress--, User_EmailAddress, User_FirstName, User_LastName, Registrant_GuardianContactID, Registrant_ContactID from DEV_WAREHOUSE_DB.DWH.NEW_OCS_SESSION_REGISTRANTS where Contact_EmailAddress not in ( select member_email from DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS) ) A ; **/ ------------********************************************************************************************************************************************------------ -- find split by state -- NEW_PARTICIPANTS_OPEN_COURT_SESSION : OCS --insert into "DEV_WAREHOUSE_DB"."DWH"."RPT_NEW_PARTICIPANTS_OCS_WEEKLY" --Select $EndDate as date, State, Count(distinct Contact_EmailAddress) as NewOCSParticanpts --FROM( insert into "DEV_WAREHOUSE_DB"."DWH"."RPT_NEW_PARTICIPANTS_OCS_SINGLE" Select $EndDate as date, State, Count(distinct c.email) as Participants from (select distinct A.email as email, A.State as State from (Select r.Contact_EmailAddress as email, MAX(r.State) as State FROM "DEV_WAREHOUSE_DB"."DWH"."VW_OCS_SESSION_REGISTRATIONS" r inner join DEV_WAREHOUSE_DB.DWH.NEW_OCS_SESSION_REGISTRANTS n on n.Contact_EmailAddress = r.Contact_EmailAddress Where r.State IS NOT NULL AND n.Contact_EmailAddress not in ( select member_email from DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS ) GROUP BY r.Contact_EmailAddress ) A union select distinct B.email as email, B.State as State from (Select r.Contact_EmailAddress as email, MAX(r.State) as State FROM "DEV_WAREHOUSE_DB"."DWH"."VW_COURT_HIRE_CASUALBOOKINGS" r inner join DEV_WAREHOUSE_DB.DWH.NEWCH_PARTICIPANTS n on n.Contact_EmailAddress = r.Contact_EmailAddress Where r.State IS NOT NULL AND n.Contact_EmailAddress not in ( select member_email from DEV_WAREHOUSE_DB.DWH.MEMBER_EMAILS ) GROUP BY r.Contact_EmailAddress ) B ) C GROUP BY C.State --GROUP BY State --ORDER BY Count(distinct Contact_EmailAddress) desc --;
Editor is loading...