February 15, 2010

SQL random number column

Here's a quickie. If you need to have a new random number on a column, this works really well.

rand(cast(cast(NEWID() as varbinary) as int)) number

Of course NEWID generates a random guid. Casting it as a varbinary first, then as an int gives you a random number which can then be used as a seed for the random number generator.

Dynamically Generated List of Dates Using Common Table Expressions (CTE)

Here is a nice use for Common Table Expressions (CTEs) to generate a dates table or other list of stuff. I won't take credit for this technique since I saw it someplace else (but can't remember where). Of course my blog is for stuff I want to remember, so thanks to whoever you are...

declare @loops int = 1000
declare @StartDate date = '1/1/2000';

WITH CountTable( RowNumber, [Month], [Year], FirstDay ) AS
(
 SELECT
  1 RowNumber,
  MONTH(@StartDate) [Month],
  YEAR(@StartDate) [Year],
  -- compute the first day of the month from whatever day was provided
  DATEADD(dd,-(DAY(@StartDate)-1),@StartDate) FirstDay   
 UNION ALL
 
 SELECT
  RowNumber + 1 RowNumber,
  MONTH(DATEADD(MONTH, 1, FirstDay)),
  YEAR(DATEADD(MONTH, 1, FirstDay)),
  DATEADD(MONTH, 1, FirstDay) FirstDay
  
 FROM CountTable t
 WHERE RowNumber < @loops
)

SELECT * 
FROM CountTable
OPTION (maxrecursion 32767)

The results look like this:

 1  1 2000 2000-01-01
 2  2 2000 2000-02-01
 3  3 2000 2000-03-01
 4  4 2000 2000-04-01
 5  5 2000 2000-05-01
 6  6 2000 2000-06-01
 7  7 2000 2000-07-01
 8  8 2000 2000-08-01
 9  9 2000 2000-09-01
10 10 2000 2000-10-01
11 11 2000 2000-11-01
12 12 2000 2000-12-01
13  1 2001 2001-01-01
14  2 2001 2001-02-01
more rows here...
 999 3 2083 2083-03-01
1000 4 2083 2083-04-01

In this query, there are two parts to the CTE, the seed (top part) and the function (bottom part). The seed sets the initial values, and the function uses the seed values to create the next row of values, which are then used to create the following row of values (and so on, and so on...)

There are a couple of things to notice:

  • The function values must be based on the previous value (notice my recalculation of the next month)
  • You must use a semicolon before the CTE
  • the maxrecursion option defaults at 100 iterations and has a maximum value of 32767

February 6, 2010

Recursively change unix permissions on a directory

On my freenas server I sometimes need to change the permissions of files that were uploaded via FTP. Here is the command that can be issued to make those changes:

chmod -R 0777 "/mnt/Seagate650/FTP Root"

The quotes are important because my ftp root directory contains a space.