Listed below are a variety of SQL examples written for production environments. All of these examples are automated and used to provide daily reporting for a variety of clients. Many more examples are available on my Github account. Reporting and visualizations were generated to provide daily updates for clients. The example listed directly below was for a major beverage client
/****** Script for SelectTopNRows command from SSMS ******/
--==================================================================================================================================================================================================================
--
--
-- PLANNED SPEND TABLE
-- UPDATED: Nov.15.2018
--
--=================================================================================================================================================================================================================
USE [TorontoMySQLRandomClientData]
DROP TABLE MattPlannedSpendTable;
select * into MattPlannedSpendTable FROM(
SELECT
EVENTS.id 'Counts Distinct Event ID'
,BRAND_SAMPLE.name 'Events Table brand Name'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Table Event Funding'
,EVENTS.[end_date] 'Event Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,TERRITORY.region 'TerritoriesTable Region'
,TERRITORY.zip 'TerritoriesTable Zip'
,EVENTS.tier 'Events Table Tier'
,EVENTS.target_consumer_age_LDA_24 'Events Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Table Other'
,EVENTS.target_consumer_gender_m 'Events Table Male'
,EVENTS.target_consumer_gender_f 'Events Table Female'
,EVENTS.total_samples 'Events Table sample Number of Samples Distributed'
,COALESCE(CAST(brand_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_activation'
,COALESCE(CAST(brand_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_sponsor'
,COALESCE(CAST(brand_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_total'
,COALESCE(CAST(local_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_sponsor'
,COALESCE(CAST(local_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_activation'
,COALESCE(CAST(local_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_total'
,COALESCE(CAST(multi_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_activation'
,COALESCE(CAST(multi_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_sponsor'
,COALESCE(CAST(multi_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_total'
,COALESCE(CAST(sport_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table sport_cost_sponsor'
,COALESCE(CAST(sport_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Table sport_cost_total'
FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] event_sampling
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS ON (EVENTS.id = EVENT_SAMPLING.event_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=EVENT_SAMPLING.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null
UNION ALL
SELECT
EVENTS.id 'Counts Distinct Event ID'
,BRAND_SAMPLE.name 'Events Table brand Name'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Table Event Funding'
,EVENTS.[end_date] 'Event Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,TERRITORY.region 'TerritoriesTable Region'
,TERRITORY.zip 'TerritoriesTable Zip'
,EVENTS.tier 'Events Table Tier'
,EVENTS.target_consumer_age_LDA_24 'Events Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Table Other'
,EVENTS.target_consumer_gender_m 'Events Table Male'
,EVENTS.target_consumer_gender_f 'Events Table Female'
,EVENTS.total_samples 'Events Table sample Number of Samples Distributed'
,COALESCE(CAST(brand_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_activation'
,COALESCE(CAST(brand_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_sponsor'
,COALESCE(CAST(brand_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table brand_cost_total'
,COALESCE(CAST(local_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_sponsor'
,COALESCE(CAST(local_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_activation'
,COALESCE(CAST(local_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table local_cost_total'
,COALESCE(CAST(multi_cost_activation as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_activation'
,COALESCE(CAST(multi_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_sponsor'
,COALESCE(CAST(multi_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table multi_cost_total'
,COALESCE(CAST(sport_cost_sponsor as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table sport_cost_sponsor'
,COALESCE(CAST(sport_cost_total as INT) , 0)/COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Table sport_cost_total'
FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] BRAND_EVENT
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS ON (EVENTS.id = BRAND_EVENT.event_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=BRAND_EVENT.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.id NOT IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] )
AND EVENTS.deleted_at is null
UNION ALL
SELECT
EVENTS.id 'Counts Distinct Event Recaps ID'
,NULL 'Events Table brand Name'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Table Event Funding'
,EVENTS.[end_date] 'Event Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,TERRITORY.region 'TerritoriesTable Region'
,TERRITORY.zip 'TerritoriesTable Zip'
,EVENTS.tier 'Events Table Tier'
,EVENTS.target_consumer_age_LDA_24 'Events Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Table Other'
,EVENTS.target_consumer_gender_m 'Events Table Male'
,EVENTS.target_consumer_gender_f 'Events Table Female'
,EVENTS.total_samples 'Events Table sample Number of Samples Distributed'
,COALESCE(CAST(brand_cost_activation as INT) , 0) 'Events Table brand_cost_activation'
,COALESCE(CAST(brand_cost_sponsor as INT) , 0) 'Events Table brand_cost_sponsor'
,COALESCE(CAST(brand_cost_total as INT) , 0) 'Events Table brand_cost_total'
,COALESCE(CAST(local_cost_sponsor as INT) , 0) 'Events Table local_cost_sponsor'
,COALESCE(CAST(local_cost_activation as INT) , 0) 'Events Table local_cost_activation'
,COALESCE(CAST(local_cost_total as INT) , 0) 'Events Table local_cost_total'
,COALESCE(CAST(multi_cost_activation as INT) , 0) 'Events Table multi_cost_activation'
,COALESCE(CAST(multi_cost_sponsor as INT) , 0) 'Events Table multi_cost_sponsor'
,COALESCE(CAST(multi_cost_total as INT) , 0) 'Events Table multi_cost_total'
,COALESCE(CAST(sport_cost_sponsor as INT) , 0) 'Events Table sport_cost_sponsor'
,COALESCE(CAST(sport_cost_total as INT) , 0) 'Events Table sport_cost_total'
FROM [TorontoMySQLRandomClientData].[dbo].[events] EVENTS
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.id NOT IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] )
AND EVENTS.id NOT IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[brand_event] )
AND EVENTS.deleted_at is null
) as tmp;
--==================================================================================================================================================================================================================
--
--
-- EVENT RECAPS TABLE
-- UPDATED: Nov.15.2018
--
--=================================================================================================================================================================================================================
DROP TABLE MattEventRecapTable;
-- drop table MattEventRecapTable;
select * into MattEventRecapTable from(
SELECT
EVENT_RECAPS.id 'Counts Distinct Event Recaps ID'
,EVENT_RECAPS.account_state 'Events Recaps Table Account State' --changed to recap table account_state
,EVENT_RECAPS.event_type 'Events Table Event Type'
,EVENT_RECAPS.event_funding 'Event Recaps Table Event Funding'
,EVENT_RECAPS.consumers_sampled 'Event Recaps Table Consumers Sampled'
,EVENT_RECAPS.consumers_engaged_in_experience 'Event Recaps Consumers Engaged In Experience'
,EVENT_RECAPS.consumers_reached 'Event Recaps Consumers Reached'
,EVENT_RECAPS.[activation_elements] 'Event Recaps Activation Elements/Cannot Find Activation Status Field' --Spoke on this field I will let you update and add whatver you bring in from Toronto Mysql
,EVENT_RECAPS.[event_date] 'Event Recaps Table Event Date'
,EVENT_RECAPS.territory 'TerritoriesTable Name' -- should be events_recap.territory from mysql
--,EVENT_RECAPS.region 'TerritoriesTable Region' -- should be events_recap.region from mysql (commented out until it is brought in to the event_recaps table)
--,EVENT_RECAP_SAMPLES.sample_brand 'Event Recap Sample Brand'
,CASE WHEN EVENT_RECAP_SAMPLES.sample_brand IS NOT NULL -- AND EVENT_RECAP_SAMPLES.sample_brand != 'MULTIPLE BRANDS' --Don't think we need this extra filter anymore -TM
THEN EVENT_RECAP_SAMPLES.sample_brand
ELSE NULL
END AS 'Event Recap Sample Brand'
--,EVENT_RECAP_SAMPLES.number_samples_distributed 'Event Recap Samples Table Number Samples'
,CASE WHEN EVENT_RECAP_SAMPLES.number_samples_distributed IS NOT NULL -- ADDED CASE STATEMENT TO FIGURE OUT WHERE TO GET SAMPLES DISTRIBUTED COUNTS FROM -TM
THEN COALESCE(EVENT_RECAP_SAMPLES.number_samples_distributed,0)
ELSE 0
END AS 'Event Recap Samples Table Number Samples'
--,CONVERT(INT, EVENT_RECAP_COMPETITIVES.consumer_engagement_with_competitor) 'Event Recap Competitives Table Consumer Engagement'
,EVENT_RECAPS.account_state 'Events Table State' --changed to recap table account_state
,EVENT_RECAPS.percentage_loa_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENT_RECAPS.percentage_25_29 'Events Recaps Table 25-29'
,EVENT_RECAPS.percentage_30_34 'Events Recaps Table 30-34'
,EVENT_RECAPS.percentage_35_plus 'Events Recaps Table 35 Plus'
,EVENT_RECAPS.percentage_caucasian 'Events Recaps Table Caucasian'
,EVENT_RECAPS.percentage_hispanic 'Events Recaps Table Hispanic'
,EVENT_RECAPS.percentage_african_american 'Events Recaps Table African American'
,EVENT_RECAPS.percentage_asian 'Events Recaps Table Asian'
,EVENT_RECAPS.percentage_other 'Events Recaps Table Other'
,EVENT_RECAPS.percentage_male 'Events Recaps Table Male'
,EVENT_RECAPS.percentage_female 'Events Recaps Table Female'
,[EVENT_RECAPS].activation_status --NEED THIS COLUMN ADDED
--INTO [TorontoMySQLRandomClientData].[dbo].[MattEventRecapTable]
FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] EVENT_RECAP_SAMPLES
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS ON (EVENT_RECAPS.id=EVENT_RECAP_SAMPLES.event_recap_id )
WHERE [EVENT_RECAPS].activation_status ='Activated' --Need to add this to pull only Activated
and [EVENT_RECAPS].deleted_at is null --Need to add this to pull only nondeleted - AS
UNION ALL
SELECT
EVENT_RECAPS.id 'Counts Distinct Event Recaps ID'
,EVENT_RECAPS.account_state 'Events Recaps Table Account State' --changed to recap table account_state
,EVENT_RECAPS.event_type 'Events Table Event Type'
,EVENT_RECAPS.event_funding 'Event Recaps Table Event Funding'
,EVENT_RECAPS.consumers_sampled 'Event Recaps Table Consumers Sampled'
,EVENT_RECAPS.consumers_engaged_in_experience 'Event Recaps Consumers Engaged In Experience'
,EVENT_RECAPS.consumers_reached 'Event Recaps Consumers Reached'
,EVENT_RECAPS.[activation_elements] 'Event Recaps Activation Elements/Cannot Find Activation Status Field' --Spoke on this field I will let you update and add whatver you bring in from Toronto Mysql
,EVENT_RECAPS.[event_date] 'Event Recaps Table Event Date'
,EVENT_RECAPS.territory 'TerritoriesTable Name' -- should be events_recap.territory from mysql
--,EVENT_RECAPS.region 'TerritoriesTable Region' -- should be events_recap.region from mysql (commented out until it is brought in to the event_recaps table)
--,EVENT_RECAP_SAMPLES.sample_brand 'Event Recap Sample Brand'
,CASE WHEN EVENT_RECAPS.brand IS NOT NULL -- AND EVENT_RECAP_SAMPLES.sample_brand != 'MULTIPLE BRANDS' --Don't think we need this extra filter anymore -TM
THEN EVENT_RECAPS.brand
ELSE NULL
END AS 'Event Recap Sample Brand'
--,EVENT_RECAP_SAMPLES.number_samples_distributed 'Event Recap Samples Table Number Samples'
,CASE WHEN EVENT_RECAPS.samples_distributed_total IS NOT NULL -- ADDED CASE STATEMENT TO FIGURE OUT WHERE TO GET SAMPLES DISTRIBUTED COUNTS FROM -TM
THEN COALESCE(EVENT_RECAPS.samples_distributed_total,0)
ELSE 0
END AS 'Event Recap Samples Table Number Samples'
--,CONVERT(INT, EVENT_RECAP_COMPETITIVES.consumer_engagement_with_competitor) 'Event Recap Competitives Table Consumer Engagement'
,EVENT_RECAPS.account_state 'Events Table State' --changed to recap table account_state
,EVENT_RECAPS.percentage_loa_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENT_RECAPS.percentage_25_29 'Events Recaps Table 25-29'
,EVENT_RECAPS.percentage_30_34 'Events Recaps Table 30-34'
,EVENT_RECAPS.percentage_35_plus 'Events Recaps Table 35 Plus'
,EVENT_RECAPS.percentage_caucasian 'Events Recaps Table Caucasian'
,EVENT_RECAPS.percentage_hispanic 'Events Recaps Table Hispanic'
,EVENT_RECAPS.percentage_african_american 'Events Recaps Table African American'
,EVENT_RECAPS.percentage_asian 'Events Recaps Table Asian'
,EVENT_RECAPS.percentage_other 'Events Recaps Table Other'
,EVENT_RECAPS.percentage_male 'Events Recaps Table Male'
,EVENT_RECAPS.percentage_female 'Events Recaps Table Female'
,[EVENT_RECAPS].activation_status --NEED THIS COLUMN ADDED
--INTO [TorontoMySQLRandomClientData].[dbo].[MattEventRecapTable]
FROM [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS
WHERE [EVENT_RECAPS].activation_status ='Activated'
and EVENT_RECAPS.deleted_at is null --Need to add this to pull only nondeleted - AS
AND EVENT_RECAPS.id NOT IN (SELECT DISTINCT event_recap_id FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] Where [TorontoMySQLRandomClientData].[dbo].[event_recap_samples].event_recap_id is not null)
) as tmp;
-- select * from MattEventRecapTable
--==================================================================================================================================================================================================================
--
--
-- CONSUMERS TABLE
-- UPDATED: Nov.15.2018
--
--=================================================================================================================================================================================================================
DROP TABLE MattConsumersTable;
--FIRST WE GET ALL THE DATA WHERE THE RECAPS HAVE RECAP_SAMPLE DATA
WITH T AS
(
SELECT
EVENT_RECAPS.id 'Counts Distinct Event Recaps ID'
,EVENT_RECAPS.event_type 'Events Table Event Type' --Modified to be recaps instead of events
,EVENT_RECAP_SAMPLES.sample_brand AS 'Event Recap Sample Brand'
,EVENT_RECAPS.account_state 'Events Table State' --changed to recap table account_state
,EVENT_RECAPS.event_funding 'Event Recaps Table Event Funding'
,EVENT_RECAPS.[event_date] 'Event Recaps Table Event Date'
,EVENT_RECAPS.territory 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,EVENT_RECAPS.percentage_loa_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENT_RECAPS.percentage_25_29 'Events Recaps Table 25-29'
,EVENT_RECAPS.percentage_30_34 'Events Recaps Table 30-34'
,EVENT_RECAPS.percentage_35_plus 'Events Recaps Table 35 Plus'
,EVENT_RECAPS.percentage_caucasian 'Events Recaps Table Caucasian'
,EVENT_RECAPS.percentage_hispanic 'Events Recaps Table Hispanic'
,EVENT_RECAPS.percentage_african_american 'Events Recaps Table African American'
,EVENT_RECAPS.percentage_asian 'Events Recaps Table Asian'
,EVENT_RECAPS.percentage_other 'Events Recaps Table Other'
,EVENT_RECAPS.percentage_male 'Events Recaps Table Male'
,EVENT_RECAPS.percentage_female 'Events Recaps Table Female'
--,EVENT_RECAPS.samples_distributed_total 'Event Recap Sample Number of Samples Distributed' --Changed to get correct #s from event_recap_samples table first -TM
,ISNULL(EVENT_RECAP_SAMPLES.number_samples_distributed,0) AS 'Event Recap Sample Number of Samples Distributed'
,EVENT_RECAPS.[activation_status] 'Events Table Approval Status' --We will use [Events Table Approval Status] as status field for now unless you want to add new [Event Status]
--INTO [TorontoMySQLRandomClientData].[dbo].[MattBrandTable]
FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] EVENT_RECAP_SAMPLES
--Changed from INNER to LEFT \/ -TM
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS ON (EVENT_RECAP_SAMPLES.event_recap_id=EVENT_RECAPS.id)
WHERE [EVENT_RECAPS].activation_status='Activated' --Need to add this to pull only Activated
AND CAST(EVENT_RECAPS.event_date AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND [EVENT_RECAPS].deleted_at is null --Need to add this to pull only nondeleted - AS
AND [EVENT_RECAPS].id IN (select event_recap_id from [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] where event_recap_id=[EVENT_RECAPS].id)
UNION ALL
--NEXT WE GET ALL THE DATA WHERE THE RECAPS HAVE NO RECAP_SAMPLE DATA BUT HAVE A RECAP RECORD
SELECT
EVENT_RECAPS.id 'Counts Distinct Event Recaps ID'
,EVENT_RECAPS.event_type 'Events Table Event Type' --Modified to be recaps instead of events
,ISNULL(EVENT_RECAPS.brand,NULL) AS 'Event Recap Sample Brand'
,EVENT_RECAPS.account_state 'Events Table State' --changed to recap table account_state
,EVENT_RECAPS.event_funding 'Event Recaps Table Event Funding'
,EVENT_RECAPS.[event_date] 'Event Recaps Table Event Date'
,EVENT_RECAPS.territory 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,EVENT_RECAPS.percentage_loa_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENT_RECAPS.percentage_25_29 'Events Recaps Table 25-29'
,EVENT_RECAPS.percentage_30_34 'Events Recaps Table 30-34'
,EVENT_RECAPS.percentage_35_plus 'Events Recaps Table 35 Plus'
,EVENT_RECAPS.percentage_caucasian 'Events Recaps Table Caucasian'
,EVENT_RECAPS.percentage_hispanic 'Events Recaps Table Hispanic'
,EVENT_RECAPS.percentage_african_american 'Events Recaps Table African American'
,EVENT_RECAPS.percentage_asian 'Events Recaps Table Asian'
,EVENT_RECAPS.percentage_other 'Events Recaps Table Other'
,EVENT_RECAPS.percentage_male 'Events Recaps Table Male'
,EVENT_RECAPS.percentage_female 'Events Recaps Table Female'
--,EVENT_RECAPS.samples_distributed_total 'Event Recap Sample Number of Samples Distributed' --Changed to get correct #s from event_recap_samples table first -TM
,ISNULL(EVENT_RECAPS.samples_distributed_total,0) AS 'Event Recap Sample Number of Samples Distributed'
,EVENT_RECAPS.[activation_status] 'Events Table Approval Status' --We will use [Events Table Approval Status] as status field for now unless you want to add new [Event Status]
--INTO [TorontoMySQLRandomClientData].[dbo].[MattBrandTable]
FROM [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS
--Changed from INNER to LEFT \/ -TM
WHERE [EVENT_RECAPS].activation_status='Activated' --Need to add this to pull only Activated
AND CAST(EVENT_RECAPS.event_date AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND [EVENT_RECAPS].deleted_at is null --Need to add this to pull only nondeleted - AS
AND [EVENT_RECAPS].id NOT IN (select event_recap_id from event_recap_samples where event_recap_id=[EVENT_RECAPS].id)
-- PLANNING DATA BELOW HERE --
UNION ALL
--NOW WE GET ALL THE RECORDS WHERE THE EVENTS HAVE SAMPLING DATA RELATIONAL DATA
SELECT
EVENTS.id 'Counts Distinct Event Recaps ID'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,BRAND_SAMPLE.name AS 'Event Recap Sample Brand'
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Recaps Table Event Funding'
,EVENTS.[end_date] 'Event Recaps Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,EVENTS.target_consumer_age_LDA_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Recaps Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Recaps Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Recaps Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Recaps Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Recaps Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Recaps Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Recaps Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Recaps Table Other'
,EVENTS.target_consumer_gender_m 'Events Recaps Table Male'
,EVENTS.target_consumer_gender_f 'Events Recaps Table Female'
,ISNULL(EVENT_SAMPLING.num_samples,0) 'Event Recap Sample Number of Samples Distributed'
,'Planned' 'Events Table Approval Status' --We will use [Events Table Approval Status] as status field for now unless you want to add new [Event Status]
--Send Planned if where clause see approval_status=1 and end_date after today which means event isn't over
FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] EVENT_SAMPLING -- OR EVENT_SAMPLING.brand_id = BRAND_SAMPLE.id) OR EVENT_SAMPLING.brand_id = EVENT_BRANDS.brand_id)
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS ON (EVENT_SAMPLING.event_id = EVENTS.id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=event_sampling.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null --ADDED LINE -TM
AND EVENTS.id in (select event_id from event_sampling_strategies where event_id=EVENTS.id) --ADDED LINE -TM
UNION ALL
--NOW WE GET ALL THE RECORDS WHERE THE EVENTS HAVE NO SAMPLING DATA BUT DO HAVE BRAND/EVENT RELATIONAL DATA
SELECT
EVENTS.id 'Counts Distinct Event Recaps ID'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,BRAND_SAMPLE.name AS 'Event Recap Sample Brand'
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Recaps Table Event Funding'
,EVENTS.[end_date] 'Event Recaps Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,EVENTS.target_consumer_age_LDA_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Recaps Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Recaps Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Recaps Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Recaps Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Recaps Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Recaps Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Recaps Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Recaps Table Other'
,EVENTS.target_consumer_gender_m 'Events Recaps Table Male'
,EVENTS.target_consumer_gender_f 'Events Recaps Table Female'
,ISNULL(EVENTS.total_samples,0) 'Event Recap Sample Number of Samples Distributed' --THIS SHOULD ALWAYS BE ZERO WHEN NO BRAND SAMPLING RECORD EXISTS
,'Planned' 'Events Table Approval Status' --We will use [Events Table Approval Status] as status field for now unless you want to add new [Event Status]
--Send Planned if where clause see approval_status=1 and end_date after today which means event isn't over
FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] EVENT_BRANDS
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS on (EVENT_BRANDS.event_id=EVENTS.id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=EVENT_BRANDS.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null --ADDED LINE -TM
AND EVENTS.id NOT IN (select event_id from event_sampling_strategies where event_id=EVENTS.id) --ADDED LINE -TM
AND EVENTS.id IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[brand_event] where event_id=EVENTS.id) --ADDED LINE -TM
UNION ALL
--NOW WE GET ALL THE REST OF THE RECORDS WHERE THE EVENTS DON'T HAVE SAMPLING OR BRAND/EVENT RELATIONAL DATA
SELECT
EVENTS.id 'Counts Distinct Event Recaps ID'
,EVENTS.event_type 'Events Table Event Type' --1 --Modified to be recaps instead of events
,'Not Specified' AS 'Event Recap Sample Brand' --WON'T HAVE A VALUE AS THERE'S NO SAMPLING DATA OR BRAND/EVENT DATA
,EVENTS.state 'Events Table State' --changed to recap table account_state
,EVENTS.funding 'Event Recaps Table Event Funding'
,EVENTS.[end_date] 'Event Recaps Table Event Date'
,TERRITORY.name 'TerritoriesTable Name' -- should be events_recap.territory from mysql
,EVENTS.target_consumer_age_LDA_24 'Events Recaps Table LOA 24' --Modifed to be for all Event Recap percentages
,EVENTS.target_consumer_age_25_29 'Events Recaps Table 25-29'
,EVENTS.target_consumer_age_30_34 'Events Recaps Table 30-34'
,EVENTS.target_consumer_age_35p 'Events Recaps Table 35 Plus'
,EVENTS.target_consumer_ethnic_cau 'Events Recaps Table Caucasian'
,EVENTS.target_consumer_ethnic_his 'Events Recaps Table Hispanic'
,EVENTS.target_consumer_ethnic_afr 'Events Recaps Table African American'
,EVENTS.target_consumer_ethnic_asi 'Events Recaps Table Asian'
,EVENTS.target_consumer_ethnic_other 'Events Recaps Table Other'
,EVENTS.target_consumer_gender_m 'Events Recaps Table Male'
,EVENTS.target_consumer_gender_f 'Events Recaps Table Female'
,ISNULL(EVENTS.total_samples,0) 'Event Recap Sample Number of Samples Distributed' --THIS SHOULD ALWAYS BE ZERO WHEN NO BRAND SAMPLING RECORD EXISTS
,'Planned' 'Events Table Approval Status' --We will use [Events Table Approval Status] as status field for now unless you want to add new [Event Status]
--Send Planned if where clause see approval_status=1 and end_date after today which means event isn't over
FROM [TorontoMySQLRandomClientData].[dbo].[events] EVENTS
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1' --Need to add this to pull only Activated
AND CAST(EVENTS.[end_date] AS DATE) >= CAST(getdate() AS DATE)
AND CAST(EVENTS.[end_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null --ADDED LINE -TM
AND EVENTS.id NOT IN (select event_id from event_sampling_strategies where event_id=EVENTS.id) --ADDED LINE -TM
AND EVENTS.id NOT IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[brand_event] where event_id=EVENTS.id) --ADDED LINE -TM
)
SELECT * INTO MattConsumersTable FROM T;
--==================================================================================================================================================================================================================
--
--
-- BRAND AND STATE TABLE
-- UPDATED: Nov.16.2018
--
--=================================================================================================================================================================================================================
DROP TABLE MattBrandStateTable;
SELECT * INTO MattBrandStateTable FROM(
--FIRST WE GET ALL THE DATA WHERE THE RECAPS HAVE RECAP_SAMPLE DATA
SELECT
'eventrecap' + CAST(EVENT_RECAPS.id as varchar(16)) 'Event Distinct ID'
,EVENT_RECAPS.id 'Event ID'
,EVENT_RECAPS.event_type 'Event Type'
,EVENT_RECAP_SAMPLES.sample_brand 'Event Brand'
,EVENT_RECAPS.account_state 'Event State'
,EVENT_RECAPS.event_funding 'Event Funding'
,EVENT_RECAPS.[event_date] 'Event Date'
,EVENT_RECAPS.territory 'Event Territory Name'
,EVENT_RECAPS.tier 'Event Tier'
,EVENT_RECAPS.actual_attendance 'Event Attendance'
,(COALESCE(CAST(match_product_cost as FLOAT) , 0) + COALESCE(CAST(match_activation_cost as FLOAT) , 0) + COALESCE(CAST(proof_product_cost as FLOAT) , 0) + COALESCE(CAST(proof_activation_cost as FLOAT) , 0) + COALESCE(CAST(mktg_product_cost as FLOAT) , 0) + COALESCE(CAST(mktg_activation_cost as FLOAT) , 0))/COALESCE((SELECT COUNT([sample_brand]) FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] WHERE event_recap_id = EVENT_RECAP_SAMPLES.event_recap_id), 1) 'Event Activation Cost Total'
,(COALESCE(CAST(match_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(proof_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(mktg_sponsor_cost as FLOAT) , 0))/COALESCE((SELECT COUNT([sample_brand]) FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] WHERE event_recap_id = EVENT_RECAP_SAMPLES.event_recap_id), 1) 'Events Sponsor Cost Total'
,(COALESCE(CAST(match_product_cost as FLOAT) , 0) + COALESCE(CAST(match_activation_cost as FLOAT) , 0) + COALESCE(CAST(proof_product_cost as FLOAT) , 0) + COALESCE(CAST(proof_activation_cost as FLOAT) , 0) + COALESCE(CAST(mktg_product_cost as FLOAT) , 0) + COALESCE(CAST(mktg_activation_cost as FLOAT) , 0) + COALESCE(CAST(match_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(proof_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(mktg_sponsor_cost as FLOAT) , 0))/COALESCE((SELECT COUNT([sample_brand]) FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] WHERE event_recap_id = EVENT_RECAP_SAMPLES.event_recap_id), 1) 'Event Total Cost'
,ISNULL(EVENT_RECAPS.consumers_sampled,0) AS 'Event Consumers Sampled'
,ISNULL(EVENT_RECAPS.consumers_reached,0) AS 'Event Consumers Reached'
,EVENT_RECAPS.[activation_status] 'Event Status'
FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] EVENT_RECAP_SAMPLES
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS ON (EVENT_RECAP_SAMPLES.event_recap_id=EVENT_RECAPS.id)
WHERE [EVENT_RECAPS].activation_status='Activated'
AND CAST(EVENT_RECAPS.event_date AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND [EVENT_RECAPS].deleted_at is null --Need to add this to pull only nondeleted - AS
AND [EVENT_RECAPS].id IN (select event_recap_id from [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] where event_recap_id=[EVENT_RECAPS].id)
UNION ALL
--NEXT WE GET ALL THE DATA WHERE THE RECAPS HAVE NO RECAP_SAMPLE DATA BUT HAVE A RECAP RECORD
SELECT
'eventrecap' + CAST(EVENT_RECAPS.id as varchar(16)) 'Event Distinct ID'
,EVENT_RECAPS.id 'Event ID'
,EVENT_RECAPS.event_type 'Event Type'
,EVENT_RECAPS.brand AS 'Event Brand'
,EVENT_RECAPS.account_state 'Event State'
,EVENT_RECAPS.event_funding 'Event Funding'
,EVENT_RECAPS.[event_date] 'Event Date'
,EVENT_RECAPS.territory 'Event Territory Name'
,EVENT_RECAPS.tier 'Event Tier'
,EVENT_RECAPS.actual_attendance 'Event Attendance'
,(COALESCE(CAST(match_product_cost as FLOAT) , 0) + COALESCE(CAST(match_activation_cost as FLOAT) , 0) + COALESCE(CAST(proof_product_cost as FLOAT) , 0) + COALESCE(CAST(proof_activation_cost as FLOAT) , 0) + COALESCE(CAST(mktg_product_cost as FLOAT) , 0) + COALESCE(CAST(mktg_activation_cost as FLOAT) , 0)) 'Event Activation Cost Total'
,(COALESCE(CAST(match_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(proof_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(mktg_sponsor_cost as FLOAT) , 0)) 'Events Sponsor Cost Total'
,(COALESCE(CAST(match_product_cost as FLOAT) , 0) + COALESCE(CAST(match_activation_cost as FLOAT) , 0) + COALESCE(CAST(proof_product_cost as FLOAT) , 0) + COALESCE(CAST(proof_activation_cost as FLOAT) , 0) + COALESCE(CAST(mktg_product_cost as FLOAT) , 0) + COALESCE(CAST(mktg_activation_cost as FLOAT) , 0) + COALESCE(CAST(match_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(proof_sponsor_cost as FLOAT) , 0) + COALESCE(CAST(mktg_sponsor_cost as FLOAT) , 0)) 'Event Total Cost'
,ISNULL(EVENT_RECAPS.consumers_sampled,0) AS 'Event Consumers Sampled'
,ISNULL(EVENT_RECAPS.consumers_reached,0) AS 'Event Consumers Reached'
,EVENT_RECAPS.[activation_status] 'Event Status'
FROM [TorontoMySQLRandomClientData].[dbo].[event_recaps] EVENT_RECAPS
WHERE [EVENT_RECAPS].activation_status='Activated'
AND CAST(EVENT_RECAPS.[event_date] AS DATE) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND [EVENT_RECAPS].deleted_at is null --Need to add this to pull only nondeleted - AS
AND [EVENT_RECAPS].id NOT IN (select event_recap_id from event_recap_samples where event_recap_id=[EVENT_RECAPS].id)
UNION ALL
-- PLANNING DATA BELOW HERE --
--NOW WE GET ALL THE RECORDS WHERE THE EVENTS HAVE SAMPLING DATA RELATIONAL DATA
SELECT
'event' + CAST(EVENTS.id as varchar(16)) 'Event Distinct ID'
,EVENTS.id 'Event ID'
,EVENTS.event_type 'Event Type'
,BRAND_SAMPLE.name 'Event Brand'
,EVENTS.state 'Event State'
,EVENTS.funding 'Event Funding'
,CONVERT(DATE, EVENTS.end_date) 'Event Date'
,TERRITORY.name 'Event Territory Name'
,EVENTS.tier 'Event Tier'
,EVENTS.attendance 'Event Attendance'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Event Activation Cost Total'
,(COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Events Sponsor Cost Total'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0)
+ COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] WHERE event_id = EVENTS.id), 1) 'Event Total Cost'
,0 'Event Consumers Sampled' --Doesn't exist for Planned events
,0 'Event Consumers Reached' --Doesn't exist for Planned events
,'Planned' 'Event Status' --Set to planned as filters below only pull approved Planned events
FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] EVENT_SAMPLING -- OR EVENT_SAMPLING.brand_id = BRAND_SAMPLE.id) OR EVENT_SAMPLING.brand_id = EVENT_BRANDS.brand_id)
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS ON (EVENT_SAMPLING.event_id = EVENTS.id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=event_sampling.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1'
AND CONVERT(DATE, EVENTS.end_date)>getdate()
AND CONVERT(DATE, EVENTS.end_date) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null
AND EVENTS.id in (select event_id from event_sampling_strategies where event_id=EVENTS.id)
UNION ALL
--NOW WE GET ALL THE RECORDS WHERE THE EVENTS HAVE NO SAMPLING DATA BUT DO HAVE BRAND/EVENT RELATIONAL DATA
SELECT
'event' + CAST(EVENTS.id as varchar(16)) 'Event Distinct ID'
,EVENTS.id 'Event ID'
,EVENTS.event_type 'Event Type'
,BRAND_SAMPLE.name 'Event Brand'
,EVENTS.state 'Event State'
,EVENTS.funding 'Event Funding'
,CONVERT(DATE, EVENTS.end_date) 'Event Date'
,TERRITORY.name 'Event Territory Name'
,EVENTS.tier 'Event Tier'
,EVENTS.attendance 'Event Attendance'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Event Activation Cost Total'
,(COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Events Sponsor Cost Total'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0)
+ COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0))
/ COALESCE((SELECT COUNT([brand_id]) FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] WHERE event_id = EVENTS.id), 1) 'Event Total Cost'
,0 'Event Consumers Sampled' --Doesn't exist for Planned events
,0 'Event Consumers Reached' --Doesn't exist for Planned events
,'Planned' 'Event Status' --Set to planned as filters below only pull approved Planned events
FROM [TorontoMySQLRandomClientData].[dbo].[brand_event] EVENT_BRANDS
INNER JOIN [TorontoMySQLRandomClientData].[dbo].[events] EVENTS on (EVENT_BRANDS.event_id=EVENTS.id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[brands] BRAND_SAMPLE on (BRAND_SAMPLE.id=EVENT_BRANDS.brand_id)
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1'
AND CONVERT(DATE, EVENTS.end_date) >getdate()
AND CONVERT(DATE, EVENTS.end_date) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null
AND EVENTS.id NOT IN (select event_id from event_sampling_strategies where event_id=EVENTS.id) --ADDED LINE -TM
AND EVENTS.id IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[brand_event] where event_id=EVENTS.id) --ADDED LINE -TM
UNION ALL
--NOW WE GET ALL THE REST OF THE RECORDS WHERE THE EVENTS DON'T HAVE SAMPLING OR BRAND/EVENT RELATIONAL DATA
SELECT
'event' + CAST(EVENTS.id as varchar(16)) 'Event Distinct ID'
,EVENTS.id 'Event ID'
,EVENTS.event_type 'Event Type'
,'Not Specified' 'Event Brand'
,EVENTS.state 'Event State'
,EVENTS.funding 'Event Funding'
,CONVERT(DATE, EVENTS.end_date) 'Event Date'
,TERRITORY.name 'Event Territory Name'
,EVENTS.tier 'Event Tier'
,EVENTS.attendance 'Event Attendance'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0)) 'Event Activation Cost Total'
,(COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0)) 'Events Sponsor Cost Total'
,(COALESCE(CAST(brand_cost_activation as FLOAT), 0) + COALESCE(CAST(local_cost_activation as FLOAT), 0) + COALESCE(CAST(multi_cost_activation as FLOAT), 0)
+ COALESCE(CAST(brand_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(local_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(multi_cost_sponsor as FLOAT) , 0) + COALESCE(CAST(sport_cost_sponsor as FLOAT) , 0)) 'Event Total Cost'
,0 'Event Consumers Sampled' --Doesn't exist for Planned events
,0 'Event Consumers Reached' --Doesn't exist for Planned events
,'Planned' 'Event Status' --Set to planned as filters below only pull approved Planned events
FROM [TorontoMySQLRandomClientData].[dbo].[events] EVENTS
LEFT JOIN [TorontoMySQLRandomClientData].[dbo].[territories] TERRITORY on (TERRITORY.id=EVENTS.territory_id)
WHERE EVENTS.approval_status='1'
AND CONVERT(DATE, EVENTS.end_date) >= CAST(getdate() AS DATE)
AND CONVERT(DATE, EVENTS.end_date) < CAST(CONVERT(varchar, '2019-07-01 00:00:00') AS DATE)
AND EVENTS.deleted_at is null
AND EVENTS.id NOT IN (select event_id from event_sampling_strategies where event_id=EVENTS.id) --ADDED LINE -TM
AND EVENTS.id NOT IN (select event_id from [TorontoMySQLRandomClientData].[dbo].[brand_event] where event_id=EVENTS.id) --ADDED LINE -TM
) as tmp;
--Step One
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[brands];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[brand_variants];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[brand_event];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[events];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[event_recap_samples];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[event_recaps];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[event_recap_competitives];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[territories];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[territory_user];
--TRUNCATE TABLE [TorontoMySQLRandomClientData].[dbo].[states];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[event_sampling_strategies] ORDER BY updated_at DESC
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[events] ORDER BY [updated_at] DESC;
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_samples] ORDER BY [updated_at] DESC;
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[event_recaps] ORDER BY [event_date] DESC;
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[event_recap_competitives] ORDER BY [updated_at] DESC;
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[brands];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[brand_variants];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[brand_event];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[territories];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[territory_user];
--SELECT * FROM [TorontoMySQLRandomClientData].[dbo].[states];
--DROP TABLE MattPlannedSpendTable;
--DROP TABLE MattEventRecapTable;
--DROP TABLE MattConsumersTable;
--DROP TABLE MattBrandStateTable;
--SELECT * FROM MattPlannedSpendTable ORDER BY [Event Table Event Date];
--SELECT * FROM MattEventRecapTable ORDER BY [Event Recaps Table Event Date] DESC;
--SELECT * FROM MattConsumersTable ORDER BY [Event Recaps Table Event Date] DESC;
--SELECT * FROM MattBrandStateTable ORDER BY [Event Date] DESC;
----BRAND AND STATE TABLE: CLEANSE FOR EVENT TOTAL COST
WITH M AS (
SELECT [Event Distinct ID]
,[Event ID]
,[Event Type]
,[Event Brand]
,[Event State]
,[Event Funding]
,[Event Date]
,[Event Territory Name]
,[Event Tier]
,[Event Attendance]
,[Event Activation Cost Total]
,[Events Sponsor Cost Total]
,[Event Total Cost]
,CASE
WHEN ([Event Activation Cost Total] + [Events Sponsor Cost Total]) = [Event Total Cost] THEN 'MATCH'
WHEN ([Event Activation Cost Total] + [Events Sponsor Cost Total]) != [Event Total Cost]
THEN 'NOT ADDING UP' END AS 'Test'
,[Event Consumers Sampled]
,[Event Consumers Reached]
,[Event Status]
FROM [TorontoMySQLRandomClientData].[dbo].[MattBrandStateTable]
)
UPDATE M
SET M.[Event Total Cost] = ([Event Activation Cost Total] + [Events Sponsor Cost Total])
FROM M
WHERE M.Test = 'NOT ADDING UP';
----BRAND AND STATE TABLE: VALIDATION FOR EVENT TOTAL COST
-- WITH M AS (
--SELECT [Event Distinct ID]
-- ,[Event ID]
-- ,[Event Type]
-- ,[Event Brand]
-- ,[Event State]
-- ,[Event Funding]
-- ,[Event Date]
-- ,[Event Territory Name]
-- ,[Event Tier]
-- ,[Event Attendance]
-- ,[Event Activation Cost Total]
-- ,[Events Sponsor Cost Total]
-- ,[Event Total Cost]
-- ,CASE
--WHEN ([Event Activation Cost Total] + [Events Sponsor Cost Total]) = [Event Total Cost] THEN 'MATCH'
--WHEN ([Event Activation Cost Total] + [Events Sponsor Cost Total]) != [Event Total Cost]
--THEN 'NOT ADDING UP' END AS 'Test'
-- ,[Event Consumers Sampled]
-- ,[Event Consumers Reached]
-- ,[Event Status]
-- FROM [TorontoMySQLRandomClientData].[dbo].[MattBrandStateTable]
-- )
--SELECT * FROM M WHERE M.Test = 'NOT ADDING UP'
--ORDER BY M.[Event Total Cost] DESC;
--==================================================================================================================================================================================================================
--
--
-- Matt DATA VALIDATION TABLE
--
--
--=================================================================================================================================================================================================================
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT COUNT(DISTINCT ISNULL([Counts Distinct Event ID], 0))
FROM [TorontoMySQLRandomClientData].[dbo].MattPlannedSpendTable WHERE [Event Table Event Date]>= GETDATE())
,[PlannedSpendTable Total # of States] = (SELECT COUNT(DISTINCT ISNULL([Events Table State], 0))
FROM [TorontoMySQLRandomClientData].[dbo].MattPlannedSpendTable WHERE [Event Table Event Date]>= GETDATE())
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Events Table brand_cost_sponsor], 0 )+ ISNULL([Events Table local_cost_sponsor], 0 )
+ ISNULL([Events Table multi_cost_sponsor], 0 ) + ISNULL([Events Table sport_cost_sponsor], 0 )) ,'N0')
FROM [TorontoMySQLRandomClientData].[dbo].MattPlannedSpendTable WHERE [Event Table Event Date]>= GETDATE())
,[PlannedSpendTable Total Activation Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Events Table brand_cost_activation], 0 )+
ISNULL([Events Table local_cost_activation], 0 ) + ISNULL([Events Table multi_cost_activation], 0 )) ,'N0')
FROM [TorontoMySQLRandomClientData].[dbo].MattPlannedSpendTable WHERE [Event Table Event Date]>= GETDATE() )
,[PlannedSpendTable Total Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Events Table brand_cost_activation], 0 )+
ISNULL([Events Table local_cost_activation], 0 ) + ISNULL([Events Table multi_cost_activation], 0 ) + ISNULL([Events Table brand_cost_sponsor], 0 )+
ISNULL([Events Table local_cost_sponsor], 0 ) + ISNULL([Events Table multi_cost_sponsor], 0 ) + ISNULL([Events Table sport_cost_sponsor], 0 )) ,'N0') FROM [TorontoMySQLRandomClientData].[dbo].MattPlannedSpendTable
WHERE [Event Table Event Date]>= GETDATE() )
,[Date Updated] = (SELECT GETDATE())
WHERE ID = 1;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT 'BrandStateTable Total # of Events')
,[PlannedSpendTable Total # of States] = (SELECT 'BrandStateTable Total # of States' )
,[PlannedSpendTable Total Sponsor Cost] = (SELECT 'BrandStateTable Events Total Sponsor Cost' )
,[PlannedSpendTable Total Activation Cost] = (SELECT 'BrandStateTable Total Activation Cost')
,[PlannedSpendTable Total Cost] = (SELECT 'BrandStateTable Cost')
,[Date Updated] = (SELECT '')
WHERE ID = 2;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT COUNT(DISTINCT ISNULL([Event Distinct ID], 0)) FROM [TorontoMySQLRandomClientData].[dbo].MattBrandStateTable WHERE [Event Date]>= GETDATE() AND [Event Status] = 'Planned')
,[PlannedSpendTable Total # of States] = (SELECT COUNT(DISTINCT ISNULL([Event State], 0))
FROM [TorontoMySQLRandomClientData].[dbo].MattBrandStateTable WHERE [Event Date]>= GETDATE() AND [Event Status] = 'Planned')
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Events Sponsor Cost Total], 0 )) ,'N0') FROM [TorontoMySQLRandomClientData].[dbo].MattBrandStateTable WHERE [Event Date]>= GETDATE() AND [Event Status] = 'Planned')
,[PlannedSpendTable Total Activation Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Event Activation Cost Total], 0 )), 'N0') FROM [TorontoMySQLRandomClientData].[dbo].MattBrandStateTable WHERE [Event Date]>= GETDATE() AND [Event Status] = 'Planned')
,[PlannedSpendTable Total Cost] = (SELECT '$ ' +FORMAT(SUM(ISNULL([Event Total Cost], 0 )), 'N0') FROM [TorontoMySQLRandomClientData].[dbo].MattBrandStateTable WHERE [Event Date]>= GETDATE() AND [Event Status] = 'Planned')
,[Date Updated] = (SELECT GETDATE())
WHERE ID = 3;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT 'Consumers Total # of Events')
,[PlannedSpendTable Total # of States] = (SELECT 'Consumers Total # of States' )
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '' )
,[PlannedSpendTable Total Activation Cost] = (SELECT '')
,[PlannedSpendTable Total Cost] = (SELECT '')
,[Date Updated] = (SELECT '')
WHERE ID = 4;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT COUNT(DISTINCT ISNULL([Counts Distinct Event Recaps ID], 0)) FROM [TorontoMySQLRandomClientData].[dbo].MattConsumersTable WHERE [Event Recaps Table Event Date] >= GETDATE() AND [Events Table Approval Status] ='Planned')
,[PlannedSpendTable Total # of States] = (SELECT COUNT(DISTINCT ISNULL([Events Table State], 0)) FROM [TorontoMySQLRandomClientData].[dbo].MattConsumersTable WHERE [Event Recaps Table Event Date] >= GETDATE() AND [Events Table Approval Status] ='Planned')
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '' )
,[PlannedSpendTable Total Activation Cost] = (SELECT '')
,[PlannedSpendTable Total Cost] = (SELECT '')
,[Date Updated] = (SELECT GETDATE())
WHERE ID = 5;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT 'Event Recaps Total # of Events')
,[PlannedSpendTable Total # of States] = (SELECT 'Event Recaps Total # of States' )
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '' )
,[PlannedSpendTable Total Activation Cost] = (SELECT '')
,[PlannedSpendTable Total Cost] = (SELECT '')
,[Date Updated] = (SELECT '')
WHERE ID = 6;
UPDATE [TorontoMySQLRandomClientData].[dbo].[MattDataValidation]
SET [Planned Spend Table Total # Of Events] = (SELECT COUNT(DISTINCT ISNULL([Counts Distinct Event Recaps ID], 0)) FROM [TorontoMySQLRandomClientData].[dbo].MattEventRecapTable WHERE [Event Recaps Table Event Date] <= GETDATE() AND [activation_status] = 'Activated' )
,[PlannedSpendTable Total # of States] = (SELECT COUNT(DISTINCT ISNULL([Events Table State], 0)) FROM [TorontoMySQLRandomClientData].[dbo].MattEventRecapTable WHERE [Event Recaps Table Event Date] <= GETDATE() AND [activation_status] = 'Activated')
,[PlannedSpendTable Total Sponsor Cost] = (SELECT '' )
,[PlannedSpendTable Total Activation Cost] = (SELECT '')
,[PlannedSpendTable Total Cost] = (SELECT '')
,[Date Updated] = (SELECT GETDATE())
WHERE ID = 7;
USE [CRM];
--spTestCRMSegEmpTypesVersionTwoBkUp
--=================================================
DECLARE StartDate DATETIME;
DECLARE EndDate DATETIME;
DECLARE AccountManager NVARCHAR(100);
DECLARE PostalType NVARCHAR(40);
SET StartDate = '2016-02-01';
SET EndDate = '2016-02-03';
SET AccountManager = 'Test'
SET PostalType = 'Bill To Postal Code From Order'
--SET PostalType = 'Preferred Postal Code';
--============================================================================================
-- If 'Preferred Postal Code' Is Chosen
--============================================================================================
IF PostalType = 'Preferred Postal Code'
BEGIN
WITH RecordIDMatchesUserYear AS (
select ID FROM vwpersons
WHERE
--CADate >= DATEADD(YYYY, -5, GETDATE()) --where Cadate BETWEEN startDate AND EndDate and
--AND
status in (5,7) --and MembertypeID = 11
--and CADate between startDate and EndDate
),
ALLMDSProducts AS
(
SELECT DISTINCT P.ID AS ProductId
--,GL.GLAccountNumber
FROM vwProducts P
INNER JOIN vwProductGLAccounts GL ON P.ID = GL.ProductID
WHERE SUBSTRING(GL.GLAccountNumber,7,4) IN ('1150','5110','5150')
),
M AS (
SELECT DISTINCT
_vwPersons.PreferredZipCode AS 'Preferred Postal Code',
_vwOrders.BillToZipCode AS 'Bill To Postal Code From Order',
_vwPersons.CADate as MembershipDate,
_vwPersons.CompanyName AS 'Company Name',
_vwPersons.JobFunction AS 'Job Title',
_vwOrders.OrderDate AS 'Order Date',
_vwOrders.ID AS 'Order ID',
_vwOrders.SalesRep AS 'Account Manager',
_vwPersons.CAProvince,
_vwPersons.PreferredStateProvince,
_vwPersons.ID AS 'New CRMs',
_vwOrderDetails.product AS 'Product Name',
_vwOrderDetails.Quantity * _vwOrderDetails.Price * case when _vwOrderDetails.SubscriptionCopies <> 0 then _vwOrderDetails.SubscriptionCopies else 1 end - _vwOrderDetails.DiscountAmount AS SalesAmount,
_vwPersons.FirstLast,
_vwPersons.EmploymentType,
_vwPersons.Email,
_vwPersons.MemberType,
_vwPersons.CADate,
CASE WHEN _vwPersons.[Language] = 1 THEN 'English' WHEN _vwPersons.[Language] = 2 THEN 'French' ELSE '' END AS 'Preferred Language',
CASE WHEN _vwPersons.Phone IS NULL OR _vwPersons.Phone LIKE '%555-5555%' THEN ''
WHEN _vwPersons.Phone IS NOT NULL THEN CONCAT(_vwPersons.PhoneAreaCode, _vwPersons.Phone) ELSE '' END AS 'Business Phone',
CASE WHEN _vwPersons.HomePhone IS NULL OR _vwPersons.HomePhone LIKE '%555-5555%' THEN ''
WHEN _vwPersons.HomePhone IS NOT NULL THEN CONCAT(_vwPersons.HomeAreaCode, _vwPersons.HomePhone) ELSE '' END AS 'Home Phone'
FROM vwOrders _vwOrders
INNER JOIN vwPersons _vwPersons ON _vwOrders.ShipToID = _vwPersons.ID
INNER JOIN VwOrderDetails _vwOrderDetails ON _vwOrderDetails.OrderID = _vwOrders.ID
and _vwOrderDetails.ProductID in (select ProductId from ALLMDSProducts)
WHERE _vwOrders.ShipDate BETWEEN startdate AND EndDate
--WHERE _vwOrders.ShipDate >= DATEADD(YYYY, -5, GETDATE())
--and MembertypeID = 11
and status in (5,7)
and ordertypeid in(1,3) and orderstatusid = 2
),
AllMembers AS
(
select _vwPersons.ID,
_vwPersons.EmploymentType,
ISNULL(JT.JobTitleDescEng,'Other') AS JobTitle
--CASE WHEN ISNULL(D.FiscalYear,1900) = FiscalYear THEN 'New Graduated CRM' ELSE NULL END AS NewGrad
from vwPersons _vwPersons
LEFT JOIN vwJobTitles JT ON JT.ID = _vwPersons.TitleCodeID
LEFT JOIN vwCRMDimDate D ON Year(_vwPersons.CADate) * 10000 + Month(_vwPersons.CADate) * 100 + Day(_vwPersons.CADate) = D.DateKey
where Status in (5,7)
--and MemberTypeID IN (11)
--AND ISNULL(D.FiscalYear,1900) <= FiscalYear
AND _vwPersons.EmploymentType = EmploymentType
),
--Preferredzipcode is not found in vwOrders, that is why we use vwPersons
AllPreferredZipCodes AS
(
SELECT DISTINCT
_vwPersons.ID,
_vwPersons.Preferredzipcode,
_vwCRMsalesterritories.PostalCode,
_vwCRMsalesterritories.Province,
_vwCRMsalesterritories.SalesRep
FROM vwPersons _vwPersons
INNER JOIN vwCRMsalesterritories _vwCRMsalesterritories
ON LEFT(_vwCRMsalesterritories.PostalCode, 4) = LEFT(_vwPersons.Preferredzipcode, 4)
WHERE _vwCRMsalesterritories.SalesRep = AccountManager
)
SELECT DISTINCT
M.[Preferred Postal Code],
M.[Bill To Postal Code From Order],
CADate,
M.[Company Name],
M.FirstLast AS 'Name',
M.[Account Manager],
M.[Product Name],
--sum(M.[SalesAmount]) OVER (PARTITION BY M.[New CRMs] ORDER BY M.[New CRMs]) AS 'Sales Amount',
M.[SalesAmount]as 'Sales Amount',
CASE WHEN M.[EmploymentType] = 'BI' THEN 'Business Industry'
WHEN M.[EmploymentType] = 'ED' THEN 'Education'
WHEN M.[EmploymentType] = 'GC' THEN 'Government Crown'
WHEN M.[EmploymentType] = 'NP' THEN 'Not-for-profit'
WHEN M.[EmploymentType] = 'PP' THEN 'Public Practice'
WHEN M.[EmploymentType] = 'RT' THEN 'Retired'
WHEN M.[EmploymentType] = 'AE' THEN 'Arts, Entertainment and Recreation'
WHEN M.[EmploymentType] = 'AF' THEN 'Accommodation and Food Services'
WHEN M.[EmploymentType] = 'AG' THEN 'Agriculture, forestry, fishing and hunting'
WHEN M.[EmploymentType] = 'AS' THEN 'Administrative and support, waste management and remediation services'
WHEN M.[EmploymentType] = 'CT' THEN 'Construction'
WHEN M.[EmploymentType] = 'FG' THEN 'Federal Government'
WHEN M.[EmploymentType] = 'HC' THEN 'Health Care and Social Assistance'
WHEN M.[EmploymentType] = 'IC' THEN 'Information and Cultural Industries'
WHEN M.[EmploymentType] = 'IN' THEN 'Finance and Insurance'
WHEN M.[EmploymentType] = 'MC' THEN 'Management of Companies and Enterprises'
WHEN M.[EmploymentType] = 'MF' THEN 'Manufacturing'
WHEN M.[EmploymentType] = 'MG' THEN 'Municipal Government'
WHEN M.[EmploymentType] = 'MI' THEN 'Mining, Quarrying and Oil and Gas Extraction'
WHEN M.[EmploymentType] = 'OT' THEN 'Other services (except public administration)'
WHEN M.[EmploymentType] = 'PA' THEN 'Public administration'
WHEN M.[EmploymentType] = 'PG' THEN 'Provincial Government'
WHEN M.[EmploymentType] = 'PS' THEN 'Professional, Scientific and Technical services'
WHEN M.[EmploymentType] = 'RE' THEN 'Real Estate and Rental and Leasing'
WHEN M.[EmploymentType] = 'RW' THEN 'Retail/Wholesale Trade'
WHEN M.[EmploymentType] = 'TR' THEN 'Transportation and Warehousing'
WHEN M.[EmploymentType] = 'UT' THEN 'Utilities' END AS 'EmploymentType',
M.[Business Phone],
M.[Home Phone],
M.[MembershipDate],
M.[Order Date], M.[Order ID],
M.[CAProvince], M.[PreferredStateProvince],
_AllMembers.JobTitle, _AllMembers.ID, M.[New CRMs],
M.Email, -- AS 'Postal Code', --Choosing PreferredZipCode As PostalCode In Order To Have Same Field In 'Postal Code' field in Crystal Reports
M.MemberType,
M.[Preferred Language]
FROM M
INNER JOIN RecordIDMatchesUserYear _RecordIDMatchesUserYear ON M.[New CRMs] = _RecordIDMatchesUserYear.ID
INNER JOIN AllMembers _AllMembers ON _AllMembers.ID = _RecordIDMatchesUserYear.ID
INNER JOIN AllPreferredZipCodes _AllPreferredZipCodes ON _AllPreferredZipCodes.ID = _RecordIDMatchesUserYear.ID
--INNER JOIN ALLMDSProducts _ALLMDSProducts ON _ALLMDSProducts.ProductId = _RecordIDMatchesUserYear.ID
WHERE M.EmploymentType = _AllMembers.EmploymentType
--AND M.[Order Date] BETWEEN StartDate AND EndDate
--AND M.[Account Manager] = AccountManager
ORDER BY M.[Order Date]
END
--============================================================================================
-- If 'Bill To Zip Code From Order' Is Chosen
--============================================================================================
IF PostalType = 'Bill To Postal Code From Order'
BEGIN
WITH RecordIDMatchesUserYear AS (
select ID FROM vwpersons
WHERE
--CADate >= DATEADD(YYYY, -5, GETDATE()) --where Cadate BETWEEN startDate AND EndDate and
--AND
status in (5,7) --and MembertypeID = 11
--and CADate between startDate and EndDate
)
, ALLMDSProducts AS
(
SELECT DISTINCT P.ID AS ProductId
--,GL.GLAccountNumber
FROM vwProducts P
INNER JOIN vwProductGLAccounts GL ON P.ID = GL.ProductID
WHERE SUBSTRING(GL.GLAccountNumber,7,4) IN ('1150','5110','5150')
)
,
M AS (
SELECT DISTINCT
_vwPersons.PreferredZipCode AS 'Preferred Postal Code',
_vwOrders.BillToZipCode AS 'Bill To Postal Code From Order',
_vwPersons.CADate as MembershipDate,
_vwPersons.CompanyName AS 'Company Name',
_vwPersons.JobFunction AS 'Job Title',
_vwOrders.OrderDate AS 'Order Date',
_vwOrders.ID AS 'Order ID',
_vwOrders.SalesRep AS 'Account Manager',
_vwPersons.CAProvince,
_vwPersons.PreferredStateProvince,
_vwPersons.ID AS 'New CRMs',
_vwOrderDetails.product AS 'Product Name',
_vwOrderDetails.Quantity * _vwOrderDetails.Price * case when _vwOrderDetails.SubscriptionCopies <> 0 then _vwOrderDetails.SubscriptionCopies else 1 end - _vwOrderDetails.DiscountAmount AS SalesAmount,
_vwPersons.FirstLast,
_vwPersons.EmploymentType,
_vwPersons.Email,
_vwPersons.MemberType,
_vwPersons.CADate,
CASE WHEN _vwPersons.[Language] = 1 THEN 'English' WHEN _vwPersons.[Language] = 2 THEN 'French' ELSE '' END AS 'Preferred Language',
CASE WHEN _vwPersons.Phone IS NULL OR _vwPersons.Phone LIKE '%555-5555%' THEN ''
WHEN _vwPersons.Phone IS NOT NULL THEN CONCAT(_vwPersons.PhoneAreaCode, _vwPersons.Phone) ELSE '' END AS 'Business Phone',
CASE WHEN _vwPersons.HomePhone IS NULL OR _vwPersons.HomePhone LIKE '%555-5555%' THEN ''
WHEN _vwPersons.HomePhone IS NOT NULL THEN CONCAT(_vwPersons.HomeAreaCode, _vwPersons.HomePhone) ELSE '' END AS 'Home Phone'
FROM vwOrders _vwOrders
INNER JOIN vwPersons _vwPersons ON _vwOrders.ShipToID = _vwPersons.ID
INNER JOIN VwOrderDetails _vwOrderDetails ON _vwOrderDetails.OrderID = _vwOrders.ID
and _vwOrderDetails.ProductId in (select ProductId from ALLMDSProducts)
WHERE _vwOrders.ShipDate BETWEEN startdate AND EndDate
--WHERE _vwOrders.ShipDate >= DATEADD(YYYY, -5, GETDATE())
--and MembertypeID = 11
and status in (5,7)
and ordertypeid in(1,3) and orderstatusid = 2
),
AllMembers AS
(
select _vwPersons.ID,
_vwPersons.EmploymentType,
ISNULL(JT.JobTitleDescEng,'Other') AS JobTitle
--CASE WHEN ISNULL(D.FiscalYear,1900) = FiscalYear THEN 'New Graduated CRM' ELSE NULL END AS NewGrad
from vwPersons _vwPersons
LEFT JOIN vwJobTitles JT ON JT.ID = _vwPersons.TitleCodeID
LEFT JOIN vwCRMDimDate D ON Year(_vwPersons.CADate) * 10000 + Month(_vwPersons.CADate) * 100 + Day(_vwPersons.CADate) = D.DateKey
where Status in (5,7)
--and MemberTypeID IN (11)
--AND ISNULL(D.FiscalYear,1900) <= FiscalYear
AND _vwPersons.EmploymentType = EmploymentType
)
,
--BillToZipCode Is not found in vwPersons, that is why we use vwOrders
AllBillToZipCodes AS
(
SELECT DISTINCT
_vwOrders.ID,
_vwOrders.BillToZipCode,
_vwCRMsalesterritories.PostalCode,
_vwCRMsalesterritories.Province,
_vwCRMsalesterritories.SalesRep
FROM vwOrders _vwOrders
INNER JOIN vwCRMsalesterritories _vwCRMsalesterritories
ON LEFT(_vwCRMsalesterritories.PostalCode, 4) = LEFT(_vwOrders.BillToZipCode, 4) -- compare 3 to 4 perhaps add RTRIM
WHERE _vwCRMsalesterritories.SalesRep = AccountManager
)
SELECT DISTINCT
M.[Preferred Postal Code],
M.[Bill To Postal Code From Order],
CADate,
M.[Company Name],
M.FirstLast AS 'Name',
M.[Account Manager],
M.[Product Name],
--sum(M.[SalesAmount]) OVER (PARTITION BY M.[New CRMs] ORDER BY M.[New CRMs]) AS 'Sales Amount',
M.[SalesAmount]as 'Sales Amount',
CASE WHEN M.[EmploymentType] = 'BI' THEN 'Business Industry'
WHEN M.[EmploymentType] = 'ED' THEN 'Education'
WHEN M.[EmploymentType] = 'GC' THEN 'Government Crown'
WHEN M.[EmploymentType] = 'NP' THEN 'Not-for-profit'
WHEN M.[EmploymentType] = 'PP' THEN 'Public Practice'
WHEN M.[EmploymentType] = 'RT' THEN 'Retired'
WHEN M.[EmploymentType] = 'AE' THEN 'Arts, Entertainment and Recreation'
WHEN M.[EmploymentType] = 'AF' THEN 'Accommodation and Food Services'
WHEN M.[EmploymentType] = 'AG' THEN 'Agriculture, forestry, fishing and hunting'
WHEN M.[EmploymentType] = 'AS' THEN 'Administrative and support, waste management and remediation services'
WHEN M.[EmploymentType] = 'CT' THEN 'Construction'
WHEN M.[EmploymentType] = 'FG' THEN 'Federal Government'
WHEN M.[EmploymentType] = 'HC' THEN 'Health Care and Social Assistance'
WHEN M.[EmploymentType] = 'IC' THEN 'Information and Cultural Industries'
WHEN M.[EmploymentType] = 'IN' THEN 'Finance and Insurance'
WHEN M.[EmploymentType] = 'MC' THEN 'Management of Companies and Enterprises'
WHEN M.[EmploymentType] = 'MF' THEN 'Manufacturing'
WHEN M.[EmploymentType] = 'MG' THEN 'Municipal Government'
WHEN M.[EmploymentType] = 'MI' THEN 'Mining, Quarrying and Oil and Gas Extraction'
WHEN M.[EmploymentType] = 'OT' THEN 'Other services (except public administration)'
WHEN M.[EmploymentType] = 'PA' THEN 'Public administration'
WHEN M.[EmploymentType] = 'PG' THEN 'Provincial Government'
WHEN M.[EmploymentType] = 'PS' THEN 'Professional, Scientific and Technical services'
WHEN M.[EmploymentType] = 'RE' THEN 'Real Estate and Rental and Leasing'
WHEN M.[EmploymentType] = 'RW' THEN 'Retail/Wholesale Trade'
WHEN M.[EmploymentType] = 'TR' THEN 'Transportation and Warehousing'
WHEN M.[EmploymentType] = 'UT' THEN 'Utilities' END AS 'EmploymentType',
M.[Business Phone],
M.[Home Phone],
M.[MembershipDate],
M.[Order Date], M.[Order ID],
M.[CAProvince], M.[PreferredStateProvince],
_AllMembers.JobTitle, _AllMembers.ID, M.[New CRMs],
M.Email,
M.MemberType,
M.[Preferred Language]
FROM M
INNER JOIN RecordIDMatchesUserYear _RecordIDMatchesUserYear ON M.[New CRMs] = _RecordIDMatchesUserYear.ID
INNER JOIN AllMembers _AllMembers ON _AllMembers.ID = _RecordIDMatchesUserYear.ID
INNER JOIN AllBillToZipCodes _AllBillToZipCodes ON _AllBillToZipCodes.ID = _RecordIDMatchesUserYear.ID
--INNER JOIN ALLMDSProducts _ALLMDSProducts ON _ALLMDSProducts.ProductId = _RecordIDMatchesUserYear.ID
WHERE M.EmploymentType = _AllMembers.EmploymentType
--AND M.[Order Date] BETWEEN StartDate AND EndDate
--AND M.[Account Manager] = AccountManager
ORDER BY M.[Order Date]
END