Tag Info

Hot answers tagged

14

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 ...


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

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 ...


8

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() ...


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> ...


7

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 - ...


6

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, ...


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

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 ...


5

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 ...


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 ...


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.


3

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 ...


3

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 ...


3

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(); ...


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 = ...


3

You can store pre-calculated gaps, and use constraints to make sure that your pre-calcualted data is always up-to-date: Here is the table and the first interval CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL, IntValue INT NOT NULL, StartedAt DATETIME NOT NULL, FinishedAt DATETIME NOT NULL, PreviousFinishedAt DATETIME NULL, ...


3

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, ...


2

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 ...


2

Here is a solution which runs is 4 seconds. WITH cteRaw(ts, type, e, s) AS ( SELECT StartedAt, 1 AS type, NULL, ROW_NUMBER() OVER (ORDER BY StartedAt) FROM dbo.Tasks UNION ALL SELECT FinishedAt, -1 AS type, ROW_NUMBER() OVER (ORDER BY FinishedAt), NULL FROM dbo.Tasks ), ...


2

Recursive CTEs are perfect for this job. You don't need cursors in this case, and I bet a CTE will perform much better than any cursor- or loop-based approach to solving this problem. The following query gives you exactly what you need. I tested it on SQL Server 2008, but if you ignore the setup block and replace @table with the name of your target ...


2

SELECT person, max(ct) AS max_ct FROM ( SELECT person, count(*) AS ct FROM ( SELECT person, result , row_number() OVER (PARTITION BY person ORDER BY time) - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp FROM tablex ) sub1 GROUP BY person, result, grp ) sub2 GROUP BY person ORDER ...


2

To see what's missing, you can compare to a complete list. There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums. Helper table to provide numbers from 0-9: CREATE TABLE int10 (i INT); INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Building ...


2

First, we combine intervals that overlap to find all the contiguous "islands" of the intervals: with c as ( select *, max(end_date) over (order by start_date rows between unbounded preceding and 1 preceding) as previous_max from my_table ) select start_date, ...


1

This query returns all gaps in the data that overlap with the given time range. Also covers leading, trailing or total (covering) gaps: WITH input(t1, t2) AS (SELECT '2015-04-02 22:00'::timestamp , '2015-04-02 23:00'::timestamp) -- t1 < t2 , gap AS ( SELECT max(end_date) OVER (ORDER BY start_date ROWS ...


1

This counts ranges of at least 3 days with minutes_asleep = 0. A continuous range of 5 days still counts as 1. A continuous range of 6 days counts as 2. Etc. Ignoring all other entries where minutes_asleep is different. SELECT sum(ct) AS total_count FROM ( SELECT (count(*)/3)::int AS ct -- integer division truncates as desired + see below FROM ( ...


1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years. ;WITH x AS ( SELECT TOP (366) n = number ...


1

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev. Please note this is intended as a ...


1

Since you are in 2012 version, here's another way to solve this gaps-and-islands problem that uses the new LAG() function: ; WITH seq AS ( SELECT SiteIDNumber, SequenceNumber, LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber ORDER BY SequenceNumber), ...



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