Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links




Post Categories

January 2010 Blog Posts

Invitation to startup meeting for PASS Scania

PASS Scania is a new PASS chapter in Sweden which will promote the interest, networking and knowledge for professional SQL Server-users such as developers, DBA's and BI-specialist in Skåne. The Chapter also has a business alliance with an existing usergroup SQLUG.   All developers who use SQL Server platform in their work is welcome to participate in the seminars with start february 4th 2010. Both consultants and employees are welcome.   Membership and seminars are free and activities are mainly sponsored events.   First meeting is held in feburary where new members have the opportunity to present themself, we will present the agenda for...

posted @ Wednesday, January 27, 2010 4:09 PM | Feedback (1) | Filed Under [ Miscellaneous ]

Convert binary value to string value

With SQL Server 2008, we can easily use DECLARE @bin VARBINARY(MAX) SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 SELECT  CONVERT(VARCHAR(MAX), @bin, 2) But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values. So, here is how you can do the conversion with SQL Server 2005 by using XML. -- Prepare value DECLARE @bin VARBINARY(MAX) SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 -- Display the results SELECT @bin AS OriginalValue,         CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

posted @ Wednesday, January 27, 2010 9:38 AM | Feedback (8) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Get date from ISO week number

This function is just the opposite of this one, 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. Or you can use the function blogged here CREATE FUNCTION dbo.fnISOMonday (     @theYear SMALLINT,     @theWeek TINYINT ) RETURNS DATETIME AS BEGIN     RETURN  (                 SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)                 FROM    (                             SELECT  DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,                                     DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear                             FROM    (                                         SELECT  DATEADD(YEAR, @theYear - 1900, 3) AS Jan4                                         WHERE   @theYear BETWEEN 1900 AND 9999                                                ...

posted @ Tuesday, January 26, 2010 10:57 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem here Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes. -- Initialize and find the valid combinations DECLARE  @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils. DECLARE  @Source TABLE         (             room_nbr CHAR(2),             class_nbr CHAR(2),             recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED         ) INSERT      @Source             (                 room_nbr,                 class_nbr             ) SELECT      r.room_nbr,             c.class_nbr FROM        dbo.Rooms AS r INNER JOIN  dbo.Classes AS...

posted @ Saturday, January 23, 2010 1:31 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Collapse date range, with safety date range

This example is based on AdventureWorks database. More details about the task is found here The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds. DECLARE @Interval INT = 7 ;WITH cteSingle(ProductID, TransactionDate, recID) AS (     SELECT  ProductID,             TransactionDate,             ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID     FROM    Production.TransactionHistory ), cteLower(ProductID, StartDate, recID) AS (     SELECT      s.ProductID,                 s.TransactionDate AS EndDate,                 ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID     FROM       ...

posted @ Friday, January 08, 2010 1:07 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

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. DECLARE @Word VARCHAR(10) = 'Peter' ;WITH cteYak(Word, Letters) AS (     SELECT  CAST(SUBSTRING(@Word, Number, 1) AS VARCHAR(10)) AS Word,             STUFF(@Word, Number, 1, '') AS Letters     FROM    dbo.TallyNumbers     WHERE   Number BETWEEN 1 AND LEN(@Word)     UNION ALL     SELECT      CAST(Word + SUBSTRING(y.Letters, n.Number, 1) AS VARCHAR(10)) AS Word,                 STUFF(y.Letters, n.Number, 1, '') AS Letters     FROM        cteYak AS y     INNER JOIN  dbo.TallyNumbers AS n ON n.Number BETWEEN 1 AND LEN(y.Letters) ) SELECT  DISTINCT         Word FROM    cteYak WHERE   LEN(Word) = LEN(@Word)  

posted @ Saturday, January 02, 2010 8:15 AM | Feedback (11) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET