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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy