SQL- Function to Convert Amount to Words

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : To get the amount in words
-------------------------------------------------
--SELECT dbo.AmountInWords (999999999)
-- ==============r===============================

ALTER FUNCTION [dbo].[AmountInwords] (
@numAmount NUMERIC(19,8)
)
RETURNS VARCHAR(8000)
--WITH ENCRYPTION
BEGIN
DECLARE @x NUMERIC(38,8)
DECLARE @y NUMERIC(38,8)
DECLARE @incr BIGINT
DECLARE @incr2 BIGINT
DECLARE @Remainder BIGINT
DECLARE @Remainder2 BIGINT
DECLARE @Integer BIGINT
DECLARE @MoneyinWords VARCHAR(MAX)
SET @MoneyinWords = ''
SET @x =@numAmount
SET @incr = 1
SET @incr2 = 1

DECLARE @tblDeult TABLE(val BIGINT , valstr VARCHAR(MAX))
INSERT @tblDeult
SELECT 1 , 'One'
UNION
SELECT 2 , 'Two'
UNION
SELECT 3 , 'Three'
UNION
SELECT 4, 'Four'
UNION
SELECT 5 , 'Five'
UNION
SELECT 6, 'Six'
UNION
SELECT 7 , 'Seven'
UNION
SELECT 8 , 'Eight'
UNION
SELECT 9 , 'Nine'
UNION
SELECT 10 , 'Ten'
UNION
SELECT 11 , 'Eleven'
UNION
SELECT 12 , 'Twelve'
UNION
SELECT 13, 'Thirteen'
UNION
SELECT 14 , 'Fourteen'
UNION
SELECT 15 , 'Fifteen'
UNION
SELECT 16 , 'Sixteen'
UNION
SELECT 17 , 'Seventeen'
UNION
SELECT 18 , 'Eighteen'
UNION
SELECT 19 , 'Nineteen'
UNION
SELECT 20 , 'Twenty'
UNION
SELECT 30 , 'Thirty'
UNION
SELECT 40 , 'Fourty'
UNION
SELECT 50 , 'Fifty'
UNION
SELECT 60 , 'Sixty'
UNION
SELECT 70 , 'Seventy'
UNION
SELECT 80, 'Eighty'
UNION
SELECT 90 , 'Ninety'
UNION
SELECT 100 , 'Hundred'
UNION
SELECT 1000 , 'Thousand'
UNION
SELECT 100000 , 'Lakhs'
UNION
SELECT 10000000 , 'Crores'


