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
3 answers

SQL Window function to find days past due

I want calculate DPD (days past due) from loan list sorted by date. Every past Due date should re-counted. See example in attach . How can I calculate "Days past due" column ?
SergeyFido
  • 3
  • 3
-1
votes
2 answers

SQL Server SUM (add) prices of accessories to products

I need to sum up the prices of accessories to the main product. There is no link between the accessories and the correspondent product, however all accessories between two product belongs to the previous product (see side note). SQL Server…
user2463808
  • 179
  • 11
-1
votes
1 answer

How do I find all “gaps” with predefined minimal gap size with SQL?

I read a lot of good answers (here, here, here) about finding gaps, but I still can't figure out how to find gaps with a minimal predefined size. In my case gaps are entries with no name order by HE. I also need to find gaps starting at the…
dnace
  • 23
  • 2
-1
votes
2 answers

determine since date from record set in PostgreSQL

I have table customer details having three columns empid(integer),productid (integer) and purchasedate(date) having records like empid productid purchasedate 1153 67 2010-01-14 1153 67 2010-02-15 1153 67 2010-02-25 1153 …
Ranjit Chavan
  • 47
  • 1
  • 6
-1
votes
2 answers

TSQL getting the maximum and minimum date for each person and his number

I have a dataset like this: It is necessary to getting the minimum and maximum date, the person and his number from each frame: The code sample below: enter link description here Thanks a lot!
En Ngn
  • 29
  • 4
-1
votes
1 answer

SQL Server query need to pull data from group

I have below data and from highlighted groups I want to pull min datetime (Grayed out rows)from each group, but the data in the all columns are same except date. I used Min aggregate but I am missing date 2019-09-28-12.31.47.653829 row. Not sure how…
Ram_God
  • 47
  • 1
  • 4
-1
votes
2 answers

MySQL query to get same column data in two rows

I am new to MySQL. I have a database and I am facing issue to getting the data in the format below. Table is as below +----+------+-------+-+ | ID | ign | time | | +----+------+-------+-+ | 1 | NULL | 12:30 | | | 2 | 1 | 12:31 | | | 3 | NULL…
nXn
  • 904
  • 6
  • 13
-1
votes
1 answer

Student absent for 5 consecutive days excluding Holidays

I am using code igniter and attendance table as below: attendance ID timestamp student_id status 1 01-01-20 1 P 2 01-01-20 2 P 3 02-01-20 1 P …
user3734397
  • 1
  • 1
-1
votes
6 answers

SQl query to calculate number of active users at the end of everyday

I have three columns User_ID, New_Status and DATETIME. New_Status contains 0(inactive) and 1(active) for users. Every user starts from active status - ie. 1. Subsequently table stores their status and datetime at which they got…
LearningEveryday
  • 7
  • 1
  • 3
-1
votes
2 answers

Count Consecutive Numeric values in SQL

There are already many existing questions about this scenario, however I am unable to replicate the answers in my scenario. I have a following sample Data Set: ID Number | Values 754321 0 754321 0 754321 0 754321 0 754321…
user12720677
-1
votes
3 answers

SQL get the last time number was set to 1

I have a sql table similar to the below. I would like to get the last time it was changed from 0 to 1 and the last time it changed back to 0 (highlighted below for an example id). What I have tried is: select * from Table t1 join Table t2 on t1.id…
user1855165
  • 289
  • 1
  • 8
  • 22
-1
votes
2 answers

SQL Query to add row number that resets everytime the column value changes (SQL Server 2014)

Was looking through Row_number() function in SQL Server to increment the counter every time the value changes. The issue is that I need it to reset on a change such that the same value can reappear at a later stage and the row_number still resets…
Sonu
  • 11
  • 3
-1
votes
3 answers

SQL Server, Getting records from random date where not started with specific column value

I want to get data from one of my tables which has EventTime column that keeps DateTime and EventType column which has 1 and 5 as value. The result data should return sum of duration times between two event types group by NetworkNode and also should…
Mehdi
  • 499
  • 1
  • 7
  • 31
-1
votes
1 answer

How to find the difference between two timestamped rows whenever value changes between rows in MySQL

My Data Set looks like this: The Output given in column D is derived as follows: Output against index 2 : TimeStamp in Index 3 - TimeStamp in Index 2 Output against index 6 : TimeStamp in Index 10 - TimeStamp in Index 6 Output against index 12 :…
Sanjeev
  • 41
  • 5
-1
votes
1 answer

SQL - get device continous uptime

Device uptime time series table There is a device monitor table recording if a device is up (STATE 1) or down for each day. DEVICE_ID, STATE, DATE 1 0 2017-10-09 1 1 2017-10-10 1 1 2017-10-11 1 1 …
mon
  • 18,789
  • 22
  • 112
  • 205
1 2 3
…
91 92