Skip to content

Tag: gaps-and-islands

Gap-and-island for more than time threshold

I have these tables: I want to investigate sessions of a user having time time difference between successive rows greater than 5 minutes, reporting: session and session mode number of sessions with the gap-and-island problem. My attempt: Error: Expected results: Note: dbfiddle. Answer The problem is we have two user_id and they’re not defined so you need to specifically choose

How to check consecutive values in rows in TSQL?

I have the following table: And I am looking for an output like this with a consecutive available two slots. Preferably the consecutive number should be a variable like @n = 2. How to achieve this in TSQL? Thanks! Update 8/3/2022: I undeleted this question as it is not fair to @lptr, as he/she has the correct solution, but didn’t

Continuous subset and ranking using sql

I have a dataset like below: Now, I need the output as below: For this purpose, I wrote a query but it is not giving me the desired sequence. My query is as below: It generates the output as below: But in the rank section I need something as below: So, what exactly am I doing wrong here? Here are

Grouping based on start date matching the previous row’s end date SQL

Hoping someone can help me out with this problem. I have the following sample dataset: MEM_ID CLM_ID ADM_DT DCHG_DT 1 111 01-01-2020 02-01-2020 1 112 03-01-2020 04-01-2020 1 113 04-01-2020 05-01-2020 1 114 06-01-2020 07-01-2020 2 211 01-01-2020 02-01-2020 2 212 05-01-2020 08-01-2020 3 311 02-01-2020 03-01-2020 3 312 03-01-2020 05-01-2020 3 313 05-01-2020 06-01-2020 3 314 07-01-2020 08-01-2020 I

How to remove and carry forward month prior when running balance decreases?

I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $7000 100000000 August 1,2021 12:00 AM $7000 100000000 July 1,2021 12:00 AM $6000 100000000 June 1,2021 12:00 AM $3000 100000000 May 1,2021 12:00 AM $20000 100000000 April 1,2021 12:00 AM $1000 100000000 March 1,2021 12:00 AM $0 200000000 September 1,2021 12:00 AM $7000 200000000 August 1,2021 12:00

SQL query to find gaps within a column of dates

I have a table with status and date for every day and I’m trying to find out when the statuses change and if there’s gaps within each status change / how many days were of a certain status. Expected output: Answer This is a type of gaps-and-islands problem. In this case, subtracting a sequential number from each day is probably