Dot Net

Wednesday, September 28, 2011

Get Unique No In SQL - Excel Column Format

-->>>>>>>>>>>Variable Declaration>>>>>>>>>>>

Declare @LastNo Varchar(max)='ZZ99999',
@NewNo Varchar(max) = '',
@NoOfDigit Int = 5,
@SeriesNo varchar(max) = '',
@SeriesChar varchar(max) = '',
@SeriesCharOld Varchar(max) = '',
@IncrementSeries Bit = 0,
/* Char Variable >>>>>*/
@CharA char(1)='A',@CharB char(1)='B',@CharC char(1)='C',@CharD char(1)='D',@CharE char(1)='E',
@CharF char(1)='F',@CharG char(1)='G',@CharH char(1)='H',@CharI char(1)='I',@CharJ char(1)='J',
@CharK char(1)='K',@CharL char(1)='L',@CharM char(1)='M',@CharN char(1)='N',@CharO char(1)='O',
@CharP char(1)='P',@CharQ char(1)='Q',@CharR char(1)='R',@CharS char(1)='S',@CharT char(1)='T',
@CharU char(1)='U',@CharV char(1)='V',@CharW char(1)='W',@CharX char(1)='X',@CharY char(1)='Y',
@CharZ char(1)='Z'
/* End Of char Variable<<<<<*/
Begin Try
-->>>Get Current Number>>>>>>>>>
Select @LastNo = Case When Len(RTrim(LTrim(@LastNo))) = 0
Then replace(STR(' ',@NoOfDigit),' ','0')
Else @LastNo End
Select @SeriesNo = right(@LastNo,@NoOfDigit)

If (@SeriesNo <> Replace(STR('9',@NoOfDigit),' ','9'))
Begin
Select @SeriesNo = Replace(replace(str((@SeriesNo + 1),@NoOfDigit),'0','0'),' ','0')
Set @SeriesChar = LEFT(@LastNo,LEN(@LastNo) - @NoOfDigit)
If Len(@SeriesChar) = 0
Begin
Set @SeriesChar = @CharA
End
End
Else
Begin
If Len(LTrim(RTrim(@LastNo))) <> 0
Begin
Set @SeriesCharOld = LEFT(@LastNo,LEN(@LastNo) - @NoOfDigit)
Set @IncrementSeries = 1
End
Else
Begin
Set @SeriesChar = @CharA
End
Set @SeriesNo = Replace(STR(1,@NoOfDigit),' ','0')
End
--<< -->>>>Get Series Character>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If (@IncrementSeries = 1)
Begin
--Set @SeriesChar = @SeriesCharOld
Declare @CharLen Int = 0, @LastChar Char(1)=''
Set @CharLen = Len(LTrim(RTrim(@SeriesCharOld)))
Set @LastChar = Substring(LTrim(RTrim(@SeriesCharOld)),@CharLen , 1)

If @LastChar = @CharA
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharB
End
Else If @LastChar = @CharB
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharC
End
Else If @LastChar = @CharC
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharD
End
Else If @LastChar = @CharD
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharE
End
Else If @LastChar = @CharE
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharF
End
Else If @LastChar = @CharF
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharG
End
Else If @LastChar = @CharG
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharH
End
Else If @LastChar = @CharH
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharI
End
Else If @LastChar = @CharI
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharJ
End
Else If @LastChar = @CharJ
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharK
End
Else If @LastChar = @CharK
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharL
End
Else If @LastChar = @CharL
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharM
End
Else If @LastChar = @CharM
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharN
End
Else If @LastChar = @CharN
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharO
End
Else If @LastChar = @CharO
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharP
End
Else If @LastChar = @CharP
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharQ
End
Else If @LastChar = @CharQ
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharR
End
Else If @LastChar = @CharR
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharS
End
Else If @LastChar = @CharS
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharT
End
Else If @LastChar = @CharT
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharU
End
Else If @LastChar = @CharU
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharV
End
Else If @LastChar = @CharV
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharW
End
Else If @LastChar = @CharW
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharX
End
Else If @LastChar = @CharX
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharY
End
Else If @LastChar = @CharY
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharZ
End
Else If @LastChar = @CharZ
Begin
If @SeriesCharOld = Replace(Replace(STR('0', Len(@LastNo)-@NoOfDigit) ,' ',@CharZ),'0',@CharZ)
Begin
Set @SeriesChar = @CharA + Replace(@SeriesCharOld,@CharZ,@CharA)
End
Else
Begin
Declare @LoopValue Int = 0, @LoopChar char(1) = ''
Set @LoopValue = @CharLen
While (@LoopValue>0)
Begin
Set @LoopChar = Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue, 1)
If @LoopChar = @CharZ
Begin
print Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharA+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)

Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharA+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = @LoopValue - 1
End
Else If @LoopChar = @CharA
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharB+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharB
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharC+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharC
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharD+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharD
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharE+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharE
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharF+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharF
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharG+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharG
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharH +Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharH
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharI+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharI
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharJ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharJ
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharK+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharK
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharL+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharL
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharM+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharM
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharN+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharN
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharO+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharO
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharP+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharP
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharQ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharQ
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharR+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharR
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharS+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharS
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharT+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharT
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharU+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharU
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharV+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharV
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharW+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharW
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharX+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharX
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharY+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharY
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharZ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else
Begin
Set @LoopValue = @LoopValue - 1
End

