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 months_between(dt,dt)
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 ValidTo column showing the last value within “the
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 work with this example) I want to, for example,
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? Answer You
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 selecting a max/min value: Here’s a relevant example