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…
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 la…
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_n…
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 p…