End
Set @SeriesChar = @SeriesCharOld
End
End
End

--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-->>>>>>>>Get Your Unique Number >>>>>>>>>>>>>>>>>>>>>
Select @SeriesChar + @SeriesNo
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

End Try
Begin Catch
Select ERROR_MESSAGE() As Error
End Catch

Get Unique No In SQL - Excel Column Format

Tuesday, September 27, 2011

Nth Highest In SQL

If Object_Id('tempdb.dbo.#EmployeeMaster') Is Not Null
Begin
Drop Table dbo.#EmployeeMaster
End
Go
Create Table dbo.#EmployeeMaster
(
EmpNo Int,
DeptId Int,
Salary Numeric(18,2)
)
Insert Into dbo.#EmployeeMaster values(1,1,4000)
Insert Into dbo.#EmployeeMaster values(2,2,1000)
Insert Into dbo.#EmployeeMaster values(3,3,400)
Insert Into dbo.#EmployeeMaster values(4,1,100)
Insert Into dbo.#EmployeeMaster values(5,2,5000)
Insert Into dbo.#EmployeeMaster values(6,3,25000)
Insert Into dbo.#EmployeeMaster values(7,1,56222)
Insert Into dbo.#EmployeeMaster values(8,2,5652)
Insert Into dbo.#EmployeeMaster values(9,3,1000)

Select *From dbo.#EmployeeMaster

Select e.EmpNo, e.DeptId, e.Salary
From dbo.#EmployeeMaster e
Where 1>=
(Select COUNT(1) From dbo.#EmployeeMaster e1
Where e.Salary <= e1.Salary And e.DeptId = e1.DeptId )

=============================================

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
EmpNo DeptId Salary
----------- ----------- ---------------------------------------
1 1 4000.00
2 2 1000.00
3 3 400.00
4 1 100.00
5 2 5000.00
6 3 25000.00
7 1 56222.00
8 2 5652.00
9 3 1000.00

(9 row(s) affected)

EmpNo DeptId Salary
----------- ----------- ---------------------------------------
6 3 25000.00
7 1 56222.00
8 2 5652.00

(3 row(s) affected)

Friday, September 16, 2011

Split String to Table

If OBJECT_ID('dbo.UdfSplitStringToTable') Is Not Null
Begin
Drop Function dbo.UdfSplitStringToTable
End
/*
Function That return Table using split with specified char from string

e.g:

Select * From dbo.UdfSplitStringToTable('1¶2¶3¶4','¶')
*/
Go
Create Function dbo.UdfSplitStringToTable
(
@String varchar(max),
@char char(1)
)
Returns @Result Table(Item Varchar(max))
As
Begin

Declare @xDoc XML
Set @xDoc = ''
+ Replace(@String,@char,'')
+''

Insert Into @Result
Select T2.Loc.value('.','Varchar(max)')
From
@xDoc.nodes('/Ids/Id') As T2(Loc)

Return
End

Dot Net

Wednesday, September 28, 2011

Get Unique No In SQL - Excel Column Format

Get Unique No In SQL - Excel Column Format

Tuesday, September 27, 2011

Nth Highest In SQL

Friday, September 16, 2011

Split String to Table

Dhaval CHauhan

surat - Google News

Earn By Survey