New answers tagged

3

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

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


-3

WITH Grouped AS ( SELECT D.bank_id, grp = D.bank_id - ROW_NUMBER() OVER (ORDER BY D.bank_id) FROM banks AS D WHERE status = 1 ) SELECT MIN(Grouped.bank_id) AS start_id , MAX(Grouped.bank_id) AS end_id FROM Grouped GROUP BY Grouped.grp start_id end_id ----------- ----------- 1 6 8 14 16 ...


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


4

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


3

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


0

I would start with a query that finds the latest row in each group of similar rows that are less than 15 minutes apart, and picks the latest five of those: SELECT DISTINCT model, model_id, timestamp FROM changes c1 WHERE (model, model_id) NOT IN (SELECT model, model_id FROM changes c2 WHERE c2.timestamp > c1.timestamp AND c2.timestamp < ...



Top 50 recent answers are included