Curr - Prev - Next Record Operation

DECLARE @T_SiteRoute TABLE (RouteOrder INT,RouteOrderRank int,DeviceGroupName NVARCHAR(50))

INSERT INTO @T_SiteRoute(RouteOrder, DeviceGroupName)
SELECT RouteOrder = 1, DeviceGroupName = '4G'
UNION All SELECT RouteOrder = 2, DeviceGroupName = '3G'
UNION All SELECT RouteOrder = 3, DeviceGroupName = 'CSR'
UNION All SELECT RouteOrder = 4, DeviceGroupName = 'LR1'
UNION All SELECT RouteOrder = 5, DeviceGroupName = 'LR2'
UNION All SELECT RouteOrder = 6, DeviceGroupName = 'LR3'
UNION All SELECT RouteOrder = 7, DeviceGroupName = 'LR4'
UNION All SELECT RouteOrder = 8, DeviceGroupName = 'MR1'
UNION All SELECT RouteOrder = 9, DeviceGroupName = 'MR2'
UNION All SELECT RouteOrder = 10, DeviceGroupName = 'MR3'
UNION All SELECT RouteOrder = 11, DeviceGroupName = 'HR1'
UNION All SELECT RouteOrder = 12, DeviceGroupName = 'HR2'
UNION All SELECT RouteOrder = 13, DeviceGroupName = 'EXFO'

UPDATE T1 SET T1.RouteOrderRank = T2.RouteOrderRank
FROM @T_SiteRoute T1
INNER JOIN (
SELECT T.RouteOrder ,
       RouteOrderRank = ROW_NUMBER() OVER (ORDER BY T.RouteOrder) ,
       T.DeviceGroupName
FROM @T_SiteRoute T
) T2 ON T1.RouteOrder = T2.RouteOrder

SELECT
    Curr.RouteOrderRank
    ,Curr_DeviceGroupName = Curr.DeviceGroupName
,Nxt_DeviceGroupName = nxt.DeviceGroupName
FROM @T_SiteRoute Curr
LEFT OUTER JOIN @T_SiteRoute Nxt ON
Nxt.RouteOrderRank = (SELECT MIN(RouteOrderRank) FROM @T_SiteRoute WHERE RouteOrderRank > Curr.RouteOrderRank)

SELECT
    Curr.RouteOrderRank
    ,Curr_DeviceGroupName = Curr.DeviceGroupName
,Prev_DeviceGroupName = Prev.DeviceGroupName
FROM @T_SiteRoute Curr
left OUTER JOIN @T_SiteRoute Prev ON
curr.RouteOrderRank = (SELECT MIN(RouteOrderRank) FROM @T_SiteRoute WHERE RouteOrderRank > Prev.RouteOrderRank)

Difference between same column Value in Current & Previous Row

DECLARE @Tbl TABLE(RowID INT,RawValue int)

INSERT INTO @Tbl(RowID,RawValue)
values('1','12'),('2','25'),('3','10'),('4','50'),('5','35')

SELECT * FROM @Tbl
SELECT *,SUM(RawValue) OVER (PARTITION BY 1) FROM @Tbl

SELECT
   [current].RowID,
   [current].RawValue,
   ISNULL([next].RawValue, 0) - [current].RawValue
FROM
   @Tbl       AS [current]
LEFT JOIN
   @Tbl       AS [next]
      ON [next].RowID = (SELECT MIN(RowID) FROM @Tbl WHERE RowID > [current].RowID)

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

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

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

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

How to generate an AutoAlphaNumeric Code in SQL

-- To know @ just copy & paste

declare
@sLength tinyint,
@AutoCode varchar(50)

set @sLength = 8 -- Can be changed

DECLARE @counter tinyint
DECLARE @nextChar char(1)

set @AutoCode = ''

SET @counter = 1

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN

SELECT @AutoCode = @AutoCode + @nextChar
SET @counter = @counter + 1

END
END

select @AutoCode AutoCode,UPPER(@AutoCode) 'AutoCode(Upper)',LOWER(@AutoCode) 'AutoCode(Lower)'

Use of Coalesce in SQL Query

Coalesce : pick the first not null value from the passing values.

-- To know @ just copy & paste


declare
@TBLCountry table(CountryID int,Country varchar(20),CityID int,CityName varchar(20))

insert into @TBLCountry(CountryID,Country,CityID,CityName)
values
(101,'India',301,'Indore'),
(101,'India',302,'Bhopal'),
(101,'India',303,'Agra'),
(101,'India',304,'Noida'),

(102,'US',301,'NY'),
(102,'US',302,'LA'),
(102,'US',303,'Chicago'),
(102,'US',304,'SJ')

-- Parameter

declare @CountryID int,@CityID int

-- Choose only one at a time

set @CountryID = -1
set @CityID = -1

--set @CountryID = 101
--set @CityID = -1

--set @CountryID = 101
--set @CityID = 301

--set @CountryID = -1
--set @CityID = 301

-- Query

if (@CountryID = -1) set @CountryID = null
if (@CityID = -1) set @CityID = null

select * from @TBLCountry T
where T.CountryID = coalesce(@CountryID,T.CountryID)
and T.CityID = coalesce(@CityID,T.CityID)

Difference b/w count(*) and count(columnname)

-- To know @ Just copy & paste

declare
@TBL table(EID int,Ename varchar(10))

insert into @TBL(EID,Ename)
values(1,'Amit'),(2,'Rakesh'),(3,null)

select * from @TBL

select COUNT(*) from @TBL

select COUNT(Ename) from @TBL -- Note :: null values will not be considered.

Calculate Median in SQL Server

-- Just copy & paste

declare @People table
(
Person varchar(5),
City varchar(10),
Age int
)

insert into @People(Person,City,Age)
select 'P1','Delhi',23 union all -- odd #
select 'P2','Delhi',43 union all
select 'P3','Delhi',29 union all

select 'P4','Bombay',15 union all -- single #

select 'P5','Kolkata',12 union all -- even #
select 'P6','Kolkata',55 union all
select 'P7','Kolkata',57 union all
select 'P8','Kolkata',61

select city,
AVG(age) as MedianAge
from
(
select City, Person, Age,
ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,
COUNT(*) over (partition by City) as CityCount
from @People
) T
where
T.AgeRank in (T.CityCount/2+1, (T.CityCount+1)/2)
group by
T.City