Given the following data sample, I’m trying to perform a running sum() isolated to date, account_no, service. Hoping someone here can help. I started with this code: The above worked as expected but then I wanted to see if I could add in other data features such as account_name and service such that I c…
Tag: sql
how to check if date is within the same month as date in another column SQL BQ
I have two date fields in my BQ table. One is start_date and the other one is end_date. DataType for both columns is: DATE. Data looks like this: I need to create a new column and check if both dates are within the same month. The values would be “yes” and “no”. Yes if two dates are wi…
SQL query to get both common and and non common data from 2 tables
Hi im looking for a query which will give me both common and non-common data in one query. Table 2 ID Assay 1 124 Result required_missing required_present 125 124 Based on req_ind column from table 1 , if req_ind is 1 and the same assay is present in table 2 i want to list it as above. required missing column
Set output to 1 if 5 or more days have passed since the last eligible entry
I have the below table: I would like to implement this logic: the oldest line (S1) will have output 1. For the subsequent lines, if 5 or more days have passed from the previous entry with an output = 1, mark that output as 1 and continue evaluating. The expected output would look something like this (4th colu…
Oracle – Data that contains multiple results from IN
I have a dataset of different transaction ids of type 1,2,3,4,5,6,7 as well as many other columns What I’m trying to do is create different scenarios such as Contains transactions 1 only Contains 1,5 and 7 I’ve started off with a CTE called ALL_CONTRACTS that contains transaction ids of type 1,2,3…
Snowflake Regular Expression
I have this string in Snowflake column: I need to get names in this format regardless of the number of company names: “SpecTra, Signal Capital Partners”. In other words, I need to extract company names and concatenate them. I have tried this : and regexp_substr() function, but did not get the desi…
SQL find the maximum value
I have 2 tables: Customer table CustomerId FirstName LastName Country 1 Luís Gonçalves Brazil 2 Leonie Köhler Germany 3 François Tremblay Canada 4 Bjørn Hansen Norway 52 Emma Jones United Kingdom 53 Phil Hughes United Kingdom Invoice table InvoiceId CustomerId Total 1 2 1.98 2 4 3.96 3 8 5.94 140 52 23.76 369…
How can I get last 2 records from another table as columns
I have a table called products with this schema: And another table with fee change log with this schema: Is there anyway to get last 2 fee changes for each sku in one sql and not 2 rows for each sku, I want to have 2 new columns with old_fee_1, new _fee_1, old_fee_2, new_fee_2: Desired result: dbfiddle Answer…
Laravel onDelete(‘cascade’) does not work
My Tables: I am getting following errors when I try to delete support_categories Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails Answer You just write foreign key in wrong order. Instead of: Write: or you can use casadeOnDelete():
How to use between if value is not null?
Im trying to write query where im using valid_from and valid_to but those values can be null or one of them. This is my query, so i want to return data using if both valid_from and valid_to is null, or both not null or only valid_to is null. Any suggestion how can i achive that? Answer You could just explicit…