I am trying to create a common data field based on multiple columns below. When I try to use nvl i am getting blank values in the common field and its not getting picked up..The input and output tables are below: Input table model-manufacture model -CAM MODEL -schedule CX190 NM890 NM890 JK1231 LO121 LO121 VB1231 OPE12 ope12 jk7812 Output table
Only return rows where Status Online is the only status is applicable (perhaps a coalesce function or qualify function)
ID Status Date 1 Online 2022-06-31 1 Offline 2022-07-28 2 Online 2022-08-01 3 Online 2022-07-03 3 Stationary 2022-07-05 4 Offline 2022-05-02 5 Online 2022-04-04 5 Online 2022-04-06 The output I’m looking for in SQL/BigQuery ID Status Date 2 Online 2022-08-01 5 Online 2022-04-04 5 Online 2022-04-06 So what SQL query I would want to return is the rows for ID
How to calculate total balance in sql using sum?
updated question — I have a table that contains the following columns: I need to calculate the balance based on the update date. In some cases there can be the same update_date for two different records. When I have this, I want to grab the lower value of the balance. This is the query I have so far: In the
Foreign key relationship with boolean values
I’m working on a feature. I’ve three different car types (Sedan, Hatchback, SUV): I’ve 6 features in total. Out of 6, 4 features are their in every car. The second car category has 5 and the third has all the 6 features. Here where I’m getting stuck: I’ve to send all the 6 features in all the categories to the
Complex Conditional Query in Where Clause
In my company, each department has their own statuses for purchase orders. I’m trying to load only the pertinent POs for a specific user’s department. I keep getting errors. It seems like it should be correct, though. I initially attempted to use a Case statement, but it appears that SQL can only do a simple return from a case, so
Google Bigquery SQL with clause `where 1=0` is not returning correct schema
I recently added a new column in my BigQuery Table. The following code snippet is used in legacy code to determine the table schema But the problem is that it is not returning the newly added column in the df. Although when I use some other condition like 1=3 in where clause or limit 0 then it returns the correct
How to get data for administrative weeks of a month?
What I want to do is to get the data of each “administrative” week (Monday to Sunday), that is if a month starts a Wednesday,the next week when I would launch the query, I would want the data from Wednesday, Thursday, Friday, Saturday, Sunday of the past week. This would get me data of week 1, and so forth for
Insert only specific columns with DBT
As of now, I have an INSERT INTO clause, that works like this: The final_table has 4 columns: ID (incremental/serial), name, age and created_at. The person_table has only name, age, and created_at. When I translate it to the dbt logic, it looks like this: However, dbt keeps compiling as an INSERT CLAUSE containing the ID (that it is auto-generated). Does
Get JSON object keys as array in Presto/Trino
I have JSON data like this in one of my columns and I would like to run a query that returns the keys as an array Answer Convert your JSON into a MAP and then use map_keys(): Use json_parse() if your JSON column is of type VARCHAR Output: _col0 bar, foo foo
how can i optimize this mysql statement, it is very slow
Answer This query is slow because it is actually 16 x 3 = 48 queries. You have 16 SELECT correlated subqueries in your main SELECT query, and you’re looking up three values of p._id. Your first step is to transform at least some of those subqueries into independent subqueries and JOIN them. (This is a vital skill for people who