Ad
DATEIME_DIFF Throwing Error When Using Safe Divide
I've created a query that I'm hoping to use to fill a table with daily budgets at the end of every day. To do this, I just need some simple maths: monthly budget / number of days left in the month.
Then, at the end of the month, I can SUM all of the rows to calculate an accurate budget.
The query is as follows:
SELECT *,
ROUND(SAFE_DIVIDE(budget, DATETIME_DIFF(CURRENT_DATE(), LAST_DAY(CURRENT_DATE()), DAY)),2) AS daily_budget
FROM `mm-demo-project.marketing_hub.budget_manager`
When executing the query, my results present as negative numbers, which according to the documentation for this function, is likely caused by the result overflowing the result type.
View the results of the query.
I've made a fools guess at rounding the calculation. Needless to say that it did not work at all.
How can I stop my query from returning negative number?
Ad
Answer
use below
SELECT *,
ROUND(SAFE_DIVIDE(budget, DATETIME_DIFF(LAST_DAY(CURRENT_DATE()), CURRENT_DATE(), DAY)),2) AS daily_budget
FROM `mm-demo-project.marketing_hub.budget_manager`
Ad
source: stackoverflow.com
Related Questions
- → BigQuery / Shopify Order Data Query
- → BigQuery / Shopify - get tracking numbers with order #
- → How to pluck the oldest child record from a join in BigQuery SQL where ordered by date and limit 1
- → extracting name/value pairs into columns and values - Shopify Data In Bigquery
- → Extracting text from a string before the first occurrence of a symbol
- → Next.js - using BigQuery client library gives an error : Module not found: Can't resolve 'child_process'
- → SQL BigQuery. Check if user bought something previous month = old, if he didn't buy anything next month = churn
- → String Functions on Unnested Arrays in BigQuery SQL
- → Google BigQuery: loading data from a local CSV file using the Java API with custom field delimiter
- → CASE WHEN - Multiple Conditions - Over Multiple Rows of same reference
- → How to import CSV to an table on BigQuery using properly
- → BigQuery: Unexpected behaviour using bigquery job when writing query result
- → Subtract dates from array Bigquery
Ad