November 2008 - Posts

Row_number() Function with no specific order

Row_number() function is used to generate row number to rows based on the column which is ordered

What if you want to generate row number without ordering any column

Here is the method (Consider Suppliers table from northwind database)

1 Assign dummy column with literal 0 and order it by that column

select row_number() over(order by order_col),companyname,address from
(
        select companyname,address,0 as order_col from northwind..Suppliers
) as t

2 Assign dummy column with literal 0 directly on the Order by clause

select row_number() over(order by (select 0)),companyname,address from northwind..Suppliers

Also refer Multipurpose Row_Number() Function

Posted by Madhivanan with no comments
Filed under: , ,

Time Calculation on Numbers

In Forums sometimes I read questions on how to find out the difference between two times which are stored as characters

Here are some possible answers

Subtraction

declare @t1 varchar(5), @t2 varchar(5), @sum float

select @t1='12.56', @t2='7.58'

set @sum=cast(@t1 as float)-cast(@t2 as float)

--Method 1 : Convert to Datetime
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))- dateadd(day,0,replace(@t2,'.',':')),108)

--Method 2 : Number caculation1
select case when right(@sum,2)>=60 then @sum-0.4 else @sum end

--Method 3 : String caculation
select cast(left(@sum,charindex('.',@sum)-1)+case when right(@sum,charindex('.',reverse(@sum))-1)>60 then right(@sum,charindex('.',reverse(@sum))-1)-40 else right(@sum,charindex('.',reverse(@sum))-1) end/100.0 as decimal(4,2))

--Method 4 : Number calculation2
select cast(cast(@sum as int)+case when cast(@sum*100 as int)%100>=60 then cast(@sum*100 as int)%100-40 else cast(@sum*100 as int)%100 end/100.0 as decimal(4,2))

Addition

--Method 1: Date calculation
select convert(varchar(5),dateadd(day,0,replace(@t1,'.',':'))+ dateadd(day,0,replace(@t2,'.',':')),108)

--Method 2: Number calculation

select @sum=cast(@t1 as decimal(4,2))+@t2

select case when right(@t1,2)*1+right(@t2,2)>=60 then @sum+0.4 else @sum end

Posted by Madhivanan with no comments

SQL_VARIANT_PROPERTY function

If you want to know the datatype or length of a column, you can query on the system table syscolumns or
system
view INFORMATION_SCHEMA.COLUMNS

But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function

Example

SELECT
    TOP 1
        SQL_VARIANT_PROPERTY('column_name','basetype')
FROM
    table_name

which gives you the datatype of the column_name

This is an example from Northwind..Orders table

SELECT 
    TOP 1
        SQL_VARIANT_PROPERTY('orderid','basetype') as basetype,
        SQL_VARIANT_PROPERTY('orderid','maxlength') as maxlength,
        SQL_VARIANT_PROPERTY('orderid','precision') as precision,
        SQL_VARIANT_PROPERTY('orderid','scale') as scale,
        SQL_VARIANT_PROPERTY('orderid','collation') as collation
FROM
        Northwind..Orders