New answers tagged

3

To keep all the state information with the current row to avoid having conditions you can so something like: SELECT start, max(end) FROM ( SELECT CASE WHEN blog_id = @prev + 1 THEN @start:=@start ELSE @start:=blog_id END AS start, blog_id AS end, @prev:=blog_id FROM (SELECT @prev:=NULL, @start:=NULL) AS vars JOIN ...


0

@variables is the secret sauce. (Or "windowing functions" of MariaDB 10.0.2.) You know how to get the 'gaps' 3, 5, 9. One less than each is the "end" of an island and one more is the start of the next island. So, walk through the table of gaps, using an @variable to keep track of the 'previous' end, etc. SELECT @prev+1 AS 'start', `start`-1 AS ...


1

I was a little bit surprised that the Vladimir's cursor solution was so slow, so I also tried to optimize that version. I did confirm that using a cursor was very slow for me as well. However, at the cost of using undocumented functionality in SQL Server by appending to a variable while processing a rowset, I was able to create a simplified version of this ...


1

Discussion will follow the code. declare @Helper table( rn tinyint, dowInt tinyint, dowChar char(3)); insert @Helper values ( 1,1,'Sun'), ( 2,2,'Mon'), ( 3,3,'Tue'), ( 4,4,'Wed'), ( 5,5,'Thu'), ( 6,6,'Fri'), ( 7,7,'Sat'), ( 8,1,'Sun'), ( 9,2,'Mon'), (10,3,'Tue'), ...


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


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


2

For the sake of completeness, here is a two-pass gaps-and-islands approach that I tried myself before asking this question. As I was testing it on the real data I found few cases when it was producing incorrect results and fixed it. Here is the algorithm: Generate islands of consecutive dates (CTE_ContractDays, CTE_DailyRN, CTE_DailyIslands) and ...


1

If overlaps are only partial, (i.e., a range may partially overlap another, but no range is a subset of another range), I think the following query will do what you want: SELECT t1.office_type_id, t1.state_id, t1.district_id, t1.office_class, t1.term_end, MIN(t2.term_begin) next_begin FROM terms t1 JOIN terms t2 ON ...


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



Top 50 recent answers are included