Hot answers tagged

16

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number WITH DATA(C) AS ( SELECT 724 UNION ALL SELECT 727 UNION ALL SELECT 728 UNION ALL SELECT 729 UNION ALL SELECT 735 UNION ALL SELECT 737 UNION ALL SELECT 743 UNION ALL SELECT 744 UNION ...


15

General solution for this class of problems To get the longest sequence (1 result, longest of all, arbitrary pick if there are ties): SELECT race_id, car_type, count(*) AS seq_len FROM ( SELECT *, count(step OR NULL) OVER (ORDER BY race_id, car_type, lap_no) AS grp FROM ( SELECT *, (lag(lap_no) OVER (PARTITION BY race_id, car_type ORDER BY ...


12

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set: WITH partitioned AS ( SELECT *, number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp FROM atable WHERE status = 'FREE' ), counted AS ( SELECT *, COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt FROM partitioned ) SELECT id_set, ...


9

You can use outer apply the get the lead and lag values and in the column list you can use datediff to see if the gap is something other than one day. select T1.ID, case when datediff(day, Lag.Start, T1.Start) = 1 then T1.Start else dateadd(hour, 9, T1.Start) end as Start, case when datediff(day, T1.Start, ...


9

Given the following sample data: DECLARE @Data AS table ( data integer PRIMARY KEY ); INSERT @Data (data) VALUES (1), (2), (3), (6), (7), (15), (16); One way to achieve the result you are after is: WITH Grouped AS ( -- Identify groups SELECT D.data, grp = D.data - ROW_NUMBER() ...


9

This type of requirement comes under the banner of "gaps and islands". A popular approach is WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY ItemId ORDER BY DateOfChange) - DENSE_RANK() OVER (PARTITION BY ItemId, Status ORDER BY DateOfChange) AS Grp FROM ItemTable) SELECT ItemId, Status, ...


9

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


8

A simple and fast variant: SELECT min(number) AS first_number, count(*) AS ct_free FROM ( SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp FROM tbl WHERE status = 'FREE' ) x GROUP BY grp HAVING count(*) >= 3 -- minimum length of sequence only goes here ORDER BY grp LIMIT 1; Requires a gapless ...


8

This is a fairly generic way to do this. Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed: SELECT number FROM mytable m CROSS JOIN (SELECT 3 AS consec) x WHERE EXISTS (SELECT 1 FROM mytable WHERE number = m.number - x....


8

To find gaps in a number range: Test table and data: mysql> CREATE TABLE wp_blogs -> ( -> blog_id INTEGER -> ); mysql> insert into wp_blogs values(1); mysql> insert into wp_blogs values(2); mysql> insert into wp_blogs values(4); mysql> insert into wp_blogs values(6); mysql> insert into wp_blogs values(7); mysql> ...


8

There are a lot of questions and articles about packing time intervals. For example, Packing Intervals by Itzik Ben-Gan. You can pack your intervals for the given user. Once packed, there will be no overlaps, so you can simply sum up the durations of packed intervals. If your intervals are dates without times, I'd use a Calendar table. This table simply ...


7

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases. The easiest workaround is to use a function that calls the ...


7

I strongly agree that a Numbers and a Calendar table are very useful and if this problem can be simplified a lot with a Calendar table. I'll suggest another solution though (that doesn't need either a calendar table or windowed aggregates - as some of the answers from the linked post by Itzik do). It may not be the most efficient in all cases (or may be the ...


6

You can group the data using a trick using row number - row number partitioned by status. That will create the same number for rows with the same status for a range of dates. This just takes the rows ordered by entry_date and status, but you might want to do something better for the entries on the same day: select ID, status, min(entry_date) as ...


6

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET DATEFIRST 1 -- Make Monday weekday=1 DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the ...


5

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() ...


5

Use a different method. For a start, don't populate a temporary table with 2.7M rows - that's not going to want to return in under ten seconds. You could use a CTE instead, and that might work much better. WITH taps as ( SELECT RowID = ROW_NUMBER() OVER (ORDER BY DeviceESN, CreatedDateUTC, [Counter]), DeviceESN, TapDateUTC, CreatedDateUTC, [Counter] ...


5

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this: DECLARE @columns TABLE ( ID INT IDENTITY NOT NULL, NAME NVARCHAR(128) NOT NULL ); Then, using whatever method you have; split the string and populate ...


5

create table tbl (lap_no int, car_type text, race_id int); insert into tbl values (1,'red',1),(2,'red',1),(3,'red',1),(4,'red',1), (1,'blue',1),(5,'red',1),(2,'blue',1),(1,'green',1); select car_type, race_id, sum(case when lap_no=(prev+1) then 1 else 0 end)+1 seq_len from ( select *, lag(lap_no) over (partition by car_type,...


5

This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions Here is how the query works: YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format. then ROW_NUMBER() ...


5

This first query creates different Start Date and End Date ranges with no overlaps. Note: Your sample(id=0) is mixed with a sample from Ypercube (id=1) This solution may not scale well with huge amount of data for each id or huge number of id. This has the advantage of not requiring a number table. With large dataset, a number table will very likely give ...


5

Not exactly what you are looking for but could perhaps be of interest to you. The query creates weeks with a comma separated string for the days used in each week. It then finds the islands of consecutive weeks that uses the same pattern in Weekdays. with Weeks as ( select T.*, row_number() over(partition by T.ContractID, T.WeekDays order by T....


4

The following C# code solves the problem: var connString = "Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=Benchmarks;"; var stopWatch = new Stopwatch(); stopWatch.Start(); using (var conn = new SqlConnection(connString)) { conn.Open(); var command = conn.CreateCommand(); ...


4

This will return only the first of the 3 numbers. It does not require that the values of number are consecutive. Tested at SQL-Fiddle: WITH cte3 AS ( SELECT *, COUNT(CASE WHEN status = 'FREE' THEN 1 END) OVER (PARTITION BY id_set ORDER BY number ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cnt FROM atable ) SELECT ...


4

If you create a clustered index on createdDateUTC the query Rob has given could be really fast. Can you try that? The other option would be to add an artificial unique tapDetailID column with a clustered index and use that instead.


4

And a 1 second solution... ;WITH cteSource(StartedAt, FinishedAt) AS ( SELECT s.StartedAt, e.FinishedAt FROM ( SELECT StartedAt, ROW_NUMBER() OVER (ORDER BY StartedAt) AS rn FROM dbo.Tasks ) AS s INNER JOIN ( SELECT FinishedAt, ...


4

I couldn't understand the logic behind grouping weeks with gaps, or weeks with weekends (e.g. when there are two consecutive weeks with a weekend, which week does the weekend go to?). The following query produces the desired output except that it only groups consecutive weekdays, and groups weeks Sun-Sat (rather than Mon-Sun). Whilst not exactly what you ...


4

I ended up with an approach that yields the optimal solution in this case and I think will do well in general. The solution is quite lengthy, however, so it would be interesting to see if someone else has a different approach that is more concise. Here is a script that contains the full solution. And here is an outline of the algorithm: Pivot the data ...


3

I think I have exhausted the limits of my knowledge in SQL server on this one.... For finding a gap in SQL server (what the C# code does), and you don't care about starting or ending gaps (those before the first start, or after the last finish), then the following query (or variants) is the fastest I could find: SELECT e.FinishedAt as GapStart, s.StartedAt ...


3

This isn't an Update statement, but it does meet the other requirements of the question. MERGE INTO t1 b USING (SELECT letter, number1, number2, Row_Number() OVER (PARTITION BY DECODE(letter,'a',1,'d',1,'e',1,0) ORDER BY number1) SectionRow FROM t1) a ON (a.number1 = b.number1) WHEN MATCHED THEN UPDATE SET b.letter='x', b.number2 = ...



Only top voted, non community-wiki answers of a minimum length are eligible