Untitled

--------------------------------------------------------------------------------------------------------------------------------------

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
--;