I have a table where the date_one field is of type VARCHAR2 (20 BYTE) and the date_two field is of type DATE. Output for date_one is 29-01-2019 20:22:08 and output for date_two is 25-JAN-19. I need to calculate records based on an interval of days between these two fields but I’m having a hard time with…
Tag: sql
Decluttering a SQL query
For a practice project i wrote the following query and i was wondering if there is way to make it more efficient than writing everything 12 times like a for loop for sql. EDIT I was given the 3 tables represented on the following database schema , and im trying to create a table with the total sales of dairy
Oracle SQL Filter common registries (1:1) between two tables
I need some help to approach a proper counting between 2 tables. There are 2 tables with duplicated fields on each table. I need to filter same registries between these 2 tables but it must be counted only once per ID on both tables. I was thinking about selecting by Distinct ID but still not getting the corr…
How to convert integer[] to jsonb in a PL/pgSQL code block
How to convert integer[] to jsonb? Answer Use to_jsonb(): Or: array_to_json() is only still useful to get line feeds in json (not jsonb!). The manual: Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optiona…
Can’t SET variable in BigQuery | Error: Cannot coerce expression
I’m trying to set variable in order to make further operation. But when I set my first variable on line 2 (“SET(new_user)=( “), I get an error Query error: Cannot coerce expression ( SELECT COUNT(*) FROM… The problem is coming the way I use the SET because my SELECT work without. Answe…
Assign NULL to repeated values in different columns in SQL
I have a table like this: id mail_1 mail_2 mail_3 1 john john_v2 NULL 2 clarisse clarisse clarisse_company 3 NULL julie NULL 4 mark markus_91 mark 5 alfred alfred alfred And I would like to put NULLs where the mail is repeated, for example in the row 2 mail_1 and mail_2 have the same value; clarisse, and I wo…
concatenate date + time to make timestamp
I am using dbt and snowflake to parse a json.. Currently, I parse two cols, date and time separately. Now, I want to concatenate both and assign the type timestampto them I tried this:: However, the col “REQUIRED_TIMESTAMP” is just always empty in my final table. What else can I try? Answer I assu…
How to Count the number of Countries From registered users in my users table
I have two tables country and users. The country holds id’s for every country in the world. When a user registers into the system the id of country is saved in a column named country as a foreign key. Now what I want to do is to count how many countries users registered, for instance 3 users registered …
Create a JSON object to be sent as parameter to Stored Procedure
I have a stored proc say sp_proc_1 which takes in a few parameters say @val1, @val2, and @val3. I would like to make a JSON object using the values @val1 and @val2 so that I can pass that JSON object as one of the parameters to a second stored procedure sp_proc2 which I will be invoking from sp_proc1. Please …
Grouping based on start date matching the previous row’s end date SQL
Hoping someone can help me out with this problem. I have the following sample dataset: MEM_ID CLM_ID ADM_DT DCHG_DT 1 111 01-01-2020 02-01-2020 1 112 03-01-2020 04-01-2020 1 113 04-01-2020 05-01-2020 1 114 06-01-2020 07-01-2020 2 211 01-01-2020 02-01-2020 2 212 05-01-2020 08-01-2020 3 311 02-01-2020 03-01-202…