New answers tagged

0

Try an approach like this. First identify the rows that have changed by using LAG. The do a running total on those change points. Once you know that you can do the Group By but include the groups. DROP TABLE IF EXISTS #Source CREATE TABLE #Source (RowNum_Helper INT, ItemID CHAR(1), Classification CHAR(1), Start_Date DATETIME2, End_Date DATETIME2) INSERT ...


0

To provide an answer to this problem, I did the following (all of the code below is on the fiddle here): CREATE TABLE test ( row_number INTEGER NOT NULL, code TEXT NOT NULL, from_date DATE NOT NULL, to_date DATE NULL ); Populated with: INSERT INTO test VALUES (1, 'A', '2021-09-07', NULL), (2, 'A', '2021-04-01', '2021-09-06'), (...


1

This is a classic gaps-and-islands situation. References https://stackoverflow.com/questions/8014577/group-by-and-aggregate-sequential-numeric-values Select longest continuous sequence Query Like in the referenced questions, I'm going to use the Tabibitosan method to separate our groups, then just select the first one. WITH start AS ( SELECT rn, code, ...


0

<TL;DR> Definitive answer based on criteria outlined below. The discussion below refers to minor differences in the criteria for the selection of records (don't include NULLs) and their ordering - I finally used the OP's ordering and not the one discussed in the analysis below this section. SELECT name, MAX(rn2) AS cnt, fv_sd, lv_sd FROM ( SELECT ...


1

WITH FIRST1 AS ( SELECT ID, NAME, DATE1, IS_FIRST, ROW_NUMBER() OVER (ORDER BY ID) AS RN FROM ( SELECT ID, NAME, DATE1, CASE WHEN ( LAG(NAME,1) OVER (ORDER BY ID) <> NAME OR LAG(NAME,1) OVER (ORDER BY ID) IS NULL ) AND LEAD(NAME,1) OVER (ORDER BY ID) = NAME THEN 1 ELSE ...


3

This is a classic gaps-and-islands problem. There are many solutions. A standard method is to define starting or ending points for each section, then use a windowed conditional COUNT to number the islands. Then you simply group by this grouping number. There are added complications here We need to check if the previous row is negative but this is not You ...


0

The table's primary key space is fragmented. As long as you're not likely to run out (I can't recall if the autoincrement values are limited to 32- or 64-bits), you should not care what the numerical values are. Auto-increment keys are guaranteed to be unique. Nothing more. They are not guaranteed to be contiguous or even that they will always increase!) ...


0

Given this table (and these gaps): CREATE TABLE dbo.TableWithGaps(id int IDENTITY(1,1), x char(1)); INSERT dbo.TableWithGaps(x) SELECT TOP (50000) 'x' FROM sys.all_columns AS c CROSS APPLY (SELECT TOP (100) * FROM sys.all_objects) AS o; DELETE dbo.TableWithGaps WHERE id BETWEEN 11 AND 1000; -- 990 DELETE dbo.TableWithGaps WHERE id BETWEEN ...


Top 50 recent answers are included