June 18, 2020 at 10:28 am
Hi All,
Not too familiar with Macros at all, but I have adjusted a pre-existing script and I am trying to create a Table from it.
The query works fine but I just font know where to fit my CREATE OR REPLACE table statement (CREATE OR REPLACE TABLE Bookings_Monthly AS)
Any advice?
If its relevant, I am using BigQuery.
Thanks!
DEFINE MACRO BOOKING_PERIOD_CUR booking_date BETWEEN '2020-01-01' AND '2020-06-30';
DEFINE MACRO BOOKINGS
(
SELECT
CASE
WHEN TRIM(partner_group_name) = 'Unmanaged' AND partner_hq_name IS NOT NULL THEN UPPER(TRIM(partner_hq_name))
WHEN TRIM(partner_group_name) = 'Unmanaged' THEN UPPER(TRIM(partner_name))
ELSE UPPER(TRIM(partner_GROUP_name))
END AS partner,
booking_month,
channel,
segment,
country_name,
region,
partner_group_name,
partner_name,
IF(TRIM(partner_group_name) = 'Unmanaged' AND partner_hq_name NOT IN ('Ilop', 'Lendo'), '3-unmanaged', '1-managed') AS man_unman,
SUM(IF($BOOKING_PERIOD_CUR AND is_new_in_half IN (true), bookings, 0)) AS gst_new_cur,
SUM(IF($BOOKING_PERIOD_CUR AND is_new_in_half NOT IN (true), bookings, 0)) AS gst_exist_cur,
SUM(IF($BOOKING_PERIOD_CUR, bookings, 0)) AS gst_total_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_new_booking_amount, 0)) AS gst_new_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_upsell_booking_amount, 0)) AS gst_upsell_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_renewal_booking_amount, 0)) AS gst_renew_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_new_booking_amount, 0)) +
SUM(IF($BOOKING_PERIOD_CUR, partner_upsell_booking_amount, 0)) +
SUM(IF($BOOKING_PERIOD_CUR, partner_renewal_booking_amount, 0)) AS gst_total_ifs_cur,
FROM
cloud_scaled_ops.gs_bookings
WHERE
customer_type = 'Reseller'
AND channel LIKE 'Online'
AND $BOOKING_PERIOD_CUR
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
)
;
WITH BOOKING_RANK AS
(
SELECT
man_unman,
partner,
booking_month,
channel,segment,
country_name,
region,
partner_group_name,
partner_name,
gst_new_cur,
gst_exist_cur,
gst_total_cur,
gst_new_ifs_cur,
gst_upsell_ifs_cur,
gst_renew_ifs_cur,
gst_total_ifs_cur,
gst_total_cur * (gst_new_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_new_ifs_cur_adj,
gst_total_cur * (gst_upsell_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_upsell_ifs_cur_adj,
gst_total_cur * (gst_renew_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_renew_ifs_cur_adj,
FROM $BOOKINGS
)
SELECT
partner,
booking_month,
channel,
segment,
country_name,
region,
partner_group_name,
partner_name
gst_new_cur,
gst_exist_cur,
gst_total_cur,
gst_new_ifs_cur_adj,
gst_upsell_ifs_cur_adj,
gst_renew_ifs_cur_adj,
gst_new_ifs_cur_adj + gst_upsell_ifs_cur_adj + gst_renew_ifs_cur_adj AS gst_total_ifs_cur_adj
FROM
BOOKING_RANK
ORDER BY 1, 2, 3
;
June 18, 2020 at 3:06 pm
Hi All,
Not too familiar with Macros at all, but I have adjusted a pre-existing script and I am trying to create a Table from it.
The query works fine but I just font know where to fit my CREATE OR REPLACE table statement (CREATE OR REPLACE TABLE Bookings_Monthly AS)
Any advice?
If its relevant, I am using BigQuery.
Thanks!
DEFINE MACRO BOOKING_PERIOD_CUR booking_date BETWEEN '2020-01-01' AND '2020-06-30';
DEFINE MACRO BOOKINGS
(
SELECT
CASE
WHEN TRIM(partner_group_name) = 'Unmanaged' AND partner_hq_name IS NOT NULL THEN UPPER(TRIM(partner_hq_name))
WHEN TRIM(partner_group_name) = 'Unmanaged' THEN UPPER(TRIM(partner_name))
ELSE UPPER(TRIM(partner_GROUP_name))
END AS partner,
booking_month,
channel,
segment,
country_name,
region,
partner_group_name,
partner_name,
IF(TRIM(partner_group_name) = 'Unmanaged' AND partner_hq_name NOT IN ('Ilop', 'Lendo'), '3-unmanaged', '1-managed') AS man_unman,
SUM(IF($BOOKING_PERIOD_CUR AND is_new_in_half IN (true), bookings, 0)) AS gst_new_cur,
SUM(IF($BOOKING_PERIOD_CUR AND is_new_in_half NOT IN (true), bookings, 0)) AS gst_exist_cur,
SUM(IF($BOOKING_PERIOD_CUR, bookings, 0)) AS gst_total_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_new_booking_amount, 0)) AS gst_new_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_upsell_booking_amount, 0)) AS gst_upsell_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_renewal_booking_amount, 0)) AS gst_renew_ifs_cur,
SUM(IF($BOOKING_PERIOD_CUR, partner_new_booking_amount, 0)) +
SUM(IF($BOOKING_PERIOD_CUR, partner_upsell_booking_amount, 0)) +
SUM(IF($BOOKING_PERIOD_CUR, partner_renewal_booking_amount, 0)) AS gst_total_ifs_cur,
FROM
cloud_scaled_ops.gs_bookings
WHERE
customer_type = 'Reseller'
AND channel LIKE 'Online'
AND $BOOKING_PERIOD_CUR
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
)
;
WITH BOOKING_RANK AS
(
SELECT
man_unman,
partner,
booking_month,
channel,segment,
country_name,
region,
partner_group_name,
partner_name,
gst_new_cur,
gst_exist_cur,
gst_total_cur,
gst_new_ifs_cur,
gst_upsell_ifs_cur,
gst_renew_ifs_cur,
gst_total_ifs_cur,
gst_total_cur * (gst_new_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_new_ifs_cur_adj,
gst_total_cur * (gst_upsell_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_upsell_ifs_cur_adj,
gst_total_cur * (gst_renew_ifs_cur / NULLIF(gst_total_ifs_cur, 0)) AS gst_renew_ifs_cur_adj,
FROM $BOOKINGS
)
SELECT
partner,
booking_month,
channel,
segment,
country_name,
region,
partner_group_name,
partner_name
gst_new_cur,
gst_exist_cur,
gst_total_cur,
gst_new_ifs_cur_adj,
gst_upsell_ifs_cur_adj,
gst_renew_ifs_cur_adj,
gst_new_ifs_cur_adj + gst_upsell_ifs_cur_adj + gst_renew_ifs_cur_adj AS gst_total_ifs_cur_adj
FROM
BOOKING_RANK
ORDER BY 1, 2, 3
;
Wouldn't you be better off posting your questions on a site dedicated to BigQuery? https://cloud.google.com/bigquery/
Hopefully someone on this site has familiarity with BigQuery, but this site is dedicated to SQL Server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply