SQL Server Select Top Statement:
SELECT TOP number | number PERCENT ColumnName(s)
FROM TableName
Example 1:
select TOP 1 * from (
select ('Umar')PersonName union
select ('Zubair') union
select ('Ali') union
select ('Muhammad') union
select ('Bilal')
)v0 Order by PersonName Asc
#Results:
PersonName
Ali
Example 2:
select TOP 1 * from (
select ('Umar')PersonName union
select ('Zubair') union
select ('Ali') union
select ('Muhammad') union
select ('Bilal')
)v1 Order by PersonName Desc
#Results:
PersonName
Zubair
Example 3:
select TOP 3 * from (
select ('Umar')PersonName union
select ('Zubair') union
select ('Ali') union
select ('Muhammad') union
select ('Bilal')
)v2 Order by PersonName Asc
#Results:
PersonName
Ali
Bilal
Muhammad
Example 4:
select TOP 3 * from (
select ('Umar')PersonName union
select ('Zubair') union
select ('Ali') union
select ('Muhammad') union
select ('Bilal')
)v2 Order by PersonName Desc
PersonName
Zubair
Umar
Muhammad
Example 5:
select TOP 30 PERCENT * from (
select ('Umar')PersonName union
select ('Zubair') union
select ('Ali') union
select ('Muhammad') union
select ('Bilal')
)v2 Order by PersonName Desc
#Results:
PersonName
Zubair
Umar
Jumat, 31 Agustus 2012
Kamis, 30 Agustus 2012
sql select count
SQL Server COUNT() function use to get number of rows that matches a specified criteria.
Example 1:
select COUNT(ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35)
)v0
#Results:
NumOfItem
6
Example 2:
select COUNT(DISTINCT ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35)
)v1
#Results:
NumOfItem
2
Example 3:
See following examples and compare with previous query:
select COUNT(DISTINCT ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35) union
select (null),('P0003'),1
)v2
#Results:
NumOfItem
2
Notes:
SQL Server DISTINCT sintax added to get number of unique item name
Results Example 2 and 3 same couse NULL values will not be counted
Example 1:
select COUNT(ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35)
)v0
#Results:
NumOfItem
6
Example 2:
select COUNT(DISTINCT ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35)
)v1
#Results:
NumOfItem
2
Example 3:
See following examples and compare with previous query:
select COUNT(DISTINCT ItemName)NumOfItem from (
select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
select ('pencil'),('P0001'),(20) union
select ('pencil'),('P0001'),(30) union
select ('pen'),('P0002'),(15) union
select ('pen'),('P0002'),(25) union
select ('pen'),('P0002'),(35) union
select (null),('P0003'),1
)v2
#Results:
NumOfItem
2
Notes:
SQL Server DISTINCT sintax added to get number of unique item name
Results Example 2 and 3 same couse NULL values will not be counted
convert month name to number sql
Use Combine DATEPART() and CAST() SQL Server Function.
For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth
#Results:
NumOfThisMonth
2
select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
select (1)OrdNum,'January'MonthNames union
select (2),'February' union
select (3),'March' union
select (4),'April' union
select (5),'May' union
select (6),'June' union
select (7),'July' union
select (8),'August' union
select (9),'September' union
select (10),'October' union
select (11),'November' union
select (12),'December'
)v0
Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
#Results:
monthNumber
10
select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
select (1)OrdNum,'JAN'MonthNames union
select (2),'FEB' union
select (3),'MAR' union
select (4),'APR' union
select (5),'MAY' union
select (6),'JUN' union
select (7),'JUL' union
select (8),'AUG' union
select (9),'SEP' union
select (10),'OCT' union
select (11),'NOV' union
select (12),'DEC'
)v1
OrdNum NumOfMonth monthNumber
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
11 11 11
12 12 12
For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth
#Results:
NumOfThisMonth
2
select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
select (1)OrdNum,'January'MonthNames union
select (2),'February' union
select (3),'March' union
select (4),'April' union
select (5),'May' union
select (6),'June' union
select (7),'July' union
select (8),'August' union
select (9),'September' union
select (10),'October' union
select (11),'November' union
select (12),'December'
)v0
Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
#Results:
monthNumber
10
select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
select (1)OrdNum,'JAN'MonthNames union
select (2),'FEB' union
select (3),'MAR' union
select (4),'APR' union
select (5),'MAY' union
select (6),'JUN' union
select (7),'JUL' union
select (8),'AUG' union
select (9),'SEP' union
select (10),'OCT' union
select (11),'NOV' union
select (12),'DEC'
)v1
OrdNum NumOfMonth monthNumber
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
11 11 11
12 12 12
Rabu, 29 Agustus 2012
average in sql
AVG() on SQL Server Function use to get average value of a numeric column.
select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)
#Results:
Num ItemName ItemPrice
1 Product One 2500000
2 Product Two 3500000
3 Product Three 4500000
4 Product Four 6500000
5 Product Five 7500000
select AVG(ItemPrice)AvgItemPrice from (
select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)
)v0
#Results:
AvgItemPrice
4900000
select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)
#Results:
Num ItemName ItemPrice
1 Product One 2500000
2 Product Two 3500000
3 Product Three 4500000
4 Product Four 6500000
5 Product Five 7500000
select AVG(ItemPrice)AvgItemPrice from (
select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)
)v0
#Results:
AvgItemPrice
4900000
sql datepart
DATEPART() SQL Server function Statement:
DATEPART(datepart,date)
declare @ThisDate datetime set @ThisDate='2012-08-30 08:45:08.717'
select ('yy')DATEPARTInit,('year')UseForShow,DATEPART(yy,@ThisDate)ShowData Union
select ('qq'),('quarter'),DATEPART(qq,@ThisDate) Union
select ('mm'),('month'),DATEPART(mm,@ThisDate) Union
select ('dy'),('dayofyear'),DATEPART(dy,@ThisDate) Union
select ('dd'),('day'),DATEPART(dd,@ThisDate) Union
select ('wk'),('week'),DATEPART(wk,@ThisDate) Union
select ('dw'),('weekday'),DATEPART(dw,@ThisDate) Union
select ('hh'),('hour'),DATEPART(hh,@ThisDate) Union
select ('mi'),('minute'),DATEPART(mi,@ThisDate) Union
select ('ss'),('second'),DATEPART(ss,@ThisDate) Union
select ('ms'),('second'),DATEPART(ms,@ThisDate) --union
--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union
--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)
#Results:
DatePartInit UseForShow ShowData
dd day 30
dw weekday 5
dy dayofyear 243
hh hour 8
mi minute 45
mm month 8
ms second 717
qq quarter 3
ss second 8
wk week 35
yy year 2012
DATEPART(datepart,date)
declare @ThisDate datetime set @ThisDate='2012-08-30 08:45:08.717'
select ('yy')DATEPARTInit,('year')UseForShow,DATEPART(yy,@ThisDate)ShowData Union
select ('qq'),('quarter'),DATEPART(qq,@ThisDate) Union
select ('mm'),('month'),DATEPART(mm,@ThisDate) Union
select ('dy'),('dayofyear'),DATEPART(dy,@ThisDate) Union
select ('dd'),('day'),DATEPART(dd,@ThisDate) Union
select ('wk'),('week'),DATEPART(wk,@ThisDate) Union
select ('dw'),('weekday'),DATEPART(dw,@ThisDate) Union
select ('hh'),('hour'),DATEPART(hh,@ThisDate) Union
select ('mi'),('minute'),DATEPART(mi,@ThisDate) Union
select ('ss'),('second'),DATEPART(ss,@ThisDate) Union
select ('ms'),('second'),DATEPART(ms,@ThisDate) --union
--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union
--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)
#Results:
DatePartInit UseForShow ShowData
dd day 30
dw weekday 5
dy dayofyear 243
hh hour 8
mi minute 45
mm month 8
ms second 717
qq quarter 3
ss second 8
wk week 35
yy year 2012
sql date convert
SQL Server Convert Date Time Function Statement:
Convert(data_type(length),expression,style)
declare @CurrDate datetime
set @CurrDate='2012-08-30 07:54:52.293'
select (100)ShowStyle,Convert(nvarchar,@CurrDate,100)ShowOutput Union
select (101),Convert(nvarchar,@CurrDate,101) Union
select (102),Convert(nvarchar,@CurrDate,102) Union
select (103),Convert(nvarchar,@CurrDate,103) Union
select (104),Convert(nvarchar,@CurrDate,104) Union
select (105),Convert(nvarchar,@CurrDate,105) Union
select (106),Convert(nvarchar,@CurrDate,106) Union
select (107),Convert(nvarchar,@CurrDate,107) Union
select (108),Convert(nvarchar,@CurrDate,108) Union
select (109),Convert(nvarchar,@CurrDate,109) Union
select (110),Convert(nvarchar,@CurrDate,110) Union
select (111),Convert(nvarchar,@CurrDate,111) Union
select (112),Convert(nvarchar,@CurrDate,112) Union
select (113),Convert(nvarchar,@CurrDate,113) Union
select (114),Convert(nvarchar,@CurrDate,114) Union
select (120),Convert(nvarchar,@CurrDate,120) Union
select (121),Convert(nvarchar,@CurrDate,121) Union
select (126),Convert(nvarchar,@CurrDate,126) Union
select (130),Convert(nvarchar,@CurrDate,130) Union
select (131),Convert(nvarchar,@CurrDate,131)
#Results:
ShowStyle ShowOutput
100 Aug 30 2012 7:54AM
101 08/30/2012
102 2012.08.30
103 30/08/2012
104 30.08.2012
105 30-08-2012
106 30 Aug 2012
107 Aug 30, 2012
108 07:54:52
109 Aug 30 2012 7:54:52:293AM
110 08-30-2012
111 2012/08/30
112 20120830
113 30 Aug 2012 07:54:52:293
114 07:54:52:293
120 2012-08-30 07:54:52
121 2012-08-30 07:54:52.293
126 2012-08-30T07:54:52.293
130 13 شوال 1433 7:54:52:293AM
131 13/10/1433 7:54:52:293AM
Convert(data_type(length),expression,style)
declare @CurrDate datetime
set @CurrDate='2012-08-30 07:54:52.293'
select (100)ShowStyle,Convert(nvarchar,@CurrDate,100)ShowOutput Union
select (101),Convert(nvarchar,@CurrDate,101) Union
select (102),Convert(nvarchar,@CurrDate,102) Union
select (103),Convert(nvarchar,@CurrDate,103) Union
select (104),Convert(nvarchar,@CurrDate,104) Union
select (105),Convert(nvarchar,@CurrDate,105) Union
select (106),Convert(nvarchar,@CurrDate,106) Union
select (107),Convert(nvarchar,@CurrDate,107) Union
select (108),Convert(nvarchar,@CurrDate,108) Union
select (109),Convert(nvarchar,@CurrDate,109) Union
select (110),Convert(nvarchar,@CurrDate,110) Union
select (111),Convert(nvarchar,@CurrDate,111) Union
select (112),Convert(nvarchar,@CurrDate,112) Union
select (113),Convert(nvarchar,@CurrDate,113) Union
select (114),Convert(nvarchar,@CurrDate,114) Union
select (120),Convert(nvarchar,@CurrDate,120) Union
select (121),Convert(nvarchar,@CurrDate,121) Union
select (126),Convert(nvarchar,@CurrDate,126) Union
select (130),Convert(nvarchar,@CurrDate,130) Union
select (131),Convert(nvarchar,@CurrDate,131)
#Results:
ShowStyle ShowOutput
100 Aug 30 2012 7:54AM
101 08/30/2012
102 2012.08.30
103 30/08/2012
104 30.08.2012
105 30-08-2012
106 30 Aug 2012
107 Aug 30, 2012
108 07:54:52
109 Aug 30 2012 7:54:52:293AM
110 08-30-2012
111 2012/08/30
112 20120830
113 30 Aug 2012 07:54:52:293
114 07:54:52:293
120 2012-08-30 07:54:52
121 2012-08-30 07:54:52.293
126 2012-08-30T07:54:52.293
130 13 شوال 1433 7:54:52:293AM
131 13/10/1433 7:54:52:293AM
right in sql
--right sql server function for static position
select RIGHT(MyAnimals,10)AnimalName,LEFT(MyAnimals, CHARINDEX(' ', MyAnimals)-1)qty from (
select ('5 chickens A')MyAnimals union
select ('15 chickens B') union
select ('25 chickens C') union
select ('35 chickens D') union
select ('45 chickens E')
)v0
#Results:
AnimalName qty
chickens B 15
chickens C 25
chickens D 35
chickens E 45
chickens A 5
--right sql server function for dinamic position
select MyAnimals,quantity,RIGHT(MyAnimals,LEN(MyAnimals)-LEN(quantity))AnimalName from (
select MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity from (
select ('5 chickens')MyAnimals union
select ('14 goats') union
select ('7 chickens') union
select ('10 cows') union
select ('500 ducks')
)v2
)v3
#Results:
MyAnimals quantity AnimalName
10 cows 10 cows
14 goats 14 goats
5 chickens 5 chickens
500 ducks 500 ducks
7 chickens 7 chickens
select RIGHT(MyAnimals,10)AnimalName,LEFT(MyAnimals, CHARINDEX(' ', MyAnimals)-1)qty from (
select ('5 chickens A')MyAnimals union
select ('15 chickens B') union
select ('25 chickens C') union
select ('35 chickens D') union
select ('45 chickens E')
)v0
#Results:
AnimalName qty
chickens B 15
chickens C 25
chickens D 35
chickens E 45
chickens A 5
--right sql server function for dinamic position
select MyAnimals,quantity,RIGHT(MyAnimals,LEN(MyAnimals)-LEN(quantity))AnimalName from (
select MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity from (
select ('5 chickens')MyAnimals union
select ('14 goats') union
select ('7 chickens') union
select ('10 cows') union
select ('500 ducks')
)v2
)v3
#Results:
MyAnimals quantity AnimalName
10 cows 10 cows
14 goats 14 goats
5 chickens 5 chickens
500 ducks 500 ducks
7 chickens 7 chickens
Label:
charindex,
len sql,
right sql server,
sql right
sql substring
Substring function on sql server is a function like mid on asp, to get one or more characters in the middle of a set of characters.
--substring sql server function for static position
select notes,substring(notes,11,3)qty from (
select ('bicycle A 285 unit')notes union
select ('bicycle B 153 unit') union
select ('bicycle C 120 unit') union
select ('bicycle D 569 unit') union
select ('bicycle E 587 unit')
)v1
#Results:
notes qty
bicycle A 285 unit 285
bicycle B 153 unit 153
bicycle C 120 unit 120
bicycle D 569 unit 569
bicycle E 587 unit 587
--substring sql server function for dinamic position
select notes,substring(notes, CHARINDEX(' ',notes)+1,3)qty from (
select ('bicycle 285 unit')notes union
select ('motorcycle 153 unit') union
select ('cabriolet 120 unit') union
select ('motor 406 unit') union
select ('pedicab 587 unit')
)v1
notes qty
bicycle 285 unit 285
cabriolet 120 unit 120
motor 406 unit 406
motorcycle 153 unit 153
pedicab 587 unit 587
--substring sql server function for static position
select notes,substring(notes,11,3)qty from (
select ('bicycle A 285 unit')notes union
select ('bicycle B 153 unit') union
select ('bicycle C 120 unit') union
select ('bicycle D 569 unit') union
select ('bicycle E 587 unit')
)v1
#Results:
notes qty
bicycle A 285 unit 285
bicycle B 153 unit 153
bicycle C 120 unit 120
bicycle D 569 unit 569
bicycle E 587 unit 587
--substring sql server function for dinamic position
select notes,substring(notes, CHARINDEX(' ',notes)+1,3)qty from (
select ('bicycle 285 unit')notes union
select ('motorcycle 153 unit') union
select ('cabriolet 120 unit') union
select ('motor 406 unit') union
select ('pedicab 587 unit')
)v1
notes qty
bicycle 285 unit 285
cabriolet 120 unit 120
motor 406 unit 406
motorcycle 153 unit 153
pedicab 587 unit 587
Selasa, 28 Agustus 2012
left sql function
-- left sql server function for static position
select CatNo,LEFT(CatNo,8)NewCatNo from (
select ('MA 30012~100')CatNo union
select ('MB 30542~20012') union
select ('MB 30031~321') union
select ('CA 30035~356487') union
select ('CB 30041~310205') union
select ('CC 31112~333')
)v0
#Results:
CatNo NewCatNo
CA 30035~356487 CA 30035
CB 30041~310205 CB 30041
CC 31112~333 CC 31112
MA 30012~100 MA 30012
MB 30031~321 MB 30031
MB 30542~20012 MB 30542
-- sql server left function for dinamic position
select CatNo,LEFT(CatNo,CHARINDEX('~',CatNo)-1)NewCatNo from (
select ('MA 30012567~100')CatNo union
select ('MB 3054231~20012') union
select ('MB 30031~321') union
select ('CA 300352345~356487') union
select ('CB 30041056879~310205') union
select ('CC 311~333')
)v0
CatNo NewCatNo
CA 300352345~356487 CA 300352345
CB 30041056879~310205 CB 30041056879
CC 311~333 CC 311
MA 30012567~100 MA 30012567
MB 30031~321 MB 30031
MB 3054231~20012 MB 3054231
select CatNo,LEFT(CatNo,8)NewCatNo from (
select ('MA 30012~100')CatNo union
select ('MB 30542~20012') union
select ('MB 30031~321') union
select ('CA 30035~356487') union
select ('CB 30041~310205') union
select ('CC 31112~333')
)v0
#Results:
CatNo NewCatNo
CA 30035~356487 CA 30035
CB 30041~310205 CB 30041
CC 31112~333 CC 31112
MA 30012~100 MA 30012
MB 30031~321 MB 30031
MB 30542~20012 MB 30542
-- sql server left function for dinamic position
select CatNo,LEFT(CatNo,CHARINDEX('~',CatNo)-1)NewCatNo from (
select ('MA 30012567~100')CatNo union
select ('MB 3054231~20012') union
select ('MB 30031~321') union
select ('CA 300352345~356487') union
select ('CB 30041056879~310205') union
select ('CC 311~333')
)v0
CatNo NewCatNo
CA 300352345~356487 CA 300352345
CB 30041056879~310205 CB 30041056879
CC 311~333 CC 311
MA 30012567~100 MA 30012567
MB 30031~321 MB 30031
MB 3054231~20012 MB 3054231
break on sql
declare @loop int
declare @spell nvarchar(15)
declare @query nvarchar(300)
set @loop=10
set @spell=''
set @query=''
WHILE not @loop =20
Begin
if @loop=11
set @spell='''Eleven'''
else if @loop=12
set @spell='''Twelve'''
else if @loop=13
set @spell='''Thirteen'''
else if @loop=14
set @spell='''Fourteen'''
else if @loop=15
set @spell='''Fifteen'''
else if @loop=16
set @spell='''Sixteen'''
else if @loop=17
BEGIN
set @spell='''Seventeen'''
BREAK
END
else if @loop=18
set @spell='''Eighteen'''
else if @loop=19
set @spell='''Nineteen'''
else
set @spell='''Twenty'''
set @loop=@loop+1
if @query=''
set @query='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
else
set @query=@query+' union '+'select '+convert(nvarchar,@loop)+','+@spell
End
exec (@query)
#Results:
num spell
11 Twenty
12 Eleven
13 Twelve
14 Thirteen
15 Fourteen
16 Fifteen
17 Sixteen
declare @spell nvarchar(15)
declare @query nvarchar(300)
set @loop=10
set @spell=''
set @query=''
WHILE not @loop =20
Begin
if @loop=11
set @spell='''Eleven'''
else if @loop=12
set @spell='''Twelve'''
else if @loop=13
set @spell='''Thirteen'''
else if @loop=14
set @spell='''Fourteen'''
else if @loop=15
set @spell='''Fifteen'''
else if @loop=16
set @spell='''Sixteen'''
else if @loop=17
BEGIN
-- Starting from this script down will never executed
set @spell='''Seventeen'''
BREAK
END
else if @loop=18
set @spell='''Eighteen'''
else if @loop=19
set @spell='''Nineteen'''
else
set @spell='''Twenty'''
set @loop=@loop+1
if @query=''
set @query='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
else
set @query=@query+' union '+'select '+convert(nvarchar,@loop)+','+@spell
End
exec (@query)
#Results:
num spell
11 Twenty
12 Eleven
13 Twelve
14 Thirteen
15 Fourteen
16 Fifteen
17 Sixteen
Senin, 27 Agustus 2012
sql if else
declare @loop int
declare @spell nvarchar(15)
declare @qry nvarchar(300)
set @loop=0
set @spell=''
set @qry=''
WHILE not @loop =10
Begin
set @loop=@loop+1
if @loop=1
set @spell='''One'''
else if @loop=2
set @spell='''Two'''
else if @loop=3
set @spell='''Three'''
else if @loop=4
set @spell='''Four'''
else if @loop=5
set @spell='''Five'''
else if @loop=6
set @spell='''Six'''
else if @loop=7
set @spell='''Seven'''
else if @loop=8
set @spell='''Eight'''
else if @loop=9
set @spell='''Nine'''
else
set @spell='''Ten'''
if @qry=''
set @qry='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
else
set @qry=@qry+' union '+'select '+convert(nvarchar,@loop)+','+@spell
End
exec (@qry)
#Results:
num spell
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
declare @spell nvarchar(15)
declare @qry nvarchar(300)
set @loop=0
set @spell=''
set @qry=''
WHILE not @loop =10
Begin
set @loop=@loop+1
if @loop=1
set @spell='''One'''
else if @loop=2
set @spell='''Two'''
else if @loop=3
set @spell='''Three'''
else if @loop=4
set @spell='''Four'''
else if @loop=5
set @spell='''Five'''
else if @loop=6
set @spell='''Six'''
else if @loop=7
set @spell='''Seven'''
else if @loop=8
set @spell='''Eight'''
else if @loop=9
set @spell='''Nine'''
else
set @spell='''Ten'''
if @qry=''
set @qry='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
else
set @qry=@qry+' union '+'select '+convert(nvarchar,@loop)+','+@spell
End
exec (@qry)
#Results:
num spell
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
sql exec
Exec sintax on sql server usually use to execute parameter
see example below:
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status
#Results:
usr_name access_sttus active_status
soemaryanto admin active
declare @qry nvarchar(100)
set @qry=('select (''soemaryanto'')usr_name,(''admin'')access_sttus,(''active'')active_status')
select (@qry)text_qry
exec (@qry)
#Results:
text_qry
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status
usr_name access_sttus active_status
soemaryanto admin active
another example exec sql server statement
declare @count int
declare @qry nvarchar(100)
declare @qryBuffer nvarchar(200)
set @count=0
set @qry=''
set @qryBuffer=''
WHILE not @count =10
BEGIN
set @count=@count+1
if @qryBuffer=''
set @qryBuffer=('select ('+convert(nvarchar,@count)+')AutoNum')
else
set @qryBuffer=@qryBuffer+' union '+('select ('+convert(nvarchar,@count)+')')
END
exec (@qryBuffer)
#Results:
AutoNum
1
2
3
4
5
6
7
8
9
10
see example below:
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status
#Results:
usr_name access_sttus active_status
soemaryanto admin active
declare @qry nvarchar(100)
set @qry=('select (''soemaryanto'')usr_name,(''admin'')access_sttus,(''active'')active_status')
select (@qry)text_qry
exec (@qry)
#Results:
text_qry
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status
usr_name access_sttus active_status
soemaryanto admin active
another example exec sql server statement
declare @count int
declare @qry nvarchar(100)
declare @qryBuffer nvarchar(200)
set @count=0
set @qry=''
set @qryBuffer=''
WHILE not @count =10
BEGIN
set @count=@count+1
if @qryBuffer=''
set @qryBuffer=('select ('+convert(nvarchar,@count)+')AutoNum')
else
set @qryBuffer=@qryBuffer+' union '+('select ('+convert(nvarchar,@count)+')')
END
exec (@qryBuffer)
#Results:
AutoNum
1
2
3
4
5
6
7
8
9
10
Minggu, 26 Agustus 2012
day name in sql
SELECT DATENAME(dw, 1)NameofThisDay
#Results:
NameofThisDay
Tuesday
SELECT DATENAME(dw, '2012-08-27 11:12:38.803')NameofThisDay
#Results:
NameofThisDay
Monday
Another example with complex case to get days a week with while loop sql server statement
declare @start int,@end int
declare @qry nvarchar(100)
declare @daysAWeek nvarchar(1000)
set @start=1
set @end=8
set @daysAWeek=''
while not (@start=@end)
begin
if @start=@end-1
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
DateName(dw,'+convert(nvarchar,@start)+')dayName')qry)
else
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
DateName(dw,'+convert(nvarchar,@start)+')dayName union ')qry)
set @daysAWeek=@daysAWeek+@qry
set @start=@start+1
end
exec (@daysAWeek)
#Results:
dayNumber dayName
1 Tuesday
2 Wednesday
3 Thursday
4 Friday
5 Saturday
6 Sunday
7 Monday
#Results:
NameofThisDay
Tuesday
SELECT DATENAME(dw, '2012-08-27 11:12:38.803')NameofThisDay
#Results:
NameofThisDay
Monday
Another example with complex case to get days a week with while loop sql server statement
declare @start int,@end int
declare @qry nvarchar(100)
declare @daysAWeek nvarchar(1000)
set @start=1
set @end=8
set @daysAWeek=''
while not (@start=@end)
begin
if @start=@end-1
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
DateName(dw,'+convert(nvarchar,@start)+')dayName')qry)
else
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
DateName(dw,'+convert(nvarchar,@start)+')dayName union ')qry)
set @daysAWeek=@daysAWeek+@qry
set @start=@start+1
end
exec (@daysAWeek)
#Results:
dayNumber dayName
1 Tuesday
2 Wednesday
3 Thursday
4 Friday
5 Saturday
6 Sunday
7 Monday
sql month name
declare @start int,@end int
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
begin
if @start=@end-1
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
else
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName
union ')qry)
set @summary=@summary+@qry
set @start=@start+1
end
exec (@summary)
#Results:
monthNumber monthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
select DateName(mm,'2012-08-27 10:10:17.327')currMonthName
#Results:
currMonthName
August
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
begin
if @start=@end-1
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
else
set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName
union ')qry)
set @summary=@summary+@qry
set @start=@start+1
end
exec (@summary)
#Results:
monthNumber monthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
select DateName(mm,'2012-08-27 10:10:17.327')currMonthName
#Results:
currMonthName
August
sql loop
DECLARE @count int
SELECT @count =11
--Loop sql statement start here
WHILE @count > 0
BEGIN
SELECT @count=@count-1
IF @count=0
--BEGIN
-- BREAK
--END
IF @count=11
BEGIN
CONTINUE
END
--Show @count on Query Screen
PRINT @count
END
--End of Loop
#Results:
10
9
8
7
6
5
4
3
2
1
0
SELECT @count =11
--Loop sql statement start here
WHILE @count > 0
BEGIN
SELECT @count=@count-1
IF @count=0
--BEGIN
-- BREAK
--END
IF @count=11
BEGIN
CONTINUE
END
--Show @count on Query Screen
PRINT @count
END
--End of Loop
#Results:
10
9
8
7
6
5
4
3
2
1
0
Label:
sql advanced,
sql print statement,
sql while
Sabtu, 25 Agustus 2012
group by
Group by statement on sql server often use to show some field with one or more of them contain number which will be summed.
We still use example previous post, with small modification.
select product_name,SUM(qty)qty from (
select 'book notes'product_name,(4)qty union
select 'pencil',(15) union
select 'book notes',(3) union
select 'pencil',(10)
)view1 group by product_name order by product_name
#Results:
product_name qty
book notes 7
pencil 25
We still use example previous post, with small modification.
select product_name,SUM(qty)qty from (
select 'book notes'product_name,(4)qty union
select 'pencil',(15) union
select 'book notes',(3) union
select 'pencil',(10)
)view1 group by product_name order by product_name
#Results:
product_name qty
book notes 7
pencil 25
view sql
Views that i mean is we can use views method without creat views.
For example:
select product_name,qty from (
select 'book notes'product_name,(4)qty union
select 'pencil',(15) union
select 'book notes',(3) union
select 'pencil',(10)
)view1 where product_name='pencil' order by product_name
#Results:
product_name qty
pencil 15
pencil 10
For example:
select product_name,qty from (
select 'book notes'product_name,(4)qty union
select 'pencil',(15) union
select 'book notes',(3) union
select 'pencil',(10)
)view1 where product_name='pencil' order by product_name
#Results:
product_name qty
pencil 15
pencil 10
sql comment
When developing, we can always use remark code, a code will be ignored when execute. There are two script comment on sql server, comment line and block comment.
See example below:
this is
sql server
comment line
select 'computer'product_name,(5)qty
#Results:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'is'.
--this is
--sql server
--comment line
select 'computer'product_name,(5)qty
#Results:
product_name qty
computer 5
/*this is
sql server
block comment*/
select 'computer'product_name,(5)qty
#Results:
product_name qty
computer 5
See example below:
this is
sql server
comment line
select 'computer'product_name,(5)qty
#Results:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'is'.
--this is
--sql server
--comment line
select 'computer'product_name,(5)qty
#Results:
product_name qty
computer 5
/*this is
sql server
block comment*/
select 'computer'product_name,(5)qty
#Results:
product_name qty
computer 5
Kamis, 23 Agustus 2012
sql dateadd
declare @thisDate datetime
set @thisDate='2012-08-24 10:37:48.293'
select (5)num,('add day 5')'init',dateadd(d,5,@thisDate)'after_add' union
select (7),('add month 5'),dateadd(mm,5,@thisDate) union
select (8),('add quarter 5'),dateadd(qq,5,@thisDate) union
select (9),('add year 5'),dateadd(yy,5,@thisDate) union
select (6),('add week 5'),dateadd(wk,5,@thisDate) union
select (4),('add hour 5'),dateadd(hh,5,@thisDate) union
select (3),('add minute 5'),dateadd(mi,5,@thisDate) union
select (2),('add second 5'),dateadd(ss,5,@thisDate) union
select (1),('add mili second 5'),dateadd(ms,5,@thisDate) order by num asc
#Results:
num init after_add
1 add mili second 5 2012-08-24 10:37:48.300
2 add second 5 2012-08-24 10:37:53.293
3 add minute 5 2012-08-24 10:42:48.293
4 add hour 5 2012-08-24 15:37:48.293
5 add day 5 2012-08-29 10:37:48.293
6 add week 5 2012-09-28 10:37:48.293
7 add month 5 2013-01-24 10:37:48.293
8 add quarter 5 2013-11-24 10:37:48.293
9 add year 5 2017-08-24 10:37:48.293
set @thisDate='2012-08-24 10:37:48.293'
select (5)num,('add day 5')'init',dateadd(d,5,@thisDate)'after_add' union
select (7),('add month 5'),dateadd(mm,5,@thisDate) union
select (8),('add quarter 5'),dateadd(qq,5,@thisDate) union
select (9),('add year 5'),dateadd(yy,5,@thisDate) union
select (6),('add week 5'),dateadd(wk,5,@thisDate) union
select (4),('add hour 5'),dateadd(hh,5,@thisDate) union
select (3),('add minute 5'),dateadd(mi,5,@thisDate) union
select (2),('add second 5'),dateadd(ss,5,@thisDate) union
select (1),('add mili second 5'),dateadd(ms,5,@thisDate) order by num asc
#Results:
num init after_add
1 add mili second 5 2012-08-24 10:37:48.300
2 add second 5 2012-08-24 10:37:53.293
3 add minute 5 2012-08-24 10:42:48.293
4 add hour 5 2012-08-24 15:37:48.293
5 add day 5 2012-08-29 10:37:48.293
6 add week 5 2012-09-28 10:37:48.293
7 add month 5 2013-01-24 10:37:48.293
8 add quarter 5 2013-11-24 10:37:48.293
9 add year 5 2017-08-24 10:37:48.293
datediff in sql
declare @currDate datetime
declare @beforeDate datetime
set @currDate='2012-08-24 09:29:21.377'
set @beforeDate='2005-12-08 03:25:21.377'
select datediff(d,@beforeDate,@currDate)'days',
datediff(mm,@beforeDate,@currDate)'months', datediff(yy,@beforeDate,@currDate)'years'
#Results:
days months years
2451 80 7
or can suggest like it
select (datediff(d,@beforeDate,@currDate))'difference',('Days')'unit' union
select datediff(mm,@beforeDate,@currDate),('Months') union
select datediff(yy,@beforeDate,@currDate),('Years')
#Results:
difference unit
7 Years
80 Months
2451 Days
Notes:
datediff(d,@beforeDate,@currDate): to get difference of day
datediff(mm,@beforeDate,@currDate): to get difference of month
datediff(yy,@beforeDate,@currDate): to get difference of year
declare @beforeDate datetime
set @currDate='2012-08-24 09:29:21.377'
set @beforeDate='2005-12-08 03:25:21.377'
select datediff(d,@beforeDate,@currDate)'days',
datediff(mm,@beforeDate,@currDate)'months', datediff(yy,@beforeDate,@currDate)'years'
#Results:
days months years
2451 80 7
or can suggest like it
select (datediff(d,@beforeDate,@currDate))'difference',('Days')'unit' union
select datediff(mm,@beforeDate,@currDate),('Months') union
select datediff(yy,@beforeDate,@currDate),('Years')
#Results:
difference unit
7 Years
80 Months
2451 Days
Notes:
datediff(d,@beforeDate,@currDate): to get difference of day
datediff(mm,@beforeDate,@currDate): to get difference of month
datediff(yy,@beforeDate,@currDate): to get difference of year
declare in sql
declare @currDate1 datetime
declare @currDate2 datetime
declare @Schedule1 nvarchar(20)
declare @Schedule2 nvarchar(20)
set @currDate1='2012-08-24 06:28:41.420'
set @currDate2='2012-08-24 14:28:41.420'
set @Schedule1='I go to school'
set @Schedule2='I go to zoo'
select (@currDate1)currDate1,(@Schedule1)Schedule1,(@currDate2)currDate2,(@Schedule2)Schedule2
#Results:
currDate1 Schedule1 currDate2 Schedule2
2012-08-24 06:28:41.420 I go to school 2012-08-24 14:28:41.420 I go to zoo
Query can be modified as below
select (@currDate1)schedule_date,(@Schedule1)schedule_activity union
select (@currDate2)schedule_date,(@Schedule2)schedule_activity
#Results:
schedule_date schedule_activity
2012-08-24 06:28:41.420 I go to school
2012-08-24 14:28:41.420 I go to zoo
declare @currDate2 datetime
declare @Schedule1 nvarchar(20)
declare @Schedule2 nvarchar(20)
set @currDate1='2012-08-24 06:28:41.420'
set @currDate2='2012-08-24 14:28:41.420'
set @Schedule1='I go to school'
set @Schedule2='I go to zoo'
select (@currDate1)currDate1,(@Schedule1)Schedule1,(@currDate2)currDate2,(@Schedule2)Schedule2
#Results:
currDate1 Schedule1 currDate2 Schedule2
2012-08-24 06:28:41.420 I go to school 2012-08-24 14:28:41.420 I go to zoo
Query can be modified as below
select (@currDate1)schedule_date,(@Schedule1)schedule_activity union
select (@currDate2)schedule_date,(@Schedule2)schedule_activity
#Results:
schedule_date schedule_activity
2012-08-24 06:28:41.420 I go to school
2012-08-24 14:28:41.420 I go to zoo
sql autonumber
Use table_accessories which created lesson before
select * from table_accessories order by product_name asc
#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer
select Row_Number() over (order by product_name asc)autoNum,* from table_accessories
#Results:
autoNum product_name
1 Keyboard
2 Keypad
3 Monitor
4 Mouse
5 Printer
Notes:
order by product_name asc: ascending ordering
order by product_name desc: descending ordering
Row_Number() over (order by product_name asc):
autonumber base on product_name ascending ordering
select * from table_accessories order by product_name asc
#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer
select Row_Number() over (order by product_name asc)autoNum,* from table_accessories
#Results:
autoNum product_name
1 Keyboard
2 Keypad
3 Monitor
4 Mouse
5 Printer
Notes:
order by product_name asc: ascending ordering
order by product_name desc: descending ordering
Row_Number() over (order by product_name asc):
autonumber base on product_name ascending ordering
create table sql auto
select 'Keyboard'product_name into table_accessories union
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name
select * from table_accessories
drop table table_accessories
Please copy code above and paste on query analyzer to see results
Notes:
into table_accessories: auto create table and insert with all query results to table name table_accessories
select * from table_accessories: select all field from table_accessories
drop table table_accessories: remove table_accessories from database
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name
select * from table_accessories
drop table table_accessories
Please copy code above and paste on query analyzer to see results
Notes:
into table_accessories: auto create table and insert with all query results to table name table_accessories
select * from table_accessories: select all field from table_accessories
drop table table_accessories: remove table_accessories from database
sql union
select 'Keyboard'product_name union
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name
#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name
#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer
Langganan:
Postingan (Atom)