Peter Larsson Blog

Patron Saint of Lost Yaks

Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. Read more →

NOT IN not equal to <>

Hi! I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum. The original poster was amazed that Col1 <> 'Some value' didn't return same records as Col1 NOT IN ('Some value') See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting. Read more →

SQLCLR aggregate function

Phew! Now it's finally done. I haven't coded outside SQL Server since 2007 and that was with VB version 6.0. Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. Read more →

Third running streak

declare@t table (Id int, dt datetime, value int) setdateformat 'dmy' insertinto @t select1, '10/12/2008', 10 union all select1, '11/12/2008', 10 union all select1, '12/12/2008', 10 union all select1, '13/12/2008', 9 union all select1, '14/12/2008', 10 union all select1, '15/12/2008', 10 union all select1, '16/12/2008', 10 union all select1, '17/12/2008', 10 union all select2, '05/03/2008', 8 union all select2, '06/03/2008', 6 union all select2, '07/03/2008', 8 union all select2, '08/03/2008', 8 union all select2, '09/03/2008', 8 union all select2, '20/03/2008', 8 SELECTId, MIN(dt) AS Startdt, MAX(dt) AS Enddt, MIN(value) AS Value FROM( SELECT Id, dt, value, ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID, ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID, ROW_NUMBER() OVER (ORDER BY dt) AS colID FROM @t ) AS d GROUPBY Id, grpID - recID, grpID - colID ORDERBY id, MIN(dt) Read more →

Complement of ISNUMERIC function

Today I was involved in an interesting topic about how to check if a text string really is integer or not. This is what I finally suggested. CREATEFUNCTION dbo.fnIsINT ( @Data NVARCHAR(11) ) RETURNSINT AS BEGIN RETURN CASE WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN @Data IN('-', '+') THEN NULL WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL ELSE CAST(@Data AS INT) END END Read more →