I have a dataset like the above. Basically it’s like a website session which I have a start + end activity, and in between there could be other unrelated records. I need to pull the records based on: I need to report all the “end” activity from the table In each “end” record, I n…
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 t…
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…
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-202…
Share price less than certain value for 6 consecutive business days or more in the past month
This is for MySQL 8.X database. My requirement is as follows: If a share price is less than a certain value for 6 consecutive business days or more in the past month then I need to inform the investor. There will not be any share price on holidays and weekends. So there will not be any data for these days.
Valid_from Valid_to from a full loaded table
There is a source table which loads the data full and monthly. The table looks like below example. Source table: pk code_paym code_terms etl_id 1 2 3 2020-08-01 1 2 3 2020-09-01 1 2 4 2020-10-01 1 2 4 2020-11-01 1 2 4 2020-12-01 1 2 4 2021-01-01 1 2 3 2021-02-01 1 2 3 2021-03-01 1 2 3 2021-04-01
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,202…
What SQL query can be used to limit continious periods by parameter value, and then to calculate datediff inside them?
I have a table of phone calls consisting of user_id, call_date, city, where city can be either A or B. It looks like this: user_id call_date city 1 2021-01-01 A 1 2021-01-02 B 1 2021-01-03 B 1 2021-01-05 B 1 2021-01-10 A 1 2021-01-12 B 1 2021-01-16 A 2 2021-01-17 A 2 2021-01-20 B 2 2021-01-22 B 2 2021-01-23 A
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 n…