Patron Saint of Lost Yaks

## Another bin-packaging algorithm using recursion and XML

This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE@Sample TABLE ( RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Expense SMALLMONEY NOT NULL ) -- Populate sample data INSERT@Sample ( Expense ) VALUES (12. Read more →

## The Excel Column Name assigment problem

Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel. Read more →

## Convert FILETIME to SYSTEM time using T-SQL

Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. Read more →

## Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx. The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week. Read more →

## Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/ Here is one suggestion to solve the problem. It's linear in time so it should be very fast. Read more →

## Collapse date range, with safety date range

This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx. The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. Read more →

## Create permutations, a simple way

This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple. 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 →

## Superfast sequence generators!

Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs. Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records. Read more →

## New article series going on by Itzik Ben-Gan

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed. First part is found here http://www. Read more →

## Median and weighted median

Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code. Joe had a feeling an elegant query existed but was not seeing it. Read more →

## Statistical SQLCLR for prediction analysis now in RC1

For more information about my SQLCLR, see http://www.developerworkshop.net/software.html The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later. //Peso 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 →

## Recursive Fibonacci number calculation

The answer to your question is "Yes, I am having a slow day today." ;WITHFibonacci(n, f, f1) AS ( SELECT CAST(1 AS BIGINT), CAST(0 AS BIGINT), CAST(1 AS BIGINT) UNION ALL SELECT n + 1, f + f1, f FROM Fibonacci WHERE n < 93 ) SELECTn, f AS Number FROMFibonacci 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 →

## Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do. Read more →

## Another running streaks algorithm

It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs. Read more →