I am new to SQL Server and trying to do some operations Sample data: Amount | BillID ——-+——- 500 | 10009 500 | 1492 350 | 15892 222 | 15596 899 | 20566 350 | 9566 How can …
Tag: window-functions
Single-row subquery returns more than one row BUT with one row
“CityJail” schema. I am supposed to find each appeal that has a less than the average number of days between the filing and hearing dates. Here is my code: I get an error: ORA-01427: single-row subquery returns more than one row 01427. 00000 – “single-row subquery returns more than one row” *Cause: *Action: Finding the average works fine by itself:
BigQuery get row above empty column
Data: What would be the best way to get the row right above the empty cell? For example, I’d like to filter out row 1 and 3 based on the fact that column_b in row 2 and 4 are empty (in this example). A simple where statement does not work unfortunately, but how would it be possible to get the
How to get a continuous group of id
I’d like to increment an id by group, for each variation of an attribute with only 2 values. like this : result wanted I tried dense_rank() with window function but i only gives me 1 or 2 in grp column Answer You can use window functions to solve this gaps and islands problem. Her is an approch using lag() and
window functions and grouping – how to propagate data from row 1 through row 3 in a partition?
I have this query below: select Contact.IndividualID, Contact.IndividualID as ContactId, Contact.CaseNumber as CaseID, [Case].ProgramCode as Benefit, Contact.Email as EmailAddress, …
Cumulative balance across two tables
I want to get data from 2 tables ordering them by date: to get the cumulative balance for the customer The 2 tables that I want to get data from are my tables: transfers & trans_payments transfers:…
Pivot table in SQL Server 2016
I am having problem with pivoting a table in SQL Server 2016. Consider the following data/query WITH DATA1 AS ( SELECT 123 cid, ‘test’ cname, ‘2020-03-17’ dt, ‘BELLE’ fc, 3782703 mn union …
Rolling sum based on date (when dates are missing)
You may be aware of rolling the results of an aggregate over a specific number of preceding rows. I.e.: how many hot dogs did I eat over the last 7 days SELECT HotDogCount, DateKey, …
Find first available value that doesn’t exist
I want to create table for book chapters where pk will be book_id and chapter_internal_number. I’m not sure how find next free chapter_internal_number value for new chapter insert (chapter can be deleted and it’s chapter_internal_number value should be reused). How to find first chapter_internal_number avaiable value for book? Avaiable value is next value that doesn’t exist in ASC order. Table
SQL Getting row number only when the value is different from all previous values
I want the count adding one only when the value has not been show before. The base table is: The goal is: I am thinking to compare the current row with all previous rows, but I have difficulty to call all previous rows. Thank you! Answer Several different ways to accomplish this. I guess you’ll get to pick one you