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