Peter Larsson Blog

Patron Saint of Lost Yaks

Alternative approach to calculate most used time interval

In the past I have given the advice to break down all date intervals into the smallest part, most often minutes. Then OP should group by the minute. -- Prepare sample data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CreateDate DATETIME, DeleteDate DATETIME ) -- Populate sample data INSERT@Data ( CreateDate, DeleteDate ) SELECT'2009-01-14 22:33', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:33', '2009-01-14 22:33' UNION ALL SELECT'2009-01-14 22:34', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:35', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:35', '2009-01-14 22:36' UNION ALL SELECT'2009-01-14 22:37', '2009-01-14 22:37' UNION ALL SELECT'2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT'2009-01-14 22:38', '2009-01-14 22:38' UNION ALL SELECT'2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT'2009-01-14 22:41', '2009-01-14 22:41' UNION ALL SELECT'2009-01-14 22:43', '2009-01-14 22:44' UNION ALL SELECT'2009-01-14 22:52', '2009-01-14 22:52' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:56', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:57', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:58' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:59' UNION ALL SELECT'2009-01-14 22:59', '2009-01-14 22:59' DECLARE@From INT, @To INT SELECT@From = MIN(DATEDIFF(MINUTE, 0, CreateDate)), @To = MAX(DATEDIFF(MINUTE, 0, DeleteDate)) FROM@Data SELECTw. Read more →

Easy sorted numeric pivoting with maximum three columns

-- Prepare sample data DECLARE@Sample TABLE ( ID INT, col INT )   INSERT@Sample SELECT0, 1 UNION ALL SELECT0, 1 UNION ALL SELECT0, 2 UNION ALL SELECT1, 1 UNION ALL SELECT1, 2 UNION ALL SELECT1, 3 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT3, 6 UNION ALL SELECT3, 6 UNION ALL SELECT5, 8 UNION ALL SELECT5, 9 UNION ALL SELECT4, 7   -- Pivot the source data SELECTID, MIN(col) AS col1, CASE COUNT(*) WHEN 1 THEN NULL WHEN 2 THEN MAX(col) ELSE SUM(col) - MIN(col) - MAX(col) END AS col2, CASE COUNT(*) WHEN 3 THEN MAX(col) ELSE NULL END AS col3 FROM@Sample GROUPBY ID ORDERBY ID Legacy Comments dineshrajan 2010-08-10 re: Easy sorted numeric pivoting with maximum three columns Good Post. Read more →

Stripping out all non-numeric characters from a string

DECLARE@Value NVARCHAR(200) SET@Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE@Value LIKE '%[^0-9]%' SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT@Value Legacy Comments Uri Dimant 2008-11-12 re: Stripping out all non-numeric characters from a string declare @string varchar(200) Read more →

Getting date or time only from a Datetime value

SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly Legacy Comments Bill Curnow 2008-11-12 re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime, Read more →

Simple FAQ search algorithm

-- Prepare sample data DECLARE@Keywords TABLE ( FaqID INT, Keyword VARCHAR(200) ) INSERT@Keywords SELECT1, 'help' UNION ALL SELECT1, 'resolve' UNION ALL SELECT1, 'issue' UNION ALL SELECT2, 'Red Herring' DECLARE@Faq TABLE ( FaqID INT, Question VARCHAR(MAX), Answer VARCHAR(MAX) ) INSERT@Faq SELECT1, 'This is stupid question', 'This is a stupid answer' DECLARE@Question AS VARCHAR(200) -- Prepare user supplied parameter SET@Question = 'How can we help resolve your issue' -- Show the expected result SELECTf. Read more →

How to sum up an unknown number of records

– Initialize the search parameter DECLARE@WantedValue INT SET@WantedValue = 221 – Stage the source data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MaxItems INT, CurrentItems INT DEFAULT 0, FaceValue INT, BestUnder INT DEFAULT 0, BestOver INT DEFAULT 1 ) – Aggregate the source data INSERT@Data ( MaxItems, FaceValue ) SELECTCOUNT(), Qty FROM( SELECT 899 AS Qty UNIONALL SELECT 100 UNION ALL SELECT 95 UNIONALL SELECT 50 UNION ALL SELECT 55 UNION ALL SELECT 40 UNION ALL SELECT 5 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUPBY Qty ORDERBY Qty DESC – Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT – If exact single wanted sum, select that item! Read more →

Another sequencing algorithm

This problem originated here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y and I post the solution here for two reasons. 1) The forum above does not support code tags 2) The common interest is high enough -- Prepare sample data DECLARE@Sample TABLE (  HoleID CHAR(8),  mFrom SMALLMONEY,  mTo SMALLMONEY,  Result SMALLMONEY,  PRIMARY KEY CLUSTERED  (  HoleID,  mFrom  ),  Seq INT  ) INSERT @Sample  (  HoleID,  mFrom,  mTo,  Result  ) SELECT'TWDD0004',1,2,0. Read more →

Horizontal partitioning, Enterprise style

CREATEPARTITION FUNCTION pfOrderDate ( DATETIME ) ASRANGE RIGHT FOR VALUES ( '20000101', '20010101', '20020101' ) GO   CREATEPARTITION SCHEME psYak ASPARTITION pfOrderDate ALLTO ([PRIMARY]) GO   CREATETABLE Orders ( OrderID INT NOT NULL, CustomerID VARCHAR(15) NOT NULL, OrderDate DATETIME NOT NULL ) ONpsYak(OrderDate) GO   CREATECLUSTERED INDEX IX_OrderID ONOrders ( OrderID ) CREATENONCLUSTERED INDEX IX_OrderDate ONOrders ( OrderDate ) INCLUDE( OrderID, CustomerID ) GO INSERTOrders ( OrderID, CustomerID, OrderDate ) SELECT1, 'Peso', '20011225' UNIONALL SELECT2, 'Jennie', '20020314' SELECTOrderID, OrderDate FROMOrders WHEREOrderDate = '20011225'   SELECTOrderID, OrderDate FROMOrders WHEREOrderID = 1   DROPTABLE Orders DROPPARTITION SCHEME psYak DROPPARTITION FUNCTION pfOrderDate   Read more →

Efficient pagination for large set of data?

This is what I pondered about today. Maybe I also will have some time to test it.  CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www. Read more →

Sum up a tree hierachy in SQL Server 2005

-- Prepare sample data DECLARE@Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT@Accounts SELECT'100-000-000', NULLUNION ALL SELECT'100-001-000', '100-000-000' UNIONALL SELECT'100-002-000', '100-000-000' UNIONALL SELECT'100-002-001', '100-002-000' UNIONALL SELECT'100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT@Transactions SELECT'100-001-000', 1000. Read more →