I have the following table. I get the following output. How can I make this more efficient. final output 1 The function i used is as follows. CREATE OR REPLACE FUNCTION sp.string_flatten_dedup(string_value string, delim string) AS ( ); I would also like to be able to store the data where concat_last_name as a…
Tag: sql
How do I retrieve scope identity with ExecuteNonQuery?
My project is using .NET Core 3.1 and I have my stored procedures executing in my repository class. I want to insert and return the scope identity(the id of the record that just inserted UserNumber) so I can use it for another stored proc within this same method. The problem I have here is that parameters[1].…
Conversion of time zone in snowflake sql
I am doing a conversion of timezone from current_timestamp to ‘US/Eastern’ in snowflake view. I was able to create the view but when I preview the data I am getting Timestamp ‘US/Eastern’ is not recognized error. I am converting like below: ((CONVERT_TIMEZONE(current_timestamp, ‘…
Create a trigger that updates the columns of another table (Postgresql)
I have two tables “books” and “bookOrder” and they look something like this: bookOrder orderID book name required availability 1 Harry Potter 9 yes 2 Twilight 8 yes 3 Bible 8 yes books book name quantity Harry Potter 10 Twilight 5 Bible 8 I want to create a trigger that every time the …
bigquery transpose and concatenate for each record
I want to achieve the following transformation. I have last_name stored in a repeated record as follows. data before transformation I want to achieve the following. data after transformation Example with sample data created. I’m not sure either if I should store it as an array instead of a concatenated …
Spark.sql Filter rows by MAX
Below is part of a source file which you could imagine being much bigger: After the following code: I would like to obtain this result: The aim is to: Select the dates which each cityname has the MAX total (Note, A city can appear twice if they have MAX total for 2 different dates), Sort by total descending, …
Running query against all schemas in Postgres
Context We do schema-based multi-tenancy in our Postgres DB. Each and every schema is associated to a different tenant and will have the exact same structure except for one schema named public. To obtain the list of all the relevant schemas, we can use: Problem We need to periodically clean up a specific tabl…
Dynamic/Customized Week Number
Let us say there is a table which stores data for student’s attendance for classes. The student only need to attend each class once weekly. Hinging on the days_diff column, we decide which weeks the student should get credited for. As usual, each week has 7 days and the end of the week (in days) is divi…
SQL: is there a way to count the number of values from a table if there are none?
So i have 2 tables, one its “Movies”(movie,type,lenght) and one “Cast”(title_movie,id_actor) and i have to count in how many “SF” Movies every actor played. I tried this: and it only shows me the actors that has at least 1 SF movie, the ones with 0 movies are not displayed.…
Is there a way to group values getting only the ones that verifies certain condition?
I’m trying to write a query in Oracle SQL that aggregates values by some ids, where I have the following table as an input: ID SOME_DATE RANK_POSITION 301 20211201 1 301 20211202 2 301 20211203 3 649 20211201 1 649 20211202 2 649 20211206 3 649 20211208 4 649 20211211 5 758 20211212 1 758 20211222 2 And…