I have a column with strings such as “month” and “year” in it, but when I pass that into the first argument for datediff it fails saying: [‘COLUMN_NAME’] is not a valid date/time component for function DATEDIFF. How can I pass in column_name as the first argument to datedif…
Tag: snowflake-cloud-data-platform
issues converting output from a view to json and array in snowflake using SQL
I am new to snowflake and have successfully created a view from a table using sql, but I am having issues creating a view that transforms the whole table into Json and array my view my_view output id town created_date updated_at Services 123 modak 2024-03-29 2024-03-29 [{ “service”: “green&#…
extract year_month from a date column along with other columns in a sql table
I have a sql table with multiple fields including (but not limited to the following): member_id, visit_date (in datetime format eg: 2016-01-01), visit_yr_qtr (as string, eg: 2016_Q1), purchase_item (ID number), and item_price (in US dollars). I would like to extract these fields, but also include year_month (…
If a string is enclosed in quotes either ” or ‘. I have to remove those starting and ending quotes in snowflake
Check the following input/output examples: Input: “”Nag”ndra”” -> Expected output: “Nag”ndra” Input: ‘N’agendra -> Expected output ‘N’agendra I tried the below query to implement that behavior, which is able to remove the starting a…
How to calculate difference in dates for column with smallest value
I have a table that contains 3 column dates: CREATED, CLOSED and EXPIRED. I need to calculate the difference in date between either CLOSED or EXPIRED and CREATED. I need to select the SMALLER value between CLOSED and EXPIRED and then calculate the difference to CREATED. If they have the same date, I need to s…
Aggregate function of column that has at least one type of value in a different column
I have table below as How can I query my table above to get average of only my IDs that have at least have one row with blood type of A Expected output: Thanks! Answer Tim’s answer is good, a simpler albeit perhaps not how you would want to do it, other way, is doing HAVING in long form so
UNPIVOT in Snowflake and adding additional columns
I am trying to unpivot a table TABLE_A and add additional columns to the query TABLE_A: YEAR ID OCT NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP 2022 1 10 5 5 10 5 10 15 20 5 0 0 10 Expected output: ID MONTHLY_NO MONTH START_DATE YEAR 1 10 10 10-01-2021 2021 1 5 11 11-01-2021
Different behaviour of Numeric columns in postgres and Snowflake
I have a tbale both inm snowflake and postgres in postgres the numeric column is defined as numeric(20,6) in snowflake also it is defined as numeric(20,6) but when I am doing the sum of that column I am getting value like : Now again I jumped into snowflake and changed the column definition to (20,5) then, I …
How to sum the values of two columns and have it in a new one? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 5 months ago. Improve this question I’m trying to sum the values of two columns into a new one, its for analysis purpose …
DISTINCT from One table and INNER JOIN with another table in snowflake
I would like to: Join two tables Remove “queryGroupName” = ‘DELETE’ from the first table From the first table, get Id by deduping (distinct ID) Using this Id, inner join with another table For output, select only the second table SELECT “TABLE2”.* FROM (SELECT DISTINCT R…