Search This Blog

Loading...

Saturday, October 20, 2012

Get nth highest and lowest salary of an employee

declare @Tbl table(sal int)

insert into @Tbl(sal) values(12),(15),(20),(25),(8),(20),(18),(10)

declare @TopNthSal int

set @TopNthSal = 3

select distinct * from @Tbl order by sal desc

select max(sal) as 'N-Highest' from @Tbl where sal not in ( select distinct top(@TopNthSal-1) sal from @Tbl order by sal desc )

select min(sal) as 'N-Lowest' from @Tbl where sal not in ( select distinct top(@TopNthSal-1) sal from @Tbl order by sal )

Get Last date of Month

declare @DT datetime

set @DT = '1/15/2012'

SELECT DATEADD(MM, DATEDIFF(MM, 0, @DT) + 1, 0)-1 AS MonthEndDate

Convert Number into RomanNo

CREATE Function GetRomanNo(@N as varchar(20)) RETURNS VARCHAR(100) AS BEGIN DECLARE @s varchar(100), @r varchar(100), @i bigint, @p int, @d bigint SET @s = '' SET @r = 'IVXLCDM' -- Roman Symbols /* There is no roman symbol for 0, but I don't want to return an empty string */ IF @n=0 SET @s = '0' ELSE BEGIN SELECT @p = 1, @i = ABS(@n) WHILE(@p<=5) BEGIN SET @d = @i % 10 SET @i = @i / 10 SELECT @s = CASE WHEN @d IN (0,1,2,3) THEN Replicate(SubString(@r,@p,1),@d) + @s WHEN @d IN (4) THEN SubString(@r,@p,2) + @s WHEN @d IN (5,6,7,8) THEN SubString(@r,@p+1,1) + Replicate(SubString(@r,@p,1),@d-5) + @s WHEN @d IN (9) THEN SubString(@r,@p,1) + SubString(@r,@p+2,1) + @s END SET @p = @p + 2 END SET @s = Replicate('M',@i) + @s IF @n < 0 SET @s = '-' + @s END RETURN @s END GO

select dbo.GetRomanNo(24)

Convert String into camel case

CREATE FUNCTION [dbo].[CamelCase] ( @Str varchar(8000) ) RETURNS varchar(8000) AS BEGIN DECLARE @Result varchar(2000) SET @Str = LOWER(@Str) + ' ' SET @Result = '' WHILE 1=1 BEGIN IF PATINDEX('% %',@Str) = 0 BREAK SET @Result = @Result + UPPER(Left(@Str,1))+ SubString (@Str,2,CharIndex(' ',@Str)-1) SET @Str = SubString(@Str, CharIndex(' ',@Str)+1,Len(@Str)) END SET @Result = Left(@Result,Len(@Result)) RETURN @Result END
SELECT dbo.CamelCase('maNISh pATHaK') Output: Manish Pathak

Thursday, October 18, 2012

UDF - Distence B/W two Lat Long

CREATE FUNCTION [dbo].[fn_DistanceBetween_Metric] (@Lat1 as real, @Long1 as real, @Lat2 as real, @Long2 as real, @Metric varchar(20)='mile') RETURNS real --WITH ENCRYPTION AS BEGIN DECLARE @dLat1InRad as float(53); SET @dLat1InRad = @Lat1 * (PI()/180.0); DECLARE @dLong1InRad as float(53); SET @dLong1InRad = @Long1 * (PI()/180.0); DECLARE @dLat2InRad as float(53); SET @dLat2InRad = @Lat2 * (PI()/180.0); DECLARE @dLong2InRad as float(53); SET @dLong2InRad = @Long2 * (PI()/180.0); DECLARE @dLongitude as float(53); SET @dLongitude = @dLong2InRad - @dLong1InRad; DECLARE @dLatitude as float(53); SET @dLatitude = @dLat2InRad - @dLat1InRad; /* Intermediate result a. */ DECLARE @a as float(53); SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad) * COS (@dLat2InRad) * SQUARE(SIN (@dLongitude / 2.0)); /* Intermediate result c (great circle distance in Radians). */ DECLARE @c as real; SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a)); DECLARE @kEarthRadius as real; If @Metric = 'km' SET @kEarthRadius = 6376.5 /* kms */ Else SET @kEarthRadius = 3956.0 /* miles */ DECLARE @dDistance as real; SET @dDistance = @kEarthRadius * @c; return (@dDistance); END

Wednesday, October 10, 2012

Get All Tables & Its Column Name of a DB

select T2.name as 'TableName',T1.name as 'ColumnName' from sys.columns T1 inner join sys.tables T2 on (T1.object_id = T2.object_id) order by TableName

Wednesday, February 8, 2012

Convert Comma Separated Values into Rows

declare @Table1 table (ID int, data varchar(500));
insert into @Table1
select 1, 'data1,data2,data3' UNION ALL
select 1, 'data2,data4' UNION ALL
select 2, 'data2,data3' UNION ALL
select 3, 'data1,data4,data3';


select * from @Table1

;WITH CTE AS
(
-- Assign a sequential row number to each ID, restart with each change in ID
SELECT t1.ID, t1.data, RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY data)
FROM @Table1 t1
),
CTE2 AS
(
-- assign a sequential row number to each ID/item, restart with each change in ID/item
SELECT t1.ID, ds.Items, RN, RN2 = ROW_NUMBER() OVER (PARTITION BY t1.ID, ds.ITEMs ORDER BY t1.ID, t1.RN)
FROM CTE t1
CROSS APPLY dbo.SplitString(t1.data, ',') ds
)

-- get the results, only getting the first item for each id.
SELECT ID, Items FROM CTE2 WHERE RN2 = 1
ORDER BY ID, RN