My end goal is to create islands of continuous enrollment days for each CLIENTID for a single sub-population: ‘Adult Expansion’ for calendar years 2019 and 2020. A CLIENTID can be associated with …
Tag: gaps-and-islands
Merge several rows into one if they have a gap less than a 5 seconds
I’m trying to find a SQL query that lets me to merge some rows into one from a table that have a gap less than 5 seconds. For example, I have a table like the following: So, I want to have a result like this: For John there are two rows that have a gap less than 5 seconds, so
Select and skip rows by date range with checking difference between them
I’m new in Teradata and i have a small sql problem, similar to this one below : source table A: output wanted: Explanation: 1 –> if c is different (between current row and the next one) so dt_f of the current row = dt of the next row – 1 day , the two rows are selected 2–> if mon…
Selecting first and last time stamps of a section
I have a MySQL database with a table like: I would like to select from this table the first and last timestamp for each value of each Batch_Number. I would like the table to look like: I am not sure how to select both, when the previous Batch_Num is different from the curent one, and also when the next one
Merge lines over timespan in SCD2 table
I’m having the following table sourced from a SCD2 table. From this source table, I have selected only a few columns, which reults in several lines looking exactly similar. I want to remove the unnecessary lines, those that contain the same data, and have the ValidFrom column showing the first value and…
Select on value change
I have a table that looks like this in a MySQL database: I would like to select all lines where the Batch_Num is different from the previous value including the first one: Is there a keyword maybe to access the previous line to compare the to the current line? Or some other way to compare a line to the line
Using SQL to group consecutive items that share a common status (dummy data included)
Given a table that has sometimes repeated statuses within a group (in this case “vehicles”), I want to consolidate those statuses into a single row and aggregate status_seconds. The data looks like this (I’ll include some TSQL below to select dummy data into a temp table to make it easy to w…
How to do a query on Oracle SQL to get time intervals, grouping by specific fields
I love a good challenge, but this one has been breaking my head for too long. 🙂 I’m trying to build a query to get dates intervals, grouping the information by one field. Let me try to explain it in a simple way. We have this table: I need to get the intervals a soldier spent on each ranking, so
TSQL – how can I sum values except absolutes
I would like to sum values in my table, except the ones that are absolute (field absolute, value = 1). If that’s the case, the summing should reset. Example: Regular SUM() would return (4+7+3=) 14. But in this example it should reset at value 7, which makes a sum of (7+3=) 10. How can I make this work? …
Is there an idiom for querying sequences in SQL?
I’m studying to interview for a job involving a lot of SQL. I’ve noticed a few exercises that revolve around returning values based on a sequence across rows, and I would love to know if there’s a standard way of doing this. Something akin to the subquery below I’ve found useful for se…