Tag: gaps-and-islands

Calculate interval between boolean column change

gaps-and-islands intervals postgresql sql window-functions

I have a table with measurements of weather here is a simplified version of it: and I need to calculate how much time is been sunny. I know I need to use window functions but I’m stuck. Because I need this interval to be calculated in some range for example for last day. I have been able to make a

Recursive CTE have performace issue, need suggestion to optimize query

gaps-and-islands sql sql-server tsql window-functions

I wanted to get TOP 5 records from log table where “Approve date” is changed like NULL to value and vice versa. Date value is doesn’t matter, but order matters. In this case I want first record and 5th record (Someone approved the data that’s why a value),then 7th record value is null someone rejected it. I tried using recursive

Number based on condition

gaps-and-islands sql sql-server tsql

I’m trying to generate a number based on a condition. When there is yes in column ‘Stop’ in the partition of a Client ordered by Start_Date, the Dense Rank has to start over. So I tried several things but it’s stil not what I want. My table with current number and expected number The query I used so far: This

Get Max And Min dates for consecutive values in T-SQL

date-arithmetic gaps-and-islands sql sql-server window-functions

I have a log table like below and want to simplfy it by getting min start date and max end date for consecutive Status values for each Id. I tried many window function combinations but no luck. This is what I have: This is what want to see: Answer This is a typical gaps-and-islands problem. You want to aggregate groups

Create query to get count of uncompleted calls

gaps-and-islands join mysql sql window-functions

There is table waiter_log as Call with call-id ‘f27de4f’ started in 9010 and finished in 9003 because there is a record with proceed_wait = 0 for call-id=’f27de4f’ Call with call-id ‘asdf231′ started in 9010, still proceed in 9002 and not finished yet because there is no record with proceed_wait = 0 for call-id=’asdf231’ Similarly for call with call-id ‘rete125’ there

SQL to find start and end date for an ID

gaps-and-islands oracle sql

I have a table with ID, start_date and end_date columns. Table: I want to write a query to get the following output: Output: Answer You can do a cumulative sum to solve this variation of the gaps-and-island problem: Demo on DB Fiddle: ID | START_DATE | END_DATE -: | :——— | :——— 1 | 01/01/2017 | 01/01/2020 2 | 01/01/2016

Row lumping, cycle dates

cycle date gaps-and-islands oracle sql

I want to look at the lead type and if that type is the same for that row then merge in those dates to fit within one row. I have the below table: Expected Results: How can I get my output to look like the expected results? I have tested withlead lag rank and case expression but nothing worthy of

Find all rows in between a set in PostgreSQL

gaps-and-islands php postgresql sql window-functions

I have a table named tc_fuel that is receiving all the fuel related data from GPS vehicles, and I want to get the last “Tank Usage” to calculate MPG for a whole tank, but the readings when the tank is …

Merge groups of consecutive rows in T-SQL and sum values from each group

azure-sql-data-warehouse gaps-and-islands sql sql-server tsql

Updated 10/08/2019: @Gordon Linoff: I have tried to applied your solution but I realized that is not working as expected. I have added an example with expected result with comments here (https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1b486476d6aeab25997f25e66ee455e9) and I would be grateful if you could help me. — I have a table of transactions with schema: Here are some row examples: What I want is

Gaps and Islands Months only

gaps-and-islands oracle oracle11g sql

I am working with a data set of clients and their date records. I am trying to apply a gaps and island problem only using MONTHS,( currently var char ‘YYYYMM’). I need to take individual records and …