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 datediff as a date_or_time_part so that it can dynamically set the time
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”, “period”:{“Type”: “definite”, “end_date”: “2024-03-29 11:17:42.000”}}] my objective is to create two
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 (2016- January or 2016-01)as a new field in the output. This is what I
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 and ending quotes. for second example it is given as N’agendra it should be ‘N’agendra Answer If you want to keep the starting ‘ then don’t use
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 select EXPIRED. In the example
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 got the same
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 (not creating a new column in the database)
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 “Id” FROM DB1.”PUBLIC”.”TABLE1″) INNER JOIN DB1.”PUBLIC”.”TABLE2″ ON DB1.”PUBLIC”.”TABLE1″.”Id” = DB1.”PUBLIC”.”TABLE2″.”Id” WHERE “queryGroupName” not in (‘DELETE’);