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…
Tag: sql
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