Stack Overflow
Stack Overflow

Questions tagged [gaps-and-islands]

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

1372 questions
-1
votes
2 answers

Select start and end dates for changing values in SQL

I have a database with accounts and historical status changes select Date, Account, OldStatus, NewStatus from HistoricalCodes order by Account,…
Max
  • 11
  • 1
  • 2
-1
votes
2 answers

SQL - timeline based queries

I have a table of events which has: user_id event_name event_time There are event names of types: meeting_started, meeting_ended, email_sent I want to create a query that counts the number of times an email has been send during a meeting. UPDATE:…
user2101699
  • 207
  • 3
  • 16
-1
votes
2 answers

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…
Pedro
  • 3
  • 2
-1
votes
2 answers

SQL Server: loop once a month value to the whole month

I have a table that gets one value of only one day in each month. I want to duplicate that value to the whole month until a new value shows up. the result will be a table with data for each day of the month based on the last known value. Can someone…
Tzachi Dvir
  • 1
  • 2
-1
votes
1 answer

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: Date Value Absolute 1-1-2020 4 0 1-2-2020 7 …
Ripper7801
  • 11
  • 2
-1
votes
1 answer

Using lag to access previous row value in mysql

I have a datetime columns, that are fromDate and toDate. I am trying to figure out if the interval is continuos. This is how my table looks this: I would like to get the following results with 3 intervals: fromDate | toDate 2020-05-23…
Bob
  • 8,392
  • 12
  • 55
  • 96
-1
votes
3 answers

find at least 2 consecutive items based on date ranges

there are lot of solutions of similar question but based only one date column. I would like to know maybe better solution for this to solve, I am attaching my solution but I find it a little bit complicated if you know better approach to this please…
Pato
  • 153
  • 6
-1
votes
2 answers

group orders based on crossing date ranges

I need to group order together with crossing their date ranges only scenario A. order 1, 1.3.2020-30.6.2020 order 2, 1.5.2020-31.8.2020 order 3, 31.7.2020-31.10.2020 order 4, 31.7.2020-31.12.2020 so the output should be order 1, order 2 order 2,…
Pato
  • 153
  • 6
-1
votes
2 answers

Group sequential repeated values sqlite

I have data that repeated sequentially.. A A A B B B A A A I need to group them like this A B A What is the best approach to do so using sqlite?
mo5br
  • 19
  • 7
-1
votes
1 answer

SQL Query to find the activity time of each user

How can we use this data to calculate the activity of each user in each period? user_id date status 101 2018-01-1 1 101 2018-01-2 0 101 2018-01-3 1 101 2018-01-4 …
alireza na
  • 15
  • 6
-1
votes
2 answers

SQL Query to transfer the data to another table based on filter

Currently I have the data in a table that look like below: #Tbl_A Id Customer_Id Indicator 1 912538132 1 2 912538132 2 3 912538132 3 4 912538132 1 5 912538132 1 6 912538132 2 7 912538132 …
venugopal
  • 79
  • 10
-1
votes
3 answers

select the rows based on unique column of each group from the table

---------------- c_id s_id p_id (customer_id, service_id, provider_id) ---- ---- ------ 1, 1, 1 - not elegible to select as next entry has same p_id 1, 2, 1 1, 3, 3 2, 1, 1 2, 2, 2 2, 3, …
Nagendra Shanbhag
  • 33
  • 4
-1
votes
2 answers

How to calculate this situation SQL?

I need to help query situation for calculate situation I don't know how to query situation. Please any help me for continue this project and you can guide me and example for me This is a sample of the data I have: Date Usage …
New Claffly
  • 25
  • 1
  • 5
-1
votes
1 answer

How do I mark rows as longterm illness using T-SQL?

I have a challenge. Our business wants to know if an employees sick day is long term. So if illness is part of 30 concurrent days, all 30 or more records should be marked as [long term illness] = True Sick days are only Monday - Friday I have a…
Tom Brox
  • 1
  • 2
-1
votes
1 answer

SQL how to convert row with date range to many rows with date range with gaps based on a data column

I want to convert data rows to date ranges in sql based on a column. Below is the sample data: Current Data FROMDATE TODATE Data 1/01/2010 31/10/2010 100 1/01/2011 31/12/2011 50 1/01/2012 31/12/2012 50 1/01/2013 31/12/2013 …
Ruhi
  • 3
  • 2
1 2 3
…
91 92