Peter Larsson Blog

Patron Saint of Lost Yaks

Unsupported and/or undocumented features

In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic. In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that. Read more →

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 →

CHAR(0) is not that innocent you may think

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was declare@s varchar(100) set@s = 'xxxx yyyyy zzzzzz' SELECTREPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts. 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 →