I’ve attached the sample data. Here actually I wanted to calculate the running total of emp_contribution and vpf column together. however that should be bounded within a financial year. suppose for 2015 it will start from April, 2015 to March, 2016. This is where I’m facing the challenges. Below I…
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 …
unpivot columns into two new columns in Stream Analytics
I have a stream from IoT Hub like: I want to pass this stream in a SQL DB like this: is there any way to use cross apply or other way to create two new columns form multi json element Answer If it’s a static pivot (you know in advance the list of fields and you can hardcode their value),
How do I use BigQuery DML to transform some fields of a struct nested within an array, within a struct, within an array?
I think this is a more complex version of the question in Update values in struct arrays in BigQuery. I’m trying to update some of the fields in a struct, where the struct is heavily nested. I’m having trouble creating the SQL to do it. Here’s my table schema: This is what the schema looks l…
How do I use BigQuery DML to insert a row with an array within a struct within an array?
I’m trying to create a Stack Overflow question where, in order to ask the question, I need to produce a minimal example. As I create the minimal example, I use BigQuery DDL to create a table. The table create step succeeds, inferring that the schema I chose is fine. But the INSERT INTO statement I wrote…
SQL to find sum of total days in a window for a series of changes
Following is the table: start_date recorded_date id 2021-11-10 2021-11-01 1a 2021-11-08 2021-11-02 1a 2021-11-11 2021-11-03 1a 2021-11-10 2021-11-04 1a 2021-11-10 2021-11-05 1a I need a query to find the total day changes in aggregate for a given id. In this case, it changed from 10th Nov to 8th Nov so 2 days…
Create a flag to know if a column is in multiple intervals
I am struggling in SQL to create a flag based on an interval. This is my use case: Table: result merchandising_month client_id start_month_12m_before end_month_12m_before start_month end_month 202106 client1 201910 202009 202010 202109 202006 client1 201910 202009 202010 202109 202003 client2 201910 202009 20…
Case statements with Join on lookup
I have a big table with some columns having lookup values. I want to create a description column for each lookup column and populate them with description from lookup table. Here is one example main table id1 id2 id3 1 2 3 1 3 4 look up table code id desc id1 1 id1-desc1 id2 2 id2-desc2 id2 3 id2-desc3
Return first element of each cell
I have a table like this: Id Description Recipient 1 lipsum 35235 2 dolor est 123, 456, 2432 3 Lorem Ipsum 143243, 34, 2344 And I’d like an output like this: Id Description Recipient RecipientId 1 lipsum 35235 35235 2 dolor est 123, 456, 2432 123 3 Lorem Ipsum 143243, 34, 2344 143243 I need to join a ta…
Filtering with exists in BigQuery
I’m running the following query in bigQuery and I don’t have the expected output. Isn’t that only the row with Alpha should be returned? Answer Use below instead with output