Latest Entries
Uncategorized

Totalling / Grouping by rolling 24 hour window.

https://stackoverflow.com/questions/45446478/sql-server-windowing-24-hour-window/45537274#45537274 I wanted to flag up transactions when the sum total of column exceeded a certain amount during a cycling period. You could easily group the data by DATE (CAST DateCol as DATE). However, this is not a true 24 cycling window. Using the Sales.SalesOrderHeader table in AdventureWorks, I wrote the query below to achieve … Continue reading

Uncategorized

Find gap ranges in IDENTITY values

Copied from https://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ SET NOCOUNT ON GO — Prepare a sample table CREATE TABLE GapsTest ( ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Name varchar(50) NULL ) GO — Insert 100k rows of dummy data INSERT INTO GapsTest (Name) VALUES (‘TheFirstSQL’) GO 100000 — Delete some rows to create gaps in the identity … Continue reading