I have a table which in which I would like to generate random numbers in a specific format (e.g. TEST-10256). For which I have been using the below: However I now want to update a table with these random numbers based on two columns, so the desired outcome would be this: I am not sure how to keep the same
Tag: snowflake-cloud-data-platform
Snowflake error when using datetime variable in a query
I am trying to query a database table for a specific date range. When the dates are hardcoded, the query works without any problem, however, when I try to use the variables, Snowflake is returning an error message “”, there is no additional information. I have tried using Date, DateTime, DateTimeLTZ, Varchar datatypes but so far nothing has worked. Snowflake
Add a column and populate it with the queried values from another column
I have a column having dates in varchar format, I want to convert it, to date format, which I did through below query: Now, I want to create a new column and populate the column with the values that I got from above query. I am not able to do it with alter table. Any solutions? Answer Hope it works
SnowflakeSQL get me last text after specific char
I’m trying to retrieve last part of string after underscore.. but every record has different number of underscore and I’m not sure how to write it correctly. Example: aaa_bb_cccc_dddd_ee – only ee aaa_bb_cccc_dddd – only dddd sss_aas_ww_ww_ww_bb – only bb As you can see there is different number of underscore and I need only last part after last underscore. I
How to Convert Array or String in Snowflake to Timestamp_NTZ
I currently have some incoming datestamps as from outside datasource, but am struggling to define them in a table through my Snowflake Tables. The column is formatted as such: {“type”:”TEXT”,”length”:12,”byteLength”:48,”nullable”:true,”fixed”:false} The only current workaround I have is this convoluted code. Any suggestions to convert string to integer for timestamp? Answer Assuming the format of input ‘YYMMDDHH24MISS’ and first 22 stands
Window Function w/ a case statement
enter image description hereHelp for a newer SQL analyst: I need to get MAX value from the YearMo column by company, where Prem is zero/null. I created a flag to find YearMo where the sum is 0, indicated by a ‘1’ in the exclude column. How can I edit my current window function to include a case statement where it
Modifying records in one column of a table conditioning on the date ranges in the other table
I am currently trying to combine Left join with window function (e.g. partition by class_id order by date_to desc) to solve the following problem in SQL. It is quite a difficult problem due to a bunch of tricky constraints that come into play, and it seems to me I need to have a way to choose which rows from the
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
Creating a VIEW to remove duplicates from table based on max date
I have a table that appends data each day and records the imported date, however it appends duplicates. My end goal here is to remove the duplicates based on the lowest imported column date. Here would be the initial state of that table: TABLE CLIENTS Name Surname Imported Bob John 18-07-2022 Marta White 18-07-2022 Ryan Max 18-07-2022 Bob John 20-07-2022
How can I pass a stored procedure parameter into a cursor in snowflake?
I am currently trying to pass a parameter into a stored procedure and use this parameter when creating a cursor unsuccessfully. Here is a simplified snippet of the code I currently have for creating the stored procedure: I then call the stored procedure using: However, when this stored procedure is run, it just runs the string ‘ ||:TABLE2PROFILE|| ‘ without