Sometimes, when writing TSQL code in functions or procedures, it is tempting to do iterations, or even worse, a cursor, when it isn’t really necessary. Cursors and iterations are both renowned for slowing down Transact SQL code. SQL Server just isn’t designed for it.<\/p>\n
However, there is usually a way to do such operations in a set-based way. If you do so, then your routines will run a lot faster, with speed at least doubling. There are a lot of tricks to turning a problem that seems to require an iterative approach into a set-based operation, and we wish we could claim we’d invented one of them. Probably the most useful technique involves that apparently useless entity, the ‘helper’ table. This workshop will concentrate on this, because it is probably the most widely used.<\/p>\n
The most common Helper table you’ll see is a table with nothing but the numbers in a sequence from 1 upwards. These tables have a surprising number of uses. Once you’ve understood the principles behind helper tables, then you’ll think of many more. We’ll be providing several examples where a helper table suddenly makes life easier. The objective is to show the principles so that you’ll try out something similar the next time you have to tackle a tricky operation in TSQL. As always, you’re encouraged to load the example script<\/a> into Query Analyser or Management Studio, and experiment!<\/p>\n Splitting Strings into table-rows, based on a specified delimiter<\/a> Note. These examples use Before we start, we’ll need a helper table of numbers. Our examples aren’t going to require high numbers, but we’ve parameterised the size of the table that the routine creates<\/p>\n Here is a routine that checks to see if such a ‘helper’ table called Once you have one of these tables, which we’ve seen described as the Transact SQL developer’s ‘Swiss Army Knife’, you will not want to be without it.<\/p>\n Imagine you have a string which you want to break into words to make into a table.<\/p>\n This can be done very simply and quickly through the following single SQL Select statement:<\/p>\n (Make sure you have executed the You can then enshrine this principle into a table function that will take any delimiter to split a string. (We believe that the credit for this clever routine should go to Anith Sen).<\/p>\n This is the fastest means I have come across in TSQL to split a string into its components as rows. Try this, which give you a table with the integer and the nominal name.<\/p>\n You can use the same principle for a lot of string operations.<\/p>\n Here is one that will This sort of routine is a lot less complex than the iterative methods and is, as one would expect, a lot faster. Just to show that this is no fluke, here is the reverse function to decode a URL Query string.<\/p>\n Here is a simple function that acts as an int to hex converter.<\/p>\n It works this way:<\/p>\n And what about TSQL that converts hex to int?<\/p>\n And now we can test it out:<\/p>\n Next, we have a function that uses these principles to do macro substitution of values into a string. It will work for replacing XHTML placeholders with a value, or producing error messages in a variety of languages. In fact, uses keep popping up for this sort of function. In this version, one can specify what strings are used for substitutions (the default is %1, %2, %3 etc,) and what you use as the delimiter of your list of macros and values.<\/p>\n There are several ways that we can use this routine in practical applications. Try out these and see what happens!<\/p>\n One can do rather cleverer things than this. For example, one can extract all the words from a string into a table, a row for each Even more useful than this is a function that picks out all the numbers from a string into a table. You can therefore easily pick out the third or fourth string simply, because the table has the order as well as the number itself. Were it not for the unary minus operator, this would have been a delightfully simple function.<\/p>\n If you are using this routine, you’ll want to cast these numbers into the number type of your choice. We supply them as strings.<\/p>\n This is a handy little routine for looking at the strings in HTML code, but seems to earn its keep in a lot of other ways. You specify the opening and closing delimiter. At the moment, only single-character delimiters are allowed. Can anyone re-write it to allow multi-character delimiters?.<\/p>\n So we can try it out with brackets:<\/p>\n Or if you want to take out tags:<\/p>\n And as a slightly silly example of the sort of chore that crops up occasionally when analysing strings, character by character, here is a way of scoring a string for Scrabble, assuming it is all on ordinary squares!<\/p>\n And now we try it out!<\/p>\n Now we find out which are the highest scorers, assuming an illegal quantity of tiles. We use the WordList from Phil’s Blog on the Fireside Fun of Decapitations.<\/p>\n Don’t try running this without the WordList!<\/p>\n As well as slicing and dicing strings, once one has one’s helper table, Moving averages<\/strong><\/p>\n How about moving averages? Here is a simple To execute this, you will need a table to try it on.<\/p>\n Now we can try out a moving average!<\/p>\n Here is a UDF that lists all the Mondays (or whatever you want) between two dates.<\/p>\n Or, how about a UDF that tells you the number of working days between two dates? (you can alter it if Saturday and Sunday are not your days off!)<\/p>\n So how many working days until Christmas?<\/p>\n We can go on for ever with example of using a numeric Helper table but we won’t because most of you will have wandered off even before you reach this point. We hope that you’ll now take over and create some more examples, try them out against iterative solutions that do the same thing. We guarantee you’ll be pleased with the result!<\/p>\n Robyn Page’s SQL Server DATE\/TIME Workbench<\/a>, Robyn Page<\/strong> Robyn Page’s SQL Server String Manipulation Workbench<\/a>, Robyn Page<\/strong> SQL Server Error Handling Workbench<\/a>, Grant Fritchey<\/strong> Robyn Page’s SQL Server Cursor Workbench<\/a>, Robyn Page<\/strong> Robyn Page’s SQL Server Data Validation Workbench<\/a>, Robyn Page<\/strong>Our examples include:<\/h2>\n
\n Encoding and decoding a string<\/a>
\n Substituting values into a string<\/a>
\n Extracting individual words from a string into a table<\/a>
\n Extracting all the numbers in a string into a table<\/a>
\n Removing all text between delimiters<\/a>
\n Scrabble score<\/a>
\n Moving averages<\/a>
\n Getting the ‘Week beginning’ date in a table<\/a>
\n Calculating the number of working days between dates.<\/a><\/p>\nVARCHAR(8000)<\/code> just so they compile on both SQL Server 2000 and 2005. If you are using SQL Server 2005, you’ll probably want to change them to VARCHAR(MAX)<\/code>.<\/p>\nCreating the helper table<\/h2>\n
numbers<\/code> exists, and, if not, creates it.<\/p>\nCREATE PROCEDURE spMaybeBuildNumberTable \r\n@size INT=10000 \r\nAS \r\nBEGIN \r\nSET NOCOUNT ON \r\nIF NOT EXISTS (SELECT * FROM dbo.sysobjects \r\n WHERE id = OBJECT_ID(N'[dbo].[Numbers]') \r\n AND OBJECTPROPERTY(id, N'IsUserTable') = 1) \r\n BEGIN \r\n CREATE TABLE [dbo].[Numbers]( \r\n [number] [int], \r\n CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED \r\n ( \r\n [number] ASC \r\n ) ON [PRIMARY] \r\n ) ON [PRIMARY] \r\n\r\n DECLARE @ii INT \r\n SELECT @ii=1 \r\n WHILE (@ii<=@size) \r\n BEGIN \r\n INSERT INTO NUMBERS(NUMBER) SELECT @II \r\n SELECT @II=@II+1 \r\n END \r\n END \r\nEND \r\n<\/pre>\n
Splitting Strings into table-rows, based on a specified delimiter<\/h2>\n
DECLARE @ordinal VARCHAR(255) \r\nSELECT @Ordinal= \r\n'first second third fourth fifth sixth seventh eighth ninth tenth' \r\n<\/pre>\n
spMaybeBuildNumberTable<\/code> procedure first!)<\/p>\nSELECT SUBSTRING(@Ordinal+' ', number, \r\n CHARINDEX(' ', @Ordinal+' ', number) - number) \r\nFROM Numbers \r\nWHERE number <= LEN(@Ordinal) \r\nAND SUBSTRING(' ' + @Ordinal, \r\n number, 1) = ' ' \r\nORDER BY number RETURN \r\n\r\n\/*----with the result \r\nfirst \r\nsecond \r\nthird \r\nfourth \r\nfifth \r\nsixth \r\nseventh \r\neighth \r\nnineth \r\ntenth \r\n\r\n(10 row(s) affected) \r\n<\/pre>\nCREATE FUNCTION [dbo].[uftSplitString] \r\n( \r\n@String VARCHAR(8000), \r\n@Delimiter VARCHAR(255) \r\n) \r\nRETURNS \r\n@Results TABLE \r\n( \r\nSeqNo INT IDENTITY(1, 1), \r\nItem VARCHAR(8000) \r\n) \r\nAS \r\nBEGIN \r\nINSERT INTO @Results (Item) \r\nSELECT SUBSTRING(@String+@Delimiter, number, \r\n CHARINDEX(@Delimiter, @String+@Delimiter, number) - number) \r\nFROM Numbers \r\nWHERE number <= LEN(REPLACE(@String,' ','|')) \r\nAND SUBSTRING(@Delimiter + @String, \r\n number, \r\n LEN(REPLACE(@delimiter,' ','|'))) = @Delimiter \r\nORDER BY number RETURN \r\nEND \r\n<\/pre>\n
SELECT * FROM dbo.uftSplitString( \r\n 'one,two,three,four,five,six,seven,eight,nine,ten',',') \r\n--or this, which \r\nSELECT * FROM dbo.uftSplitString( \r\n 'Monday--Tuesday--Wednesday--thursday--friday--saturday--sunday','--') \r\n\r\n <\/pre>\n
Encoding and decoding a string<\/h2>\n
URLencode<\/code> a string so it can be used in a POST<\/code> or GET HTTP<\/code> operation. The string is converted into a table with one character per row and, after being operated on, it is re-assembled.<\/p>\nCREATE FUNCTION ufsURLEncoded \r\n( \r\n@String VARCHAR(MAX) \r\n) \r\nRETURNS VARCHAR(MAX) \r\nBEGIN \r\nDECLARE @URLEncodedString VARCHAR(MAX) \r\nSELECT @URLEncodedString='' \r\n\r\nSELECT @URLEncodedString=@URLEncodedString+ \r\n CASE WHEN theChar LIKE '[A-Za-z0-9()''*-._!]' \r\n THEN theChar \r\n ELSE '%' \r\n + SUBSTRING ('0123456789ABCDEF', \r\n (ASCII(theChar) \/ 16)+1,1) \r\n + SUBSTRING ('0123456789ABCDEF', \r\n (ASCII(theChar) % 16)+1,1) \r\n END \r\nFROM \r\n ( \r\n SELECT [theChar]=SUBSTRING(@string,number,1) \r\n FROM numbers \r\n WHERE number <= LEN(@String) ) Characterarray \r\n -- Return the result of the function \r\n RETURN @URLEncodedString \r\n\r\nEND \r\n<\/pre>\nCREATE FUNCTION ufsURLDecoded \r\n( \r\n@String VARCHAR(MAX) \r\n) \r\nRETURNS VARCHAR(MAX) \r\nBEGIN \r\nSELECT @string= \r\n REPLACE(@string,escapeString, TheCharacter) \r\nFROM \r\n (SELECT \r\n [escapeString]=SUBSTRING(@string,number,3), \r\n [theCharacter]=CHAR( \r\n (CHARINDEX( \r\n SUBSTRING(@string,number+1, \r\n 1), \r\n '0123456789ABCDEF')-1)*16 \r\n +CHARINDEX( \r\n SUBSTRING(@string,number+2, \r\n 1), \r\n '0123456789ABCDEF')-1) \r\n\r\n FROM numbers \r\n WHERE number <= LEN(@String) \r\n AND SUBSTRING(@string,number,1) = '%' \r\n )f \r\nWHERE CHARINDEX(escapeString,@string)>0 \r\nRETURN @String \r\nEND \r\n<\/pre>\n
CREATE FUNCTION ufsIntToHex \r\n( \r\n @integer INT \r\n) \r\nRETURNS VARCHAR(20) \r\nAS \r\nBEGIN \r\nDECLARE @result VARCHAR(80) \r\nSELECT @result='' \r\n\r\nIF @integer>0 \r\n SELECT @result=CASE WHEN @integer>0 THEN \r\n SUBSTRING('0123456789ABCDEF',(@integer%16)+1,1) \r\n ELSE '' END+@result, \r\n @integer=@integer\/16 \r\n FROM numbers WHERE number<20 \r\nELSE SELECT @result=0 \r\nRETURN '0x'+@result \r\n\r\nEND \r\n<\/pre>\nSELECT dbo.ufsIntToHex(1024) \r\n<\/pre>\n
CREATE FUNCTION ufiHexToInt \r\n( \r\n @HexString VARCHAR(50) \r\n) \r\nRETURNS INT \r\nAS \r\nBEGIN \r\nDECLARE @result INT \r\nSELECT @result=0 \r\nSELECT @Result=(@result*16)+ \r\n (CHARINDEX(SUBSTRING(@HexString,number,1),'0123456789ABCDEF')-1) \r\n FROM numbers WHERE number<=LEN(@HexString) \r\n AND SUBSTRING(@HexString,number,1) LIKE '[0-9ABCDEF]' \r\nRETURN @Result \r\n\r\nEND \r\n<\/pre>\n
SELECT COUNT(*) \r\n FROM numbers \r\nWHERE dbo.ufiHexToInt(dbo.ufsIntToHex(number))<>number \r\n<\/pre>\n
Substituting values into a string<\/h2>\n
CREATE FUNCTION [dbo].[ufsSubstitute] \r\n( \r\n@Template VARCHAR(8000), \r\n@macroList VARCHAR(8000), \r\n@valueList VARCHAR(8000), \r\n@Delimiter VARCHAR(255) \r\n) \r\nRETURNS \r\nVARCHAR(8000) \r\nAS \r\nBEGIN \r\nDECLARE @macros TABLE (MyID INT IDENTITY(1,1),variable VARCHAR(80)) \r\nDECLARE @values TABLE (MyID INT IDENTITY(1,1),[value] VARCHAR(8000)) \r\n--get all the variables \r\nINSERT INTO @macros (variable) \r\n SELECT SUBSTRING(@MacroList+@Delimiter, number, \r\n CHARINDEX(@Delimiter, @MacroList+@Delimiter, number) - number) \r\nFROM Numbers \r\nWHERE number <= LEN(@MacroList) \r\n AND SUBSTRING(@Delimiter + @MacroList, number, LEN(@delimiter)) \r\n = @Delimiter \r\nORDER BY number \r\n\r\nINSERT INTO @values ([value]) \r\n SELECT SUBSTRING(@ValueList+@Delimiter, number, \r\n CHARINDEX(@Delimiter, @ValueList+@Delimiter, number) - number) \r\nFROM Numbers \r\nWHERE number <= LEN(@ValueList) \r\nAND SUBSTRING(@Delimiter + @ValueList, number, \r\n LEN(@delimiter)) = @Delimiter \r\nORDER BY number \r\n\r\nSELECT @Template= \r\n REPLACE(@Template,COALESCE(variable, \r\n '%'+CAST(v.MyID AS VARCHAR)), \r\n [value]) \r\nFROM @values v \r\nLEFT OUTER JOIN @macros m ON v.MyID=m.MyID \r\n WHERE CHARINDEX(COALESCE(variable,'%'+CAST(v.MyID AS VARCHAR)) \r\n ,@Template)>0 \r\n\r\nRETURN (@Template) \r\n\r\nEND \r\n<\/pre>\n
\r\nSELECT dbo.ufsSubstitute (NULL,NULL,'',',') \r\nSELECT dbo.ufsSubstitute ('','','',',') \r\nSELECT dbo.ufsSubstitute ( \r\n ' views','','6',',') \r\nSELECT dbo.ufsSubstitute (' \r\nDear $1 $2, \r\nIt has come to our attention that your $3 account is $4 \r\nto the extent of \u00c2\u00a3$5. \r\nPlease phone our adviser, $6 $7 on $8 who will inform you of \r\nthe various actions that need to be taken', \r\n '$1,$2,$3,$4,$5,$6,$7,$8', \r\n 'Mrs,Prism,current,overdrawn,5678,Mr,Grabbitas,04585 725938', \r\n ',') \r\nSELECT dbo.ufsSubstitute ('To @Destination; \r\nReport dated @Date \r\nThe @table table is now @rows long. please @action' \r\n,'@Destination|@Date|@Table|@rows|@action', \r\n'Phil Factor|12 Apr 2007|Log|1273980|truncate it at once','|') \r\nSELECT dbo.ufsSubstitute ( \r\n'I thought that your present of a %1 was %2. Thank you very much. \r\nThe %1 will come in handy for %3' \r\n ,'' \r\n ,'trowel|absolutely wonderful|gardening','|') \r\n<\/pre>\nExtracting individual words from a string into a table<\/h2>\n
\n word.<\/p>\nCREATE FUNCTION [dbo].[uftWords] \r\n( \r\n@String VARCHAR(8000) \r\n) \r\nRETURNS \r\n@Results TABLE \r\n( \r\nSeqNo INT IDENTITY(1, 1), \r\nWord VARCHAR(8000) \r\n) \r\nAS \r\nBEGIN \r\nINSERT INTO @Results(word) \r\n SELECT [word]=LEFT(RIGHT(@string,number), \r\n PATINDEX('%[^a-z]%',RIGHT(@string,number)+' ')-1) \r\nFROM Numbers \r\nWHERE number <= LEN(@String) \r\nAND PATINDEX('%[a-z]%',RIGHT(@string,number))=1 \r\nAND PATINDEX('%[^a-z]%',RIGHT(' '+@string,number+1))=1 \r\nORDER BY number DESC \r\nRETURN \r\nEND \r\n--and you can get the words (we use it for inversion indexes) \r\nSELECT * FROM dbo.uftWords ('One can do rather cleverer \r\nthings than this. <>! For example, one can extract all the \r\nwords from a string into a table, a row for each word.') \r\n--or a word count \r\nSELECT COUNT(*) FROM dbo.uftWords ('It is extraordinary \r\nhow easy it is to get a wordcount using this ') \r\n<\/pre>\nExtracting all the numbers in a string into a table<\/h2>\n
CREATE FUNCTION [dbo].[uftNumbers] \r\n( \r\n@String VARCHAR(8000) \r\n) \r\nRETURNS \r\n@Results TABLE \r\n( \r\nSeqNo INT IDENTITY(1, 1), \r\nnumber VARCHAR(100) \r\n) \r\nAS \r\nBEGIN \r\nINSERT INTO @Results(number) \r\n SELECT \r\n CASE LEFT(RIGHT(' '+@String,number),1) \r\n WHEN '-' THEN '-' ELSE '' END+ \r\n SUBSTRING( RIGHT(@String,number-1),1, \r\n PATINDEX('%[^0-9.]%', \r\n RIGHT(' '+@String,number-1)+' ')-1) \r\n FROM Numbers \r\n WHERE number <= LEN(REPLACE(@String,' ','!'))+1 \r\n AND PATINDEX('%[^0-9.][0-9]%',RIGHT(' ' \r\n +@String,number))=1 \r\n ORDER BY number DESC \r\nRETURN \r\nEND \r\n--So we try out a few examples just to see. It removes anything \r\n--that doesn't look kile a number \r\nSELECT * FROM dbo.uftNumbers('there are numbers like 34.56, \r\n-56, 67.878, maybe34; possibly56, and a few others like <789023>') \r\n\r\nSELECT * FROM dbo.uftNumbers('23,87986,56.78,67,09,23,30') \r\nSELECT * FROM dbo.uftNumbers('') \r\nSELECT * FROM dbo.uftNumbers(' \r\n<div>') \r\nSELECT * FROM dbo.uftNumbers('there are numbers like 34.56, \r\n-56, 67.878, maybe34; possibly56, and a few others like <789023>') \r\n\r\n\/* \r\nSeqNo number \r\n----------- ------------ \r\n1 34.56 \r\n2 -56 \r\n3 67.878 \r\n4 34 \r\n5 56 \r\n6 789023 \r\n\r\n(6 row(s) affected) \r\n*\/ \r\n<\/pre>\nRemoving all text between delimiters<\/h2>\n
CREATE FUNCTION [dbo].[ufsRemoveDelimited] \r\n( \r\n@String VARCHAR(8000), \r\n@OpeningDelimiter CHAR(1), \r\n@ClosingDelimiter CHAR(1) \r\n) \r\nRETURNS \r\nVARCHAR(8000) \r\nAS \r\nBEGIN \r\nDECLARE @newString VARCHAR(8000) \r\nIF @OpeningDelimiter = @ClosingDelimiter \r\n BEGIN \r\n RETURN NULL \r\n END \r\nIF @OpeningDelimiter+@ClosingDelimiter+@String IS NULL \r\n BEGIN \r\n RETURN @String \r\n END \r\nSELECT @NewString='' \r\nSELECT @newString =@newString +SUBSTRING(@String,number,1) \r\n FROM numbers \r\n WHERE number<=LEN (REPLACE(@string,' ','|')) \r\n AND \r\n CHARINDEX (@OpeningDelimiter,@string+@OpeningDelimiter,number) \r\n < \r\n CHARINDEX (@ClosingDelimiter,@string+' '+@closingDelimiter,number) \r\nAND number <> CHARINDEX (@OpeningDelimiter,@string,number) \r\nRETURN @NewString \r\nEND \r\n<\/pre>\n
SELECT dbo.ufsRemoveDelimited( \r\n 'this will appear(but not this),)will this?(','(',')') \r\n<\/pre>\nSELECT dbo.ufsRemoveDelimited( \r\n 'this will appear \r\n <div>and this <\/div>and this','<','>') \r\n--or this \r\nSELECT dbo.ufsRemoveDelimited( \r\n'<?xml version=\"1.0\" encoding=\"us-ascii\"?>\r\n<!DOCTYPE html PUBLIC \"-\/\/W3C\/\/DTD XHTML 1.0 Strict\/\/EN\"\r\n\"http:\/\/www.w3.org\/TR\/xhtml1\/DTD\/xhtml1-strict.dtd\">\r\n<html xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\r\n<head>\r\n<title>A poem<\/title>\r\n<\/head>\r\n<body>\r\n <div style=\"float left: width:300px;\">\r\n <h2>\r\n Weather forecast\r\n <\/h2>\r\n <p>\r\n The rain it raineth every day<br \/>\r\n upon the just and unjust fellah<br \/>\r\n but mainly on the just, because,<br \/>\r\n the unjust pinched the just''s umbrella<br \/>\r\n <\/p>\r\n <\/div>\r\n<\/body>\r\n<\/html>\r\n','<','>')\r\n<\/pre>\n
Scrabble Score<\/h2>\n
CREATE FUNCTION ufiScrabbleScore \r\n( \r\n @String VARCHAR(100) \r\n) \r\nRETURNS INT \r\nAS \r\nBEGIN \r\nDECLARE @Total INT \r\nSELECT @total=0 \r\n\r\nSELECT @Total=@total+ \r\nCASE WHEN SUBSTRING(@String,number,1) NOT LIKE '[a-z]' THEN NULL \r\n ELSE \r\nCAST(SUBSTRING('00000000001122223333347799', \r\n CHARINDEX(SUBSTRING(@String,number,1), \r\n 'EAIONRTLSUDGBCMPFHVWYKJXQZ') \r\n ,1) AS INT)+1 END \r\nFROM numbers WHERE number <=LEN(@String) \r\nRETURN @Total \r\nEND \r\n<\/pre>\nSELECT dbo.ufiScrabbleScore('Quiz') --22 \r\nSELECT dbo.ufiScrabbleScore('Robyn') --10 \r\n<\/pre>\nSELECT dbo.ufiScrabbleScore(word),word \r\nFROM wordlist ORDER BY dbo.ufiScrabbleScore(word) DESC \/* \r\n49 razzamatazz \r\n48 razzmatazz \r\n45 pizzazz \r\n43 quizzically \r\n39 squeezeboxes \r\n38 quizzical \r\n38 psychoanalyzing \r\n37 psychoanalyzed \r\n37 squeezebox \r\n..etc.... \r\n<\/pre>\n
\n suddenly time-interval based reporting becomes much easier.<\/p>\nselect<\/code> statement that gives you the moving average (over a week) of the number of log entries for every day for a year. This can be adapted to give weighted and exponential moving averages over arbitrary time periods. You use this technique for ironing out ‘noise’ from a graph in order to accentuate the underlying trend.<\/p>\nDROP TABLE #cb \r\nCREATE TABLE #cb (insertionDate datetime)--quantity \r\n--for once, we need to iterate to shuffle the pack \r\nDECLARE @ii INT \r\nSELECT @ii=0 \r\nWHILE @ii<20000 \r\n BEGIN \r\n INSERT INTO #cb(insertionDate) \r\n SELECT DATEADD(Hour,RAND()*8760,'1 jan 2006') \r\n SELECT @ii=@ii+1 \r\n END \r\n--and put an index on it \r\nCREATE CLUSTERED INDEX idxInsertionDate ON #cb(insertionDate) \r\n<\/pre>\n
SELECT start,[running average]=COUNT(*)\/7 \r\n FROM \r\n ( \r\n SELECT [order]=number, \r\n [start]=DATEADD(DAY,number,'1 Jan 2006'), \r\n [end]=DATEADD(DAY,number+7,'1 Jan 2006') \r\n FROM numbers \r\n WHERE DATEADD(DAY,number,'1 Jan 2006') \r\n BETWEEN '1 Jan 2006' AND '1 Jan 2007')f \r\n LEFT OUTER JOIN [#cb] \r\n ON [#cb].insertionDate BETWEEN f.start AND f.[end] \r\n GROUP BY start \r\n ORDER BY start \r\n<\/pre>\n
Getting the ‘Week beginning’ date in a table<\/h2>\n
CREATE FUNCTION uftDatesOfWeekday \r\n( \r\n @Weekday VARCHAR(10), \r\n @StartDate datetime, \r\n @EndDate DateTime \r\n) \r\nRETURNS TABLE \r\nAS \r\nRETURN \r\n( \r\nSELECT \r\n [start]=DATEADD(DAY,number-1,@StartDate) \r\n FROM numbers \r\n WHERE DATEADD(DAY,number-1,@StartDate)< @EndDate \r\n AND DATENAME(dw,DATEADD(DAY,number-1,@StartDate))=@Weekday \r\n) \r\n\/* \r\nAnd you can try it out by finding how many mondays there are between \r\nthe first of January and 1st june this year. *\/ \r\nSELECT * FROM dbo.uftDatesOfWeekday('monday','1 Jan 2007','1 Jun 2007') \r\n<\/pre>\nNumber of Working Days between two dates<\/h2>\n
CREATE FUNCTION ufiWorkingDays \r\n( \r\n @StartDate datetime, \r\n @EndDate DateTime \r\n) \r\nRETURNS INT \r\nAS \r\nBEGIN \r\nRETURN \r\n(SELECT COUNT(*) \r\n FROM numbers \r\n WHERE DATEADD(DAY,number-1,@StartDate)< @EndDate \r\n AND DATENAME(dw,DATEADD(DAY,number-1,@StartDate)) \r\n NOT IN ('saturday','sunday')) \r\nEND \r\n<\/pre>\nSELECT dbo.ufiWorkingDays( \r\n GETDATE(),'25 Dec '+DATENAME(YEAR,GETDATE())) \r\n <\/pre>\n
See also other Workbenches at Simple-Talk<\/h3>\n
\n Date calculation and formatting in SQL Server can be surprisingly tricky. Robyn Page’s “hands-on” workbench will lead you through the minefield.<\/p>\n
\n String searching and manipulation in SQL Server can be error-prone and tedious… Unless you’re armed with the techniques described in Robyn’s string manipulation workbench…<\/p>\n
\n Grant Fritchey steps into the workbench arena, with an example-fuelled examination of catching and gracefully handling errors in SQL 2000 and 2005, including worked examples of the new TRY..CATCH<\/code> capabilities.<\/p>\n
\n The topic of cursors is the ultimate “hot potato” in the world of SQL Server. Everyone has a view on when they should and mainly should not be used. By example and testing Robyn Page proves that, when handled with care, cursors are not necessarily a “bad thing”.<\/p>\n
\n Robyn Page provides essential techniques for ensuring the validity of the data being entered into your SQL Server tables.<\/p>\n