WHILE (@incr > 0)
BEGIN
IF (@x < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @x

SET @incr = 0
END
ELSE IF (@x > 20 AND @x < 100)
BEGIN
SET @Remainder = @x % 10
SET @Integer = (@x /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99 AND @x <1000 )
BEGIN
SET @Remainder = @x % 100
SET @Integer = (@x /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 999 AND @x <100000 )
BEGIN
SET @Remainder = @x % 1000
SET @Integer = (@x /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99999 AND @x <10000000 )
BEGIN
SET @Remainder = @x % 100000
SET @Integer = (@x /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 9999999)-- AND @x <1000000000 )
BEGIN
SET @Remainder = @x % 10000000
SET @Integer = (@x /10000000)
SET @y = @Integer
SET @Remainder2 = @Remainder
IF (@Integer > 19)
BEGIN
WHILE ( @incr2> 0 )
BEGIN
IF (@y < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @y

SET @incr2 = 0
END
ELSE IF (@y > 20 AND @y < 100)
BEGIN
SET @Remainder2= @y % 10
SET @Integer = (@y /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99 AND @y <1000 )
BEGIN
SET @Remainder2= @y % 100
SET @Integer = (@y /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 999 AND @y <100000 )
BEGIN
SET @Remainder2= @y % 1000
SET @Integer = (@y /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99999 AND @y <10000000 )
BEGIN
SET @Remainder2= @y % 100000
SET @Integer = (@y /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
END

END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 10000000

SET @incr = @incr +1
SET @x = @Remainder
END

END

SELECT @MoneyinWords = @MoneyinWords + ' Rupees Only'


RETURN @MoneyinWords
END

SQL - INITCap Function

-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To make upper case for Each word
-- =============================================
ALTER FUNCTION [dbo].[INITCAP_date]
(
@vchDateString VARCHAR(MAX)
) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @bitValidDate BIT
SET @vchDateString = REPLACE(@vchDateString,' ','')
-- a valid date should be min Eight chars and Max
IF (LEN( @vchDateString) > 7) AND (LEN(@vchDateString) < 24)
BEGIN
SET @bitValidDate = ISDATE(@vchDateString)
END
ELSE
BEGIN
SET @bitValidDate = 0
END

RETURN @vchDateString
END

SQL- To list the hierarchy date

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To List list the dates between startdate and end date
-- =============================================
ALTER FUNCTION [dbo].[udf_HierarchyDate]
(
@StartDate DATETIME
, @EndDate DATETIME
)
RETURNS @date TABLE(DATE DATETIME) AS
BEGIN
-- process until startdate is equal to end date
WHILE (@StartDate<=@EndDate)
BEGIN
INSERT INTO @date
SELECT @StartDate
-- increment the date
SET @StartDate = @StartDate+1
END

RETURN

END

SQL-Split String Values

-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To split the string as separate parts
-- =============================================

ALTER FUNCTION [dbo].[udf_SplitString]
(
@vchString VARCHAR(MAX)
, @vchDelimitter CHAR(1)= ','
)
RETURNS @Values TABLE (Parts VARCHAR(MAX))AS
BEGIN
DECLARE @intPosistion INT -- to hold the string posistion
DECLARE @Part VARCHAR(MAX)
SELECT @intPosistion = 1
WHILE (@intPosistion > 0)
BEGIN
-- check the given delimitter in between names else consider it as single string
SELECT @intPosistion = CHARINDEX(@vchDelimitter,@vchString)
IF @intPosistion > 0
BEGIN
-- then split the name
SELECT @Part = LEFT(@vchString,@intPosistion - 1)
END
ELSE
BEGIN
-- if string doesnt have specified delimitter then take it as single string
SELECT @Part = @vchString
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
-- insert the splitted part to return as row value
INSERT @Values(Parts)
SELECT LTRIM(RTRIM(@Part))
SELECT @vchString = RIGHT(@vchString,LEN(@vchString) - @intPosistion)
-- if there are no chanraters in given string then end loop
IF LEN(@vchString) = 0
BEGIN
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
END
RETURN
END

SQL Server val Function Equivalent to MS Access Val function

RemoveCharValues

-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : to remove the char values
-------------------------------------------------

-- ==============r===============================
CREATE FUNCTION [dbo].[RemoveCharValues] (
@vchString VARCHAR(MAX)
)
RETURNS VARCHAR(8000)
--WITH ENCRYPTION
BEGIN
-- find if character exists remove the character
WHILE (PATINDEX('%[A-Z]%',@vchString))>0
BEGIN
SET @vchString = REPLACE(@vchString,SUBSTRING(@vchString,PATINDEX('%[A-Z]%',@vchString),1),'')
END
-- return the numeric part
RETURN @vchString
END

Ramesh-Generate Table data as Insert statements

--
-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : To Generate Table Datas as SQL Script
-- Important Note 1: This SP Doesnot Support For image datatype hold columns
-- 2: No cursors,No Looping Used
-------------------------------------------------
--[dbo].[uSP_Sql] 'select * from tAA_Parcel'
-- ==============r===============================
ALTER PROC [dbo].[uSP_Sql]
@vchSqlQuery VARCHAR(MAX)
--WITH ENCRYPTION
AS
DECLARE @InsertStmt VARCHAR(128) -- hold the Insert stmt
, @ValuesStmt VARCHAR(128) -- hold the values stmt
, @vchTableName VARCHAR(128)
, @vchNewTblName VARCHAR(128)
, @InsertText VARCHAR(MAX) -- hold the Insert stmt text
, @ValuesText VARCHAR(MAX) -- hold the Values stmt text
, @SQLStmt NVARCHAR(MAX) -- hold the whole SQL stmt
, @vchTmpTblQuery VARCHAR(MAX)
, @iFromPos INT
, @iWherePos INT

BEGIN
SET NOCOUNT ON;
BEGIN TRY

SET @vchSqlQuery = LTRIM(RTRIM(@vchSqlQuery))
SET @iFromPos = CHARINDEX(' FROM ',@vchSqlQuery,0)
IF (@iFromPos = 0 )
BEGIN
SET @vchTableName = @vchSqlQuery
SET @vchSqlQuery = 'SELECT * FROM '+LTRIM(RTRIM(@vchSqlQuery))

END
ELSE IF (@iFromPos > 0 )
BEGIN
SET @iWherePos = ISNULL(ISNULL(ISNULL(NULLIF(CHARINDEX('WHERE ',@vchSqlQuery,0),0),NULLIF(CHARINDEX('GROUP ',@vchSqlQuery,0),0)),NULLIF(CHARINDEX('ORDER ',@vchSqlQuery,0),0)),LEN(@vchSqlQuery)+1)
SET @vchTableName = SUBSTRING(@vchSqlQuery,@iFromPos +6,@iWherePos - @iFromPos -4)
END

SET @iWherePos = ISNULL(ISNULL(ISNULL(NULLIF(CHARINDEX('WHERE ',@vchSqlQuery,0),0),NULLIF(CHARINDEX('GROUP ',@vchSqlQuery,0),0)),NULLIF(CHARINDEX('ORDER ',@vchSqlQuery,0),0)),LEN(@vchSqlQuery)+1)
SET @vchNewTblName = 'TempTbl'+LTRIM(RTRIM(STR(@@SPID)))
SET @vchTmpTblQuery = REPLACE (@vchSqlQuery, 'FROM',' INTO '+@vchNewTblName+' FROM ')

IF EXISTS( SELECT 'X' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @vchNewTblName)
BEGIN
EXEC( 'DROP TABLE '+@vchNewTblName)
END

EXEC (@vchTmpTblQuery)

SET @InsertText = 'INSERT INTO ' + @vchTableName +'('
SET @ValuesText = ' VALUES ('''+'+' ;

WITH RankTable(RowNumber,ColumnName,DataType) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION)AS RowNumber
,COLUMN_NAME
,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE NOT IN ('image')
AND TABLE_NAME = @vchNewTblName

)
SELECT @InsertText = @InsertText + CASE RowNumber WHEN 1 THEN ColumnName
ELSE ','+ ColumnName
END
, @ValuesText = @ValuesText + CASE RowNumber WHEN 1 THEN ''
ELSE '+'',''+'
END
+ CASE WHEN DataType IN ( 'TINYINT' ,'SMALLINT','INT','REAL','MONEY','FLOAT','BIT','DECIMAL','NUMERIC','SMALLMONEY','BIGINT')
THEN 'COALESCE(CONVERT(VARCHAR(128),'+ ColumnName+'),''NULL'')'
--THEN ' CONVERT(VARCHAR(128),'+ ColumnName+')'
WHEN DataType IN ('XML','TEXT','NTEXT','SQL_VARIANT' ,'BINARY','VARBINARY','TIMESTAMP')
THEN ''''''''''+'+COALESCE(REPLACE(RTRIM(LTRIM('+ColumnName+')),'''''''',''''''''''''),''NULL'') +'+''''''''''
WHEN DataType IN ('Datetime','smalldatetime') THEN
''''''''''+'+CONVERT(VARCHAR(25),COALESCE('+ColumnName+',''1/1/1900''),101) +'+''''''''''
ELSE ''''''''''+'+COALESCE(REPLACE(RTRIM(LTRIM('+ColumnName+')),'''''''',''''''''''''),''NULL'') +'+''''''''''

END
FROM RankTable
ORDER BY RowNumber ASC

SET @InsertText = @InsertText + ' )'
SET @SQLStmt = 'DECLARE @tblScript TABLE ( AscID INT IDENTITY(1,1) ,vchSQL VARCHAR(MAX)) '
SELECT @InsertText = 'INSERT INTO @tblScript SELECT '+''''+ @InsertText + @ValuesText +'+'')''' +' FROM '+@vchNewTblName

IF ( (SELECT ISNULL( (SELECT 1 FROM sys.syscolumns WHERE object_name(id) = @vchTableName AND colstat = 1) ,0)) = 1 )
BEGIN
SELECT @InsertText = 'INSERT INTO @tblScript SELECT '+''''+ 'SET IDENTITY_INSERT ' + @vchTableName + ' ON ' +''''+char(10)+char(10)
+@InsertText +char(10)+char(10)
+'INSERT INTO @tblScript SELECT '+''''+ 'SET IDENTITY_INSERT ' + @vchTableName + ' OFF ' +''''
END
ELSE
BEGIN
SELECT @InsertText = @InsertText
END
SELECT @SQLStmt = @SQLStmt + @InsertText + +char(10)+char(10) +' SELECT REPLACE(REPLACE(vchSQL,''01/01/1900'',''NULL''),''''''NULL'''''',''NULL'') FROM @tblScript ORDER BY AscID ASC'
EXEC( @SQLStmt)
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT ERROR_MESSAGE()
-- RETURN -999
END CATCH
SET NOCOUNT OFF;
END

Group by and Distinct with Ranking Functions

DECLARE @tbl_Groupby TABLE(RevisionID INT ,RevisionNumber VARCHAR(100))

INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R2')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (3,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (3,'R1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (4,'R5')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (5,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (5,'R2')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R7')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (8,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (9,'R12')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (10,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (8,'R4')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (63,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (64,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (65,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (66,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (67,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (68,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (50,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R14')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R16')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (51,'R16')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (52,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (53,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (54,'R5')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (54,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (55,'R7')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (55,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (56,'R12')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (57,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (58,'R1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (59,'R0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (60,'R0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (61,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (62,'R12')
--Distinct
SELECT DISTINCT [RevisionID] AS RevisionName
,ROW_NUMBER() OVER ( ORDER BY [RevisionID] ASC) AS Rank
FROM @tbl_Groupby


--
---- Group by
SELECT [RevisionID] AS RevisionName
,ROW_NUMBER() OVER ( ORDER BY [RevisionID] ASC) AS Rank
FROM @tbl_Groupby
GROUP BY [RevisionID]

SQL Server 2005: Using OVER() with Aggregate Functions

One of new features in SQL 2005 that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.
The way it works is similar to joining an aggregated copy of a SELECT to itself. For example, consider the following:

DECLARE @Orders TABLE( customerID INT, productID INT, orderDate DATETIME, orderAmount NUMERIC(19,2))

INSERT INTO @Orders
SELECT 1 , 1 , '2007-01-01 00:00:00.000' ,20.00
UNION
SELECT 1 , 2 , '2007-01-02 00:00:00.000', 30.00
UNION
SELECT 1 , 2 , '2007-01-05 00:00:00.000' ,23.00
UNION
SELECT 1 , 3 , '2007-01-04 00:00:00.000', 18.00
UNION
SELECT 2 , 1 , '2007-01-03 00:00:00.000' ,74.00
UNION
SELECT 2 , 1 , '2007-01-06 00:00:00.000', 34.00
UNION
SELECT 2 , 2 , '2007-01-08 00:00:00.000' ,10.00

select customerID, productID, orderDate, orderAmount
from @Orders

customerID productID orderDate orderAmount
----------- ----------- ----------------------- ---------------------
1 1 2007-01-01 00:00:00.000 20.00
1 2 2007-01-02 00:00:00.000 30.00
1 2 2007-01-05 00:00:00.000 23.00
1 3 2007-01-04 00:00:00.000 18.00
2 1 2007-01-03 00:00:00.000 74.00
2 1 2007-01-06 00:00:00.000 34.00
2 2 2007-01-08 00:00:00.000 10.00

(7 row(s) affected)
You can now easily return the total orderAmount per customer as an additional column in this SELECT, simply by adding an aggregate SUM() function with an OVER() clause:
select customerID, productID, orderDate, orderAmount,
sum(orderAmount) OVER (Partition by CustomerID) as Total
from @Orders


customerID productID orderDate orderAmount Total
----------- ----------- ----------------------- ------------- ---------
1 1 2007-01-01 00:00:00.000 20.00 91.00
1 2 2007-01-02 00:00:00.000 30.00 91.00
1 2 2007-01-05 00:00:00.000 23.00 91.00
1 3 2007-01-04 00:00:00.000 18.00 91.00
2 1 2007-01-03 00:00:00.000 74.00 118.00
2 1 2007-01-06 00:00:00.000 34.00 118.00
2 2 2007-01-08 00:00:00.000 10.00 118.00

(7 row(s) affected)
The previous SQL is essentially shorthand for:
select
o.customerID, o.productID, o.orderDate, o.orderAmount, t.Total
from
@Orders o
inner join
(
select customerID, sum(orderAmount) as Total from @Orders group by customerID
)
t on t.customerID = o.customerID
since the two return the same results.
Note that the total returned using SUM(..) OVER (..) is not the total for the entire table, just for the scope of the SELECT where it is used. For example, if you add a filter to the SELECT to return only rows for ProductID 2, the totals will reflect that criteria as well:
select customerID, productID, orderDate, orderAmount,
sum(orderAmount) OVER (Partition by CustomerID) as Total
from @Orders
where productID = 2

customerID productID orderDate orderAmount Total
----------- ----------- ----------------------- ------------ ------------
1 2 2007-01-02 00:00:00.000 30.00 53.00
1 2 2007-01-05 00:00:00.000 23.00 53.00
2 2 2007-01-08 00:00:00.000 10.00 10.00

(3 row(s) affected)
That is a nice advantage over the old way of linking to a derived table, since in that case you'd need to repeat the criteria for both the primary (outer) SELECT and also the derived table.
Typically, SUM(..) OVER(..) is most useful for calculating a percentage of a total for each row. For example, for each Order we can calculate the percentage of that order's orderAmount compared to the customer's total orderAmount:
select customerID, productID, orderDate, orderAmount,
orderAmount / sum(orderAmount) OVER (Partition by CustomerID) as Pct
from @Orders

customerID productID orderDate orderAmount Pct
----------- ----------- ----------------------- ------------ -------
1 1 2007-01-01 00:00:00.000 20.00 0.2197
1 2 2007-01-02 00:00:00.000 30.00 0.3296
1 2 2007-01-05 00:00:00.000 23.00 0.2527
1 3 2007-01-04 00:00:00.000 18.00 0.1978
2 1 2007-01-03 00:00:00.000 74.00 0.6271
2 1 2007-01-06 00:00:00.000 34.00 0.2881
2 2 2007-01-08 00:00:00.000 10.00 0.0847

(7 row(s) affected)
Of course, be sure that you don't encounter any divide by zero errors by using a CASE if necessary.
To using the SUM() function, of course this technique works with any of the other aggregate functions as well, such as MIN() or AVG(). For example, you could return only Orders where the orderAmount is below the average for the product that was ordered by writing:
select x.*
from
(
select customerId, productID, orderDate, orderAmount,
avg(orderAmount) over (partition by productID) as ProductAvg
from @Orders) x
where x.orderAmount < x.productAvg

customerId productID orderDate orderAmount ProductAvg
----------- ----------- ----------------------- ------------- -----------
1 1 2007-01-01 00:00:00.000 20.00 42.6666
2 1 2007-01-06 00:00:00.000 34.00 42.6666
2 2 2007-01-08 00:00:00.000 10.00 21.00

a