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, DateTimeL…
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 …
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…
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 st…
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 …
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…
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-0…
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 ju…