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

Get duration of consecutive record views

I have a call log for my Widget sales staff. Each click in a customer record is logged. Staff may access the same customer account several times a day, so there could be dozens of consecutive clicks grouped together under the same recordID at…
a coder
  • 7,530
  • 20
  • 84
  • 131
0
votes
1 answer

MySQL - add zeroes to a time based query where no result found

I have a query that prints out times and averages. The only problem is - if nothing happened in the time period of the 6 weeks that I'm running this for - it skips right over it... I need that 15 minute period to have a NULL or zero in its place.…
brandoncluff
  • 303
  • 1
  • 5
  • 19
0
votes
1 answer

how to fetch number of records based on changing column value

I am having a LEAVE table as following EMPID LEAVEDT APPDT LEAVETYPE ------ ------- ------- --------- E1 10-3-13 5-3-13 CL E1 11-3-13 5-3-13 CL E1 12-3-13 5-3-13 …
achap patch
  • 11
0
votes
2 answers

Generate missing dates + Sql Server (SET BASED)

I have the following id eventid startdate enddate 1 1 2009-01-03 2009-01-05 1 2 2009-01-05 2009-01-09 1 3 2009-01-12 2009-01-15 How to generate the missing dates pertaining to every eventid? Edit: The missing gaps are to be find out…
priyanka.sarkar
  • 25,766
  • 43
  • 127
  • 173
0
votes
1 answer

SQL Server Membership Date Range

I am a newbie to SQL Server with a rather complex SQL task and none of the solutions I have found so far is fit for my problem below. I have a database table that currently holds membership joining information (extract below) with one line for each…
Babs
  • 195
  • 2
  • 3
  • 8
0
votes
1 answer

SQL Server 2008 finding missing rows

I have a huge DB and one table in particular is missing rows. I know this to be true but I need to find the gaps and show them. The primary key is the sequence number column. I need to show the actual missing sequence numbers. This table has…
KevinW
  • 13
  • 3
0
votes
2 answers

SQL Count Grouping by a sequence of numbers

i have a SQL table like this: id pNum ----- -------- 100 12 100 13 100 15 100 16 100 17 200 18 200 19 300 20 300 21 300 25 and i want to group by the id and the pNum sequences, and count the number of…
Thorgrim the Dwarf
  • 41
  • 1
  • 5
0
votes
3 answers

I require to create an SQL or PLSQL Query for merging and ordering data in a table

I am trying to create a SQL query that will help me get a proper ordered output from the below data. Data in table : Cust num Eff_Date Exp_date 1001 1234 10-01-2010 20-06-2010 1001 1234 …
user1928281
  • 25
  • 5
0
votes
0 answers

Show all date between two date range Using mysql

Possible Duplicate: MySQL how to fill missing dates in range? I have table with name "trip_data".There it is having four fields |trip_paramid|fuel_content|creation_time|vehicle_id I want to generate a report so that input parameters will be…
vmb
  • 2,878
  • 15
  • 60
  • 90
0
votes
1 answer

Cropping overlapping timespans by priority in SQL server

I have a large database table with timespans described by start and stop time. Easy timespan has a priority and the timespans might overlap each other. I need to process it so that overlaps will be removed. In case of an overlap, the span with a…
ANisus
  • 74,460
  • 29
  • 162
  • 158
0
votes
1 answer

Filling in missing dates DB2 SQL

My initial query looks like this: select process_date, count(*) batchCount from T1.log_comments order by process_date asc; I need to be able to do some quick analysis for weekends that are missing, but wanted to know if there was a quick way to…
avgvstvs
  • 6,196
  • 6
  • 43
  • 74
0
votes
2 answers

Using Inner Join to determine the interval between specific records and related events in a table

I have a simple table that is used to record events against specific visits: Describe Histories; +------------------+ | Field | +------------------+ | HistoryId | | VisitId | | Location | …
skyman
  • 2,255
  • 4
  • 32
  • 55
-1
votes
0 answers

Find a gap between date ranges in SQL Server

I have an invoice table that includes invoice dates (Date_Effective) per Company_ID, and I'm trying to essentially find those customers that, at one point or another, had a gap in invoicing of 2 years or more (for example--Customer ABC started…
burghman217
  • 13
  • 2
-1
votes
2 answers

How to find consecutive occurrences of data in a row by date?

The problem I am trying to solve is that I need to know how many times EmployeeId + TenantId (1A) has shown up on consecutive runs starting at a given date (the current run). I really only care if its greater than 1. My app runs on business days and…
Victorio Berra
  • 2,760
  • 2
  • 28
  • 53
-1
votes
2 answers

SQL Query counts to get latest records count only

I have table with userID, FY, clientID I want a SQL statement that gives me the latest interaction count with the client by the user. Example: In the below table I have User 1 who had interactions with client "10001" in 2024, 23, 22, after that in…
Rocking Star
  • 1
  • 1
1 2 3
…
91 92