Finding Data Gaps
Quality data is a goal we all strive to attain. Nobody wants to have bad data. Yet, despite our best efforts, the problem of bad data has its way...
2019-08-05 (first published: 2019-07-11)
508 reads
Search results for "gaps and islands"
Your search for "gaps and islands" returned 100 results:
T-Sql Logical Query
Hi all - I was presented with a request today which I'm completely stumped on. Here it is; Given an employees table with the columns ID, FirstName, LastName, HireDate, and TerminationDate how would you calculate the longest period in days that the company has gone without hiring or firing anyone. Any help would be greatly […]
2013-03-05
49 reads
Count of consecutive years of participation (islands and gaps)
I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below. ID DATE ------ -------- 1 2000-05-03 1 2001-06-10 1 2002-04-02 1 2005-07-29 1 2010-12-15 4 2001-05-07 4 1999-08-01 4 2000-07-05 4 2001-08-01 9 2002-05-01 9 2000-04-02 […]
2015-06-29
1,676 reads
An Efficient Set-based Solution for Islands and Gaps
After reading a recent article on identifying islands and gaps in sequential numbers, one of our readers was inspired to develop a more efficient solution for fragmented data. New Author Goce Smilevski brings us his solution and supporting data to show how this can be done better.
2021-04-16 (first published: 2004-11-22)
11,051 reads
Merging date based on start and end date
I have tried to group/merge dates (Min/Max) but due to same data at line 1,2 and 4 , its not working as expected. Thank for looking into it. create table #Temp ( ID INT, Status_ID INT, StartDate DATE, EndDate DATE ) INSERT INTO #TEMP SELECT 101,1,'01/01/2020','01/28/2020' UNION ALL SELECT 101,1,'01/29/2020','01/31/2020' UNION ALL SELECT 101,5,'02/01/2020','02/29/2020' UNION […]
2020-07-24
969 reads
TSQL Script: Min, Max function
Hello All, I hope you are doing well. I have difficulties to write a sql script. May be you could help me. I have a table like this. And I want to have a result like this: I used the min and max function but it doesn't work. Do you have any idea […]
2020-12-15
177 reads
Calculating Gaps Between Overlapping Time Intervals in SQL
There are a number of real-life reporting tasks in SQL that require a 'gaps and islands' analysis. There are a number of techniques around that work, but finding ones that scale well makes for a tougher, but interesting, challenge.
2014-02-28
3,824 reads
Calculating Gaps Between Overlapping Time Intervals in SQL ...
Feb 14, 2014 ... There are a number of real-life reporting tasks in SQL that require a 'gaps and islands' analysis. There are a number of techniques around ...
Generating Test Data: Part 2 - Generating Sequential and Random Dates
Comments posted to this topic are about the item
2012-04-24
70 reads
Calculating Downtime in SQL
I am looking to find the downtime of my machines. All the information is being pulled from SQL views and tables. When the machines is ON it is =1. When it is off it is =0. Here is a sample of what it looks like queried. 2014-09-15 09:53:38.6730000 LimitSwitch 1 2014-09-15 10:17:30.7280000 LimitSwitch 0 I […]
2014-09-15
82 reads
Blogs
Attacking the Weakest Link
By Brian Kelley
When I look at a system and think about its security model, the first...
Migrate datetime data to datetimeoffset with AT TIME ZONE
By Will Assaf
I recently reviewed, worked on, and added a similar example to the DATETIMEOFFSET Microsoft...
The Comprehensive Guide to Mastering Your SQL DBA Skills
Database administrators (DBAs) are the backbone of data-driven organizations. If you're looking to break...
Forums
Multiple processes accessing the same table. some to write others to read / Lock
Dears, Hope this message finds you well We have a log table which is...
The Backup File Extension
Comments posted to this topic are about the item The Backup File Extension
Ingest and Retrieve data from an ADX Cluster Using Python
Comments posted to this topic are about the item Ingest and Retrieve data from...
Question of the Day
The Backup File Extension
I run this command in SQL Server 2022. What is the extension of the backup file?
BACKUP DATABASE HerdofTwo TO DISK = 'HerdOfTwo_20240501'See possible answers