tag:blogger.com,1999:blog-50611773456186725452024-03-08T03:24:29.992-08:00Sql TutorialSql Server How To (Easily Understood With Real Examples)adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.comBlogger34125tag:blogger.com,1999:blog-5061177345618672545.post-65060269036823718022012-09-18T22:56:00.000-07:002012-09-18T23:10:14.126-07:00sql case whenSQL Server Case Statement:<br />
<code><span style="color: blue;">CASE</span> [expression]
<span style="color: blue;"> </span></code><br />
<code><span style="color: blue;"> WHEN</span> [value | Boolean expression] <span style="color: blue;">THEN</span> [return value] </code><br />
<code> <span style="color: blue;">ELSE</span> [return value]</code><br />
<code><span style="color: blue;">END</span></code><br />
<br />
Example 1:<br />
<span style="color: blue;">declare</span> @Gender <span style="color: blue;">nvarchar</span>(6)<br />
<span style="color: blue;">set</span> @Gender=<span style="color: red;">'M'</span>
<span style="color: blue;">select</span> <span style="color: blue;">case</span> <span style="color: blue;">when</span> @Gender=<span style="color: red;">'M'</span> <span style="color: blue;">then</span> <span style="color: red;">'Male'</span> <span style="color: blue;">else</span> <span style="color: red;">'Female'</span> <span style="color: blue;">End</span> <span style="color: red;">'Gender'</span><br />
<br />
#Results:
<b> </b><br />
<b>Gender</b><br />
Male<br />
<br />
Example 2:
<span style="color: blue;"> </span><br />
<span style="color: blue;">declare</span> @Gender <span style="color: blue;">nvarchar</span>(6)<br />
<span style="color: blue;">set</span> @Gender=<span style="color: red;">'F'</span>
<span style="color: blue;">select</span> <span style="color: blue;">case</span> <span style="color: blue;">when</span> @Gender=<span style="color: red;">'M'</span> <span style="color: blue;">then</span> <span style="color: red;">'Male'</span> <span style="color: blue;">else</span> <span style="color: red;">'Female'</span> <span style="color: blue;">End</span> <span style="color: red;">'Gender'</span><br />
<br />
#Results:<br />
<b>Gender</b><br />
Female<br />
<br />
Example 3:<br />
<span style="color: blue;">select</span> Num, <span style="color: blue;">case</span> Num <span style="color: blue;"> </span><br />
<span style="color: blue;"> when</span> 100 <span style="color: blue;">then</span> <span style="color: red;">'A'</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;"> when</span> 90 <span style="color: blue;">then</span> <span style="color: red;">'B'</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;"> when</span> 80 <span style="color: blue;">then</span> <span style="color: red;">'C'</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;"> when</span> 70 <span style="color: blue;">then</span> <span style="color: red;">'D'</span><br />
<span style="color: blue;">when</span> 60 <span style="color: blue;">then</span> <span style="color: red;">'E'</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;"> end</span> <span style="color: red;">'Grade'</span> <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (100)Num <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (90) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (80) <span style="color: blue;">union</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;"> select</span> (70) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (60)<br />
)v1<br />
<br />
#Results:<br />
<b>Num Grade</b><br />
60 E<br />
70 D<br />
80 C<br />
90 B<br />
100 A adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-47446476056696510702012-09-14T03:13:00.001-07:002012-09-14T03:16:13.972-07:00insert into select fromAnother method to insert data record into a table in addition on <a href="http://sqltosql.blogspot.com/2012/09/sql-insert-into.html">previous post</a>,<br />
<br />
We still use table ProductList.<br />
<br />
<br />
<span style="color: blue;">select</span> * <span style="color: blue;">from</span> ProductList<br />
<br />
If results:<br />
<span style="color: red;">Msg 208, Level 16, State 1, Line 1<br />Invalid object name 'ProductList'. </span><br />
<br />
Create table ProductList first, use query below.<br />
<br />
<span style="color: blue;">CREATE TABLE</span> ProductList<br />
(<br />
ProductId <span style="color: blue;">nvarchar</span>(6),<br />
ProductName <span style="color: blue;">nvarchar</span>(50),<br />
UnitPrice <span style="color: blue;">float</span>,<br />
Quantity <span style="color: blue;">int</span><br />
)<br />
<br />
<span style="color: blue;"><b>insert into</b></span> ProductList <b><span style="color: blue;">select</span></b> <span style="color: red;">'CPU001'</span>,<span style="color: red;">'CPU'</span>,<span style="color: red;">'300'</span>,15 <br />
<br />
<span style="color: blue;">select</span> * <span style="color: blue;">from</span> ProductList<br />
<br />
#Results: <br />
<b>ProductId ProductName UnitPrice Quantity</b><br />
CPU001 CPU 300 15adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-24200931132573675912012-09-14T02:34:00.001-07:002012-09-14T02:54:07.434-07:00sql insert intoOn SQL Server, to add data record into a table use <b>INSERT INTO Statement</b>.<br />
<br />
<span style="color: blue;">INSERT INTO</span> (FieldName1,FieldName2,FieldName3, ...)TableName <span style="color: blue;">VALUES</span> (value1, value2, value3, ...)<br />
<br />
Example 1:<br />
We will create a table first, its name ProductList.<br />
<br />
<span style="color: #6aa84f;">--Create Table ProductList </span><br />
<span style="color: blue;">CREATE TABLE</span> ProductList<br />
(<br />
ProductId <span style="color: blue;">nvarchar</span>(6),<br />
ProductName <span style="color: blue;">nvarchar</span>(50),<br />
UnitPrice <span style="color: blue;">float</span>,<br />
Quantity <span style="color: blue;">int</span><br />
)<br />
<br />
After ProductList created, <a href="http://sqltosql.blogspot.com/2012/08/sql-comment.html">remark</a> above query, cause table name is unique on a database.<br />
<br />
<span style="color: blue;">select</span> * <span style="color: blue;">from</span> ProductList <br />
<br />
#Results:<br />
<b>ProductId ProductName UnitPrice Quantity</b> <br />
<br />
Empty, cause we not yet inserted anyhing into ProductList, now insert with statement below<br />
<br />
<span style="color: blue;"><b>insert into</b></span> ProductList (ProductId,ProductName,UnitPrice,Quantity) <span style="color: blue;"><b>values</b></span> (<span style="color: red;">'LCD001'</span>,<span style="color: red;">'LCD Monitor'</span>,<span style="color: red;">'150'</span>,5) <br />
<br />
<span style="color: blue;">select</span> * <span style="color: blue;">from</span> ProductList<br />
<br />
#Results:<br />
<b>ProductId ProductName UnitPrice Quantity</b><br />
LCD001 LCD Monitor 150 5<br />
<br />
Example 2:<br />
We will insert 1 record againt into ProductList, <span class="short_text" id="result_box" lang="en"><span class="hps">in</span> <span class="hps">some fields</span> <span class="hps">only.</span></span><br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><span style="color: blue;"><b>insert into</b></span> ProductList (ProductId,ProductName) <b><span style="color: blue;">values</span></b> (<span style="color: red;">'LCD002'</span>,<span style="color: red;">'LCD Monitor'</span>) </span></span><br />
<br />
<span style="color: blue;">select</span> * <span style="color: blue;">from</span> ProductList<br />
#Results:<br />
<b>ProductId ProductName UnitPrice Quantity</b><br />
LCD001 LCD Monitor 150 5<br />
LCD002 LCD Monitor NULL NULLadminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-63360105873096891762012-09-13T23:26:00.001-07:002012-09-14T01:52:15.782-07:00sql create tableOn SQL Server, to create table use statement as follows:<br />
<br />
<span style="color: blue;">CREATE TABLE</span> TableName<br />
(<br />
FieldName1 <span style="color: blue;">DataType</span>,<br />
FieldName2 <span style="color: blue;">DataType</span>,<br />
FieldName3 <span style="color: blue;">DataType</span>,<br />
...<br />
)<br />
<br />
Example 1:<br />
Create table with name EmployeeList.<br />
<br />
<span style="color: blue;">CREATE TABLE</span> EmployeeList<br />
(<br />
EmployeeId <span style="color: blue;">int</span>,<br />
EmployeeName <span style="color: blue;">nvarchar</span>(50),<br />
EmployeeAddr <span style="color: blue;">nvarchar</span>(50),<br />
EmployeeDept <span style="color: blue;">nvarchar</span>(30)<br />
)<br />
<br />
After run on query analyzer, here are <br />
#Results:<br />
Command(s) completed successfully<br />
<br />
Hereafter, copy and paste on query analyzer script below:<br />
<br />
<span style="color: blue;">Select</span> * <span style="color: blue;">from</span> EmployeeList<br />
<br />
#Results:<br />
<b>EmployeeId EmployeeName EmployeeAddr EmployeeDept</b><br />
<br />
This Table is empty.<br />
<br />
We can be fill EmployeeList table with data, use <b>INSERT INTO SQL SERVER Statement</b>. <br />
<br />
<span style="color: blue;">insert into</span> EmployeeList (EmployeeId,EmployeeName,EmployeeAddr,EmployeeDept) <span style="color: blue;">values</span> (<span style="color: red;">'11111'</span>,<span style="color: red;">'John Nuxer'</span>,<span style="color: red;">'Jl Raya Bekasi Km 28'</span>,<span style="color: red;">'MIS Dept'</span>)<br />
<br />
Yes, now 1 data inserted to EmployeeList table.<br />
See with run this query<br />
<br />
<span style="color: blue;">Select</span> * <span style="color: blue;">from</span> EmployeeList<br />
<br />
#Results:<br />
<b>EmployeeId EmployeeName EmployeeAddr EmployeeDept</b><br />
11111 John Nuxer Jl Raya Bekasi Km 28 MIS Dept<br />
<br />
How to create table with another way? or Is there any way auto create table on SQL Server?<br />
Yes, <a href="http://sqltosql.blogspot.com/2012/08/create-table-sql-auto.html">here</a>.adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-26792233508345305062012-09-05T23:05:00.000-07:002012-09-05T23:08:29.834-07:00sql round<b>SQL Server ROUND() Function</b> used to get round a numeric field to number of decimals specified.<br />
<br />
Statement:<br />
<span style="color: blue;">SELECT</span> <b><span style="color: magenta;">ROUND</span></b>(ColumnName,decimals) <span style="color: blue;">FROM</span> TableName<br />
where decimals is a number of decimals to be setup.<br />
<br />
Example 1:<br />
<span style="color: blue;">select</span> <span style="color: magenta;">ROUND</span>(ItemPrice,0)ItemPrice <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (15.424)ItemPrice <span style="color: blue;">union</span> <br />
<span style="color: blue;">select</span> (20.685) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (37.273) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (43.556) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (59.317)<br />
)v0<br />
<br />
#Results:<br />
<b>ItemPrice</b><br />
15.000<br />
21.000<br />
37.000<br />
44.000<br />
59.000<br />
<br />
Example 2:<br />
like Example 1 with decimal change to 1<br />
<br />
<span style="color: blue;">select</span> <span style="color: magenta;">ROUND</span>(ItemPrice,1)ItemPrice <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (15.424)ItemPrice <span style="color: blue;">union</span> <br />
<span style="color: blue;">select</span> (20.685) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (37.273) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (43.556) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (59.317)<br />
)v0<br />
<br />
#Results:<br />
<b>ItemPrice</b><br />
15.400<br />
20.700<br />
37.300<br />
43.600<br />
59.300 <br />
<br />
Example 3:<br />
like Example 2 with decimal change to 2<br />
<br />
<span style="color: blue;">select</span> <span style="color: magenta;">ROUND</span>(ItemPrice,2)ItemPrice <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (15.424)ItemPrice <span style="color: blue;">union</span> <br />
<span style="color: blue;">select</span> (20.685) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (37.273) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (43.556) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (59.317)<br />
)v0 <br />
<br />
#Results:<br />
<b>ItemPrice</b><br />
15.420<br />
20.690<br />
37.270<br />
43.560<br />
59.320adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-17375937612344423892012-09-05T22:34:00.000-07:002012-09-05T23:09:00.145-07:00sql between<b>BETWEEN SQL Server operator</b> use to get a range of data between two values.<br />
<br />
<span style="color: blue;">SELECT</span> ColumnName(s) <span style="color: blue;">FROM</span> TableName <span style="color: blue;">WHERE</span> ColumnName <b><span style="color: #999999;">BETWEEN</span></b> value1 <b><span style="color: #999999;">AND </span></b>value2.<br />
<br />
Example 1:<br />
<span style="color: blue;">select</span> InvoiceNo <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/001'</span>)InvoiceNo,(<span style="color: red;">'2012/08/20 09:43:29.993'</span>)InvoiceDate <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/002'</span>),(<span style="color: red;">'2012/08/21 09:44:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/003'</span>),(<span style="color: red;">'2012/08/22 09:45:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/004'</span>),(<span style="color: red;">'2012/08/23 13:46:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/005'</span>),(<span style="color: red;">'2012/08/24 09:47:29.993'</span>)<br />
)v0 <span style="color: blue;">where</span> InvoiceDate <b><span style="color: #999999;">BETWEEN</span></b> (<span style="color: red;">'2012/08/21'</span>) <b><span style="color: #999999;">AND</span></b> (<span style="color: red;">'2012/08/23'</span>)<br />
<br />
#Results:<br />
<b>InvoiceNo</b><br />
YAN/08/12/002<br />
YAN/08/12/003<br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps">In fact</span> <span class="hps">there are 3</span> <span class="hps">invoice</span> <span class="hps">for that period, but on above query result only 2. Modif above query like below (Example 2) to get </span></span><span class="short_text" id="result_box" lang="en"><span class="hps">actual results.</span></span><br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps"> Example 2:</span></span><br />
<span style="color: blue;">select</span> InvoiceNo <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/001'</span>)InvoiceNo,(<span style="color: red;">'2012/08/20 09:43:29.993'</span>)InvoiceDate <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/002'</span>),(<span style="color: red;">'2012/08/21 09:44:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/003'</span>),(<span style="color: red;">'2012/08/22 09:45:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/004'</span>),(<span style="color: red;">'2012/08/23 13:46:29.993'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'YAN/08/12/005'</span>),(<span style="color: red;">'2012/08/24 09:47:29.993'</span>)<br />
)v0 <span style="color: blue;">where</span> <b><span style="color: magenta;">CONVERT</span></b>(<b><span style="color: blue;">DATE</span></b>,InvoiceDate <b><span style="color: #999999;">BETWEEN</span></b> (<span style="color: red;">'2012/08/21'</span>) <b><span style="color: #999999;">AND</span></b> (<span style="color: red;">'2012/08/23'</span>)<br />
<br />
#Results:<br />
<b>InvoiceNo</b><br />
YAN/08/12/002<br />
YAN/08/12/003<br />
YAN/08/12/004adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-31747938230536394972012-09-05T17:59:00.000-07:002012-09-05T23:09:21.896-07:00sql likeOperator <b>LIKE on SQL Server</b> used in a WHERE clause to search for a specified pattern in a column.<br />
<br />
<span style="color: blue;">SELECT</span> ColumnName(s) <span style="color: blue;">FROM</span> TableName <span style="color: blue;">WHERE</span> ColumnName <b style="color: #999999;">LIKE</b> pattern<br />
<br />
Example 1:<br />
<span style="color: blue;">select</span> StudentName <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Wardana Putra'</span>)StudentName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Raihana Pratiwi'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Juliana Eka Putri'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Joko Susanto'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Sugeng Joko Utomo'</span>)<br />
)v0 <span style="color: blue;">where</span> StudentName <b>like <span style="color: red;">'J%'</span></b><br />
<br />
#Results:<br />
<b>StudentName</b><br />
Juliana Eka Putri<br />
Joko Susanto<br />
<br />
Example 2:<br />
Still use Example 1 with small modification.<br />
<span style="color: blue;">select</span> StudentName <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Wardana Putra'</span>)StudentName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Raihana Pratiwi'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Juliana Eka Putri'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Joko Susanto'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Sugeng Joko Utomo'</span>)<br />
)v0 <span style="color: blue;">where</span> StudentName <b>like <span style="color: red;">'i%'</span></b><br />
<br />
#Results:<br />
<b>StudentName</b><br />
Juliana Eka Putri<br />
Raihana Pratiwi <br />
<br />
Example 3:<br />
Still use Example 1 with small modification.<br />
<span style="color: blue;">select</span> StudentName <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Wardana Putra'</span>)StudentName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Raihana Pratiwi'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Juliana Eka Putri'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Joko Susanto'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Sugeng Joko Utomo'</span>)<br />
)v0 <span style="color: blue;">where</span> StudentName <b>like <span style="color: red;">'%ana%'</span></b><br />
<br />
#Results:<br />
<b>StudentName</b><br />
Juliana Eka Putri<br />
Raihana Pratiwi<br />
Wardana Putraadminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-6545373921421435002012-09-03T23:54:00.001-07:002012-09-03T23:55:26.579-07:00sql sort order<b>SQL Server Order by</b> Statement<br />
<span style="color: blue;">SELECT</span> ColumnName(s) <span style="color: blue;">FROM</span> TableName <span style="color: blue;">Order by</span> ColumnName <span style="color: blue;">Asc</span>|<span style="color: blue;">Desc</span><br />
<br />
Example 1:<br />
<span style="color: #6aa84f;">--ascending order</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Rudi'</span>)FootBallTeam <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Anton'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Budi'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Cahya'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Yahya'</span>) <span style="color: blue;">Order by</span> FootBallTeam <span style="color: blue;">Asc</span><br />
<br />
#Results:<br />
<b>FootBallTeam</b><br />
Anton<br />
Budi<br />
Cahya<br />
Rudi<br />
Yahya<br />
<br />
Example 2:<br />
<div style="color: #6aa84f;">
--descending order</div>
<span style="color: blue;">select</span> (<span style="color: red;">'Rudi'</span>)FootBallTeam <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Anton'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Budi'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Cahya'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Yahya'</span>) <span style="color: blue;">Order by</span> FootBallTeam <span style="color: blue;">Desc</span><br />
<br />
#Results:<br />
<b>FootBallTeam</b><br />
Yahya<br />
Rudi<br />
Cahya<br />
Budi<br />
Antonadminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-9880762645657164972012-09-03T23:36:00.001-07:002012-09-03T23:40:11.792-07:00sql server group by<b>SQL Server Group by</b> Statement<br />
<span style="color: blue;">SELECT</span> ColumnName, aggregate_function(ColumnName) <span style="color: blue;">FROM</span> TableName<br />
<span style="color: blue;">WHERE</span> ColumnName operator value <span style="color: blue;">GROUP BY</span> ColumnName<br />
<span style="color: blue;">HAVING</span> aggregate_function(ColumnName) operator value<br />
<br />
<span class="hps">Group by Statemet often used in conjunction</span> <span class="hps">with a</span> <a href="http://sqltosql.blogspot.com/2012/09/having-in-sql.html"><span class="hps"></span><span class="hps">having</span></a><a href="http://sqltosql.blogspot.com/2012/09/having-in-sql.html"> clause</a> <span class="hps">statement.</span><br />
<br />
<span class="hps">Example 1:</span><br />
<span class="hps"><span style="color: blue;">select</span> Catagory <span style="color: blue;">from</span> (<br /> <span style="color: blue;">select</span> (<span style="color: red;">'Stork'</span>)AnimalName,(<span style="color: red;">'Bird'</span>)Catagory <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'Pigeon'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'Hawk'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'Shark'</span>),(<span style="color: red;">'Fish'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'Dolphin'</span>),(<span style="color: red;">'Fish'</span>) <br />)v0</span><br />
<br />
<span class="hps">#Results:</span><br />
<span class="hps"><b>Catagory</b><br />Fish<br />Bird<br />Bird<br />Fish<br />Bird</span><br />
<br />
<span class="hps">Example 2:</span><br />
<span class="hps">Still use query on Example 1, </span><span class="hps">with added <span style="color: blue;">group by</span> statement.</span><br />
<span class="hps"> </span><span class="hps"><span style="color: blue;">select</span> Catagory <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Stork'</span>)AnimalName,(<span style="color: red;">'Bird'</span>)Catagory <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Pigeon'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Hawk'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Shark'</span>),(<span style="color: red;">'Fish'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Dolphin'</span>),(<span style="color: red;">'Fish'</span>) <br />
)v0 <span style="color: blue;">group by</span> Catagory</span><br />
<span class="hps"><br /></span>
<span class="hps">#Results:</span><br />
<span class="hps"><b>Catagory</b><br />Bird<br />Fish</span><br />
<span class="hps"><br /></span>
<span class="hps">Example 3:</span><br />
<span class="hps">Still use query on Example 2, </span><span class="hps">with added SQL Server <span style="color: magenta;">COUNT()</span> Function to get Count of Similiar Catagory.</span><br />
<span class="hps"><span style="color: blue;">select</span> Catagory, <span style="color: magenta;">COUNT</span>(Catagory)NumOfCatagory <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Stork'</span>)AnimalName,(<span style="color: red;">'Bird'</span>)Catagory <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Pigeon'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Hawk'</span>),(<span style="color: red;">'Bird'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Shark'</span>),(<span style="color: red;">'Fish'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Dolphin'</span>),(<span style="color: red;">'Fish'</span>) <br />
)v0 <span style="color: blue;">group by</span> Catagory</span><span class="hps"> </span><br />
<span class="hps"><br /></span>
<span class="hps">#Results:</span><br />
<span class="hps"><b>Catagory NumOfCatagory</b><br />Bird 3<br />Fish 2</span>adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-26826086519919522572012-09-03T23:08:00.002-07:002012-09-03T23:16:00.849-07:00having in sql<b>SQL Server Having Clause</b> Statement:<br />
<span style="color: blue;">SELECT</span> ColumnName, aggregate_function(ColumnName) <span style="color: blue;">FROM</span> TableName<br />
<span style="color: blue;">WHERE</span> ColumnName operator value <span style="color: blue;">GROUP BY</span> ColumnName<br />
<span style="color: blue;">HAVING</span> aggregate_function(ColumnName) operator valu<br />
<br />
Example 1:<br />
<span style="color: blue;"> select</span> CustName,ProductName,<span style="color: magenta;">SUM</span>(Qty)Quantity <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Buana Jaya'</span>)CustName,(<span style="color: red;">'Tubex'</span>)ProductName,(10)Qty <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Tubex'</span>),(15) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Dengue'</span>),(5) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Buana Jaya'</span>),(<span style="color: red;">'Tubex'</span>),(5) <span style="color: blue;">union</span><br />
<span style="color: blue;">select </span>(<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Tubex'</span>),(25)<br />
)v0 <b><span style="color: blue;">group by</span> CustName,ProductName <span style="color: blue;">having</span> CustName=<span style="color: red;">'Citra Makmur'</span></b><br />
<br />
#Results:<br />
<b>CustomerName ProductName Quantity</b><br />
Citra Makmur Dengue 5<br />
Citra Makmur Tubex 40 <br />
<br />
On Example 1, query can modified like show on Example 2 to make same result.<br />
<br />
Example 2:<br />
<span style="color: blue;">select</span> CustName,ProductName,<span style="color: magenta;">SUM</span>(Qty)Quantity <span style="color: blue;">from</span> (<br />
<span style="color: blue;"> select</span> (<span style="color: red;">'Buana Jaya'</span>)CustName,(<span style="color: red;">'Tubex'</span>)ProductName,(10)Qty <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Tubex'</span>),(15) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Dengue'</span>),(5) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Buana Jaya'</span>),(<span style="color: red;">'Tubex'</span>),(5) <span style="color: blue;">union</span><br />
<span style="color: blue;">select </span>(<span style="color: red;">'Citra Makmur'</span>),(<span style="color: red;">'Tubex'</span>),(25) <br />
)v0 <b><span style="color: blue;">where</span> CustName=<span style="color: red;">'Citra Makmur'</span> <span style="color: blue;">group by</span> CustName,ProductName</b><br />
<br />
#Results:<br />
<b>CustomerName ProductName Quantity</b><br />
Citra Makmur Dengue 5<br />
Citra Makmur Tubex 40<br />
<br />
Notes:<br />
Example 1 use combine group by having clause sql server statement while<br />
Example 2 use combine where group by sql server statementadminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-84533699496652205542012-08-31T00:43:00.003-07:002012-08-31T00:47:01.109-07:00select top sql<b>SQL Server Select Top</b> Statement:<br />
<span style="color: blue;">SELECT TOP</span> number | number <span style="color: blue;">PERCENT</span> ColumnName(s)
<span style="color: blue;">FROM</span> TableName<br />
<br />
Example 1:<br />
<br />
<span style="color: blue;">select <b>TOP</b></span><b> 1</b> * <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Umar'</span>)PersonName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Zubair'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Ali'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Muhammad'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Bilal'</span>)<br />
)v0 <b style="color: blue;">Order by</b> PersonName <span style="color: blue;">Asc</span><br />
<br />
#Results:<br />
<b>PersonName</b><br />
Ali<br />
<br />
Example 2:<br />
<span style="color: blue;">select TOP</span> 1 * <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Umar'</span>)PersonName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Zubair'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Ali'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Muhammad'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Bilal'</span>)<br />
)v1 <b style="color: blue;">Order by</b> PersonName <span style="color: blue;">Desc</span><br />
<br />
#Results:<br />
<b>PersonName</b><br />
Zubair<br />
<br />
Example 3:<br />
<span style="color: blue;">select TOP</span> 3 * <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Umar'</span>)PersonName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Zubair'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Ali'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Muhammad'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Bilal'</span>)<br />
)v2 <b style="color: blue;">Order by</b> PersonName <span style="color: blue;">Asc</span><br />
<br />
<br />
#Results:<br />
<b>PersonName</b><br />
Ali<br />
Bilal<br />
Muhammad<br />
<br />
Example 4:<br />
<span style="color: blue;">select TOP</span> 3 * <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Umar'</span>)PersonName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Zubair'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Ali'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Muhammad'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Bilal'</span>)<br />
)v2 <b style="color: blue;">Order by</b> PersonName <span style="color: blue;">Desc</span><br />
<br />
<br />
<b>PersonName </b><br />
Zubair<br />
Umar<br />
Muhammad<br />
<br />
Example 5:<br />
<span style="color: blue;">select <b>TOP</b></span><b> 30 <span style="color: blue;">PERCENT</span></b> * <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'Umar'</span>)PersonName <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Zubair'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Ali'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Muhammad'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'Bilal'</span>)<br />
)v2 <b style="color: blue;">Order by</b> PersonName <span style="color: blue;">Desc</span> <br />
<br />
#Results:<br />
<b>PersonName </b><br />
Zubair<br />
Umar
adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-15159817717503215252012-08-30T23:42:00.003-07:002012-08-30T23:46:03.311-07:00sql select count<b>SQL Server <span style="color: magenta;">COUNT()</span> function</b> use to get number of rows that matches a specified criteria.<br />
<br />
Example 1:<br />
<span style="color: blue;">select</span> <span style="color: magenta;">COUNT</span>(ItemName)NumOfItem <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>)ItemName,(<span style="color: red;">'P0001'</span>)ItemCode,(10)Qty <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(20) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(30) <span style="color: blue;">union</span><br />
<span style="color: blue;">select </span>(<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(15) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(25) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(35)<br />
)v0<br />
<br />
#Results:<br />
<b>NumOfItem</b><br />
6<br />
<br />
Example 2: <br />
<span style="color: blue;">select</span> <span style="color: magenta;">COUNT</span>(<b><span style="color: blue;">DISTINCT</span></b> ItemName)NumOfItem <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>)ItemName,(<span style="color: red;">'P0001'</span>)ItemCode,(10)Qty <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(20) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(30) <span style="color: blue;">union</span><br />
<span style="color: blue;">select </span>(<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(15) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(25) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(35)<br />
)v1 <br />
<br />
#Results:<br />
<b>NumOfItem</b><br />
2<br />
<br />
Example 3: <br />
See following examples and compare with <span class="short_text" id="result_box" lang="en"><span class="hps">previous query:</span></span><br />
<br />
<br />
<span style="color: blue;">select</span> <span style="color: magenta;">COUNT</span>(<b><span style="color: blue;">DISTINCT</span></b> ItemName)NumOfItem <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>)ItemName,(<span style="color: red;">'P0001'</span>)ItemCode,(10)Qty <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(20) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pencil'</span>),(<span style="color: red;">'P0001'</span>),(30) <span style="color: blue;">union</span><br />
<span style="color: blue;">select </span>(<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(15) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(25) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'pen'</span>),(<span style="color: red;">'P0002'</span>),(35) <span style="color: blue;">union</span><br />
<br />
<span style="color: blue;">select</span> <b>(null)</b>,(<span style="color: red;">'P0003'</span>),1<br />
)v2<br />
<br />
#Results:<br />
<b>NumOfItem</b><br />
2<br />
<br />
<br />
Notes:<br />
SQL Server <b><span style="color: blue;">DISTINCT</span></b> sintax added to get number of unique item name<br />
Results Example 2 and 3 same couse <b>NULL values</b> will not be counted<br />
<br />adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-37106238833303534452012-08-30T23:17:00.001-07:002012-08-30T23:20:15.645-07:00convert month name to number sqlUse Combine <span style="color: magenta;">DATEPART()</span> and <span style="color: magenta;">CAST()</span> SQL Server Function.<br />
<br />
For example:<br />
<span style="color: blue;">SELECT</span> <span style="color: magenta;">DATEPART</span>(mm,<span style="color: magenta;">CAST</span>(<span style="color: red;">'February'</span>+<span style="color: red;"> ' 1900'</span> <span style="color: blue;">AS DATETIME</span>))NumOfThisMonth<br />
<br />
#Results:<br />
<b>NumOfThisMonth</b><br />
2<br />
<br />
<span style="color: blue;">select</span> OrdNum,<span style="color: magenta;">DATEPART</span>(mm,<span style="color: magenta;">CAST</span>(MonthNames+ <span style="color: red;">' 1900'</span> <span style="color: blue;">AS DATETIME</span>))NumOfMonth <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (1)OrdNum,<span style="color: red;">'January'</span>MonthNames <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (2),<span style="color: red;">'February'</span> <span style="color: blue;">union </span><br />
<span style="color: blue;">select</span> (3),<span style="color: red;">'March'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (4),<span style="color: red;">'April'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (5),<span style="color: red;">'May'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (6),<span style="color: red;">'June'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (7),<span style="color: red;">'July'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (8),<span style="color: red;">'August'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (9),<span style="color: red;">'September'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (10),<span style="color: red;">'October'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (11),<span style="color: red;">'November'</span> <span style="color: blue;">union </span><br />
<span style="color: blue;">select</span> (12),<span style="color: red;">'December'</span><br />
)v0<br />
<br />
Compare with this another way:<br />
<span style="color: blue;">SELECT</span> <span style="color: magenta;">CHARINDEX</span>(<span style="color: red;">'OCT'</span>,<span style="color: red;">'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC'</span>)/3 <span style="color: blue;">AS</span> monthNumber <br />
<br />
#Results:<br />
<b>monthNumber</b><br />
10<br />
<br />
<span style="color: blue;">select</span> OrdNum,<span style="color: magenta;">DATEPART</span>(mm,<span style="color: magenta;">CAST</span>(MonthNames+ <span style="color: red;">' 1900'</span> <span style="color: blue;">AS DATETIME</span>))NumOfMonth,<br />
<span style="color: magenta;">CHARINDEX</span>(MonthNames,<span style="color: red;">'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC'</span>)/3 <span style="color: blue;">AS</span> monthNumber<br />
from (<br />
<span style="color: blue;">select</span> (1)OrdNum,<span style="color: red;">'JAN'</span>MonthNames <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (2),<span style="color: red;">'FEB'</span> <span style="color: blue;">union</span> <br />
<span style="color: blue;">select</span> (3),<span style="color: red;">'MAR'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (4),<span style="color: red;">'APR'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (5),<span style="color: red;">'MAY'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (6),<span style="color: red;">'JUN'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (7),<span style="color: red;">'JUL'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (8),<span style="color: red;">'AUG'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (9),<span style="color: red;">'SEP'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (10),<span style="color: red;">'OCT'</span> <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (11),<span style="color: red;">'NOV'</span> <span style="color: blue;">union</span> <br />
<span style="color: blue;">select</span> (12),<span style="color: red;">'DEC'</span><br />
)v1 <br />
<br />
<b>OrdNum NumOfMonth monthNumber</b><br />
1 1 1<br />
2 2 2<br />
3 3 3<br />
4 4 4<br />
5 5 5<br />
6 6 6<br />
7 7 7<br />
8 8 8<br />
9 9 9<br />
10 10 10<br />
11 11 11<br />
12 12 12adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-28110228044165113162012-08-29T22:22:00.002-07:002012-08-29T22:24:34.756-07:00average in sql<b><span style="color: magenta;">AVG()</span></b> on <b>SQL Server Function</b> use to get average value of a numeric column.<br />
<br />
<span style="color: blue;">select</span> (1)Num,(<span style="color: red;">'Product One'</span>)ItemName,(2500000)ItemPrice <span style="color: blue;">Union</span><br />
<span style="color: blue;">select </span>(2),(<span style="color: red;">'Product Two'</span>),(3500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (3),(<span style="color: red;">'Product Three'</span>),(4500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (4),(<span style="color: red;">'Product Four'</span>),(6500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (5),(<span style="color: red;">'Product Five'</span>),(7500000) <br />
<br />
#Results:<br />
<b>Num ItemName ItemPrice</b><br />
1 Product One 2500000<br />
2 Product Two 3500000<br />
3 Product Three 4500000<br />
4 Product Four 6500000<br />
5 Product Five 7500000<br />
<br />
<span style="color: blue;">select</span> <b style="color: magenta;">AVG</b>(ItemPrice)AvgItemPrice <span style="color: blue;">from</span> (<br />
<span style="color: blue;"> select</span> (1)Num,(<span style="color: red;">'Product One'</span>)ItemName,(2500000)ItemPrice <span style="color: blue;">Union</span><br />
<span style="color: blue;"> select </span>(2),(<span style="color: red;">'Product Two'</span>),(3500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;"> select</span> (3),(<span style="color: red;">'Product Three'</span>),(4500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;"> select</span> (4),(<span style="color: red;">'Product Four'</span>),(6500000) <span style="color: blue;">Union</span><br />
<span style="color: blue;"> select</span> (5),(<span style="color: red;">'Product Five'</span>),(7500000)<br />
)v0 <br />
<br />
#Results:<br />
<b>AvgItemPrice</b><br />
4900000<br />
<br />adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-9071517265002087202012-08-29T19:33:00.000-07:002012-08-29T19:48:42.530-07:00sql datepart<b>DATEPART() SQL Server function</b> Statement:<br />
<br />
<span style="color: magenta;">DATEPART</span>(datepart,date)<br />
<br />
<span style="color: blue;">declare</span> @ThisDate datetime
set @ThisDate=<span style="color: red;">'2012-08-30 08:45:08.717'
</span>
<span style="color: blue;"> </span><br />
<span style="color: blue;">select </span> (<span style="color: red;">'yy'</span>)<span style="color: magenta;">DATEPART</span>Init,(<span style="color: red;">'year'</span>)UseForShow,<span style="color: magenta;">DATEPART</span>(yy,@ThisDate)ShowData <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'qq'</span>),(<span style="color: red;">'quarter'</span>),<span style="color: magenta;">DATEPART</span>(qq,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'mm'</span>),(<span style="color: red;">'month'</span>),<span style="color: magenta;">DATEPART</span>(mm,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'dy'</span>),(<span style="color: red;">'dayofyear'</span>),<span style="color: magenta;">DATEPART</span>(dy,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'dd'</span>),(<span style="color: red;">'day'</span>),<span style="color: magenta;">DATEPART</span>(dd,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'wk'</span>),(<span style="color: red;">'week'</span>),<span style="color: magenta;">DATEPART</span>(wk,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'dw'</span>),(<span style="color: red;">'weekday'</span>),<span style="color: magenta;">DATEPART</span>(dw,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'hh'</span>),(<span style="color: red;">'hour'</span>),<span style="color: magenta;">DATEPART</span>(hh,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'mi'</span>),(<span style="color: red;">'minute'</span>),<span style="color: magenta;">DATEPART</span>(mi,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'ss'</span>),(<span style="color: red;">'second'</span>),<span style="color: magenta;">DATEPART</span>(ss,@ThisDate) <span style="color: blue;">Union</span> <br />
<span style="color: blue;">select </span> (<span style="color: red;">'ms'</span>),(<span style="color: red;">'second'</span>),<span style="color: magenta;">DATEPART</span>(ms,@ThisDate) <span style="color: #6aa84f;">--union</span><br />
<span style="color: #6aa84f;">--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union</span><br />
<span style="color: #6aa84f;">--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)</span><br />
<br />
#Results:<br />
<b>DatePartInit UseForShow ShowData</b><br />
dd day 30<br />
dw weekday 5<br />
dy dayofyear 243<br />
hh hour 8<br />
mi minute 45<br />
mm month 8<br />
ms second 717<br />
qq quarter 3<br />
ss second 8<br />
wk week 35<br />
yy year 2012adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-17941224827221726522012-08-29T18:16:00.004-07:002012-08-29T18:18:11.327-07:00sql date convert<b>SQL Server <span style="color: magenta;">Convert</span> Date Time Function</b> Statement:<br />
<span style="color: magenta;">Convert</span>(<i>data_type(length)</i>,<i>expression</i>,<i>style</i>)<br />
<br />
<span style="color: blue;">declare</span> @CurrDate <span style="color: blue;">datetime</span><br />
<span style="color: blue;">set</span> @CurrDate=<span style="color: red;">'2012-08-30 07:54:52.293'</span><br />
<br />
<span style="color: blue;">select</span> (100)ShowStyle,<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,100)ShowOutput <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (101),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,101) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (102),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,102) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (103),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,103) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (104),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,104) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (105),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,105) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (106),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,106) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (107),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,107) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (108),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,108) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (109),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,109) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (110),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,110) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (111),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,111) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (112),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,112) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (113),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,113) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (114),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,114) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (120),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,120) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (121),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,121) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (126),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,126) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (130),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,130) <span style="color: blue;">Union</span><br />
<span style="color: blue;">select</span> (131),<span style="color: magenta;">Convert</span>(nvarchar,@CurrDate,131)<br />
<br />
#Results:<br />
<b>ShowStyle ShowOutput</b><br />
100 Aug 30 2012 7:54AM<br />
101 08/30/2012<br />
102 2012.08.30<br />
103 30/08/2012<br />
104 30.08.2012<br />
105 30-08-2012<br />
106 30 Aug 2012<br />
107 Aug 30, 2012<br />
108 07:54:52<br />
109 Aug 30 2012 7:54:52:293AM<br />
110 08-30-2012<br />
111 2012/08/30<br />
112 20120830<br />
113 30 Aug 2012 07:54:52:293<br />
114 07:54:52:293<br />
120 2012-08-30 07:54:52<br />
121 2012-08-30 07:54:52.293<br />
126 2012-08-30T07:54:52.293<br />
130 13 شوال 1433 7:54:52:293AM<br />
131 13/10/1433 7:54:52:293AM adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-17880021642699207032012-08-29T04:25:00.000-07:002012-08-29T04:27:59.731-07:00right in sql<span style="color: #6aa84f;">--right sql server function for static position</span><br />
<span style="color: blue;">select</span> <b>RIGHT</b>(MyAnimals,10)AnimalName,<b>LEFT</b>(MyAnimals, <b><span style="color: magenta;">CHARINDEX</span></b>(' ', MyAnimals)-1)qty <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'5 chickens A'</span>)MyAnimals <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'15 chickens B'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'25 chickens C'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'35 chickens D'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'45 chickens E'</span>) <br />
)v0<br />
<br />
#Results:<br />
<b>AnimalName qty</b><br />
chickens B 15<br />
chickens C 25<br />
chickens D 35<br />
chickens E 45<br />
chickens A 5<br />
<br />
<span style="color: #6aa84f;">--right sql server function for dinamic position</span><br />
<span style="color: blue;">select</span> MyAnimals,quantity,RIGHT(MyAnimals,<b style="color: magenta;">LEN</b>(MyAnimals)-<b style="color: magenta;">LEN</b>(quantity))AnimalName <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'5 chickens'</span>)MyAnimals <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'14 goats'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'7 chickens'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'10 cows'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'500 ducks'</span>)<br />
)v2<br />
)v3<br />
<br />
#Results:<br />
<b>MyAnimals quantity AnimalName</b><br />
10 cows 10 cows<br />
14 goats 14 goats<br />
5 chickens 5 chickens<br />
500 ducks 500 ducks<br />
7 chickens 7 chickensadminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-81081834621545549672012-08-29T03:53:00.002-07:002012-08-29T20:52:21.396-07:00sql substring<b>Substring function</b> on sql server is a function like mid on asp, to get one or more characters <span class="short_text" id="result_box" lang="en"><span class="hps">in the middle</span> <span class="hps">of</span> <span class="hps">a set of characters.</span></span><br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><span style="color: #6aa84f;">--substring sql server function for static position</span><br /><span style="color: blue;">select</span> notes,<span style="color: magenta;">substring</span>(notes,11,3)qty <span style="color: blue;">from</span> (<br /> <span style="color: blue;">select</span> (<span style="color: red;">'bicycle A 285 unit'</span>)notes <span style="color: blue;">union </span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'bicycle B 153 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'bicycle C 120 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'bicycle D 569 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'bicycle E 587 unit'</span>)<br />)v1</span></span><br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps">#Results:</span></span><br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><b>notes qty</b><br />bicycle A 285 unit 285<br />bicycle B 153 unit 153<br />bicycle C 120 unit 120<br />bicycle D 569 unit 569<br />bicycle E 587 unit 587</span></span><br />
<br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><span style="color: #6aa84f;">--substring sql server function for dinamic position</span></span></span><span class="short_text" id="result_box" lang="en"><span class="hps"> </span></span><br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><span style="color: blue;">select</span> notes,<b><span style="color: magenta;">substring</span></b>(notes, <b><span style="color: magenta;">CHARINDEX</span></b>(' ',notes)+1,3)qty <span style="color: blue;">from</span> (<br /> <span style="color: blue;">select</span> <span style="color: red;">('bicycle 285 unit'</span>)notes <span style="color: blue;">union </span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'motorcycle 153 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'cabriolet 120 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'motor 406 unit'</span>) <span style="color: blue;">union</span><br /> <span style="color: blue;">select</span> (<span style="color: red;">'pedicab 587 unit'</span>)<br />)v1 </span></span><br />
<span class="short_text" id="result_box" lang="en"><span class="hps"><br /></span></span>
<span class="short_text" id="result_box" lang="en"><span class="hps"><b>notes qty</b><br />bicycle 285 unit 285<br />cabriolet 120 unit 120<br />motor 406 unit 406<br />motorcycle 153 unit 153<br />pedicab 587 unit 587</span></span>adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-32757691813844855842012-08-28T18:10:00.001-07:002012-08-28T18:14:15.188-07:00left sql function<span style="color: #6aa84f;">-- left sql server function for static position</span><br />
<span style="color: blue;">select</span> CatNo,<b>LEFT(CatNo,8)</b>NewCatNo <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'MA 30012~100'</span>)CatNo <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'MB 30542~20012'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'MB 30031~321'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CA 30035~356487'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CB 30041~310205'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CC 31112~333'</span>)<br />
)v0<br />
<br />
#Results:<br />
<b>CatNo NewCatNo</b><br />
CA 30035~356487 CA 30035<br />
CB 30041~310205 CB 30041<br />
CC 31112~333 CC 31112<br />
MA 30012~100 MA 30012<br />
MB 30031~321 MB 30031<br />
MB 30542~20012 MB 30542<br />
<br />
<span style="color: #6aa84f;">-- sql server left function for dinamic position</span><br />
<span style="color: blue;">select</span> CatNo,<b>LEFT(CatNo,<span style="color: magenta;">CHARINDEX</span>('~',CatNo)-1)</b>NewCatNo <span style="color: blue;">from</span> (<br />
<span style="color: blue;">select</span> (<span style="color: red;">'MA 30012567~100'</span>)CatNo <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'MB 3054231~20012'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'MB 30031~321'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CA 300352345~356487'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CB 30041056879~310205'</span>) <span style="color: blue;">union</span><br />
<span style="color: blue;">select</span> (<span style="color: red;">'CC 311~333'</span>)<br />
)v0<br />
<br />
<b>CatNo NewCatNo</b><br />
CA 300352345~356487 CA 300352345<br />
CB 30041056879~310205 CB 30041056879<br />
CC 311~333 CC 311<br />
MA 30012567~100 MA 30012567<br />
MB 30031~321 MB 30031<br />
MB 3054231~20012 MB 3054231adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-76500117503509268162012-08-28T00:50:00.001-07:002012-08-29T21:08:14.775-07:00break on sql<span style="color: blue;">declare</span> @loop <span style="color: blue;">int</span><br />
<span style="color: blue;">declare</span> @spell <span style="color: blue;">nvarchar</span>(15)<br />
<span style="color: blue;">declare</span> @query <span style="color: blue;">nvarchar</span>(300)<br />
<span style="color: blue;">set</span> @loop=10<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">''</span><br />
<span style="color: blue;">set</span> @query=<span style="color: red;">''</span><br />
<br />
<span style="color: blue;">WHILE</span> not @loop =20<br />
<span style="color: blue;">Begin</span><br />
<span style="color: blue;">if</span> @loop=11<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Eleven'''</span><br />
<span style="color: blue;">else if</span> @loop=12<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Twelve'''</span><br />
<span style="color: blue;">else if</span> @loop=13<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Thirteen'''</span><br />
<span style="color: blue;">else if</span> @loop=14<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Fourteen'''</span><br />
<span style="color: blue;">else if</span> @loop=15<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Fifteen'''</span><br />
<span style="color: blue;">else if</span> @loop=16<br />
set @spell=<span style="color: red;">'''Sixteen'''</span><br />
<span style="color: blue;">else if</span> @loop=17<br />
<b><span style="color: blue;">BEGIN<span style="color: black;"> </span></span></b><code style="font-size: 12px;"><span style="color: green;">-- Starting from this script down will never executed</span></code><b><span style="color: blue;"></span><br /> <span style="color: blue;">set</span> @spell=<span style="color: red;">'''Seventeen'''</span><br /> <span style="color: blue;">BREAK</span><br /> <span style="color: blue;">END </span></b><br />
<span style="color: blue;">else if</span> @loop=18<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Eighteen'''</span><br />
<span style="color: blue;">else if</span> @loop=19<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Nineteen'''</span><br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Twenty'''</span><br />
<br />
<span style="color: blue;">set</span> @loop=@loop+1<br />
<br />
<span style="color: blue;">if </span>@query=<span style="color: red;">''</span><br />
<span style="color: blue;">set </span>@query=<span style="color: red;">'select '</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@loop)+<span style="color: red;">'num,'</span>+@spell+<span style="color: red;">'spell'</span><br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @query=@query+<span style="color: red;">' union '</span>+<span style="color: red;">'select '</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@loop)+<span style="color: red;">','</span>+@spell<br />
<span style="color: blue;">End</span><br />
<span style="color: blue;">exec</span> (@query)<br />
<br />
#Results:<br />
<b>num spell</b><br />
11 Twenty<br />
12 Eleven<br />
13 Twelve<br />
14 Thirteen<br />
15 Fourteen<br />
16 Fifteen<br />
17 Sixteen adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-79507540584841107722012-08-27T23:12:00.002-07:002012-08-29T21:05:12.331-07:00sql if else<span style="color: blue;">declare</span> @loop <span style="color: blue;">int</span><br />
<span style="color: blue;">declare</span> @spell <span style="color: blue;">nvarchar</span>(15)<br />
<span style="color: blue;">declare</span> @qry <span style="color: blue;">nvarchar</span>(300)<br />
<span style="color: blue;">set</span> @loop=0<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">''</span><br />
<span style="color: blue;">set</span> @qry=<span style="color: red;">''</span><br />
<br />
<span style="color: blue;">WHILE</span> not @loop =10<br />
<span style="color: blue;">Begin</span><br />
<span style="color: blue;">set</span> @loop=@loop+1<br />
<span style="color: blue;">if</span> @loop=1<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''One'''</span><br />
<b><span style="color: blue;">else if</span></b> @loop=2<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Two'''</span><br />
<span style="color: blue;">else if</span> @loop=3<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Three'''</span><br />
<span style="color: blue;">else if</span> @loop=4<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Four'''</span><br />
<span style="color: blue;">else if</span> @loop=5<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Five'''</span><br />
<span style="color: blue;">else if</span> @loop=6<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Six'''</span><br />
<span style="color: blue;">else if</span> @loop=7<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Seven'''</span><br />
<span style="color: blue;">else if</span> @loop=8<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Eight'''</span><br />
<span style="color: blue;">else if</span> @loop=9<br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Nine'''</span><br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @spell=<span style="color: red;">'''Ten'''</span><br />
<br />
<span style="color: blue;">if</span> @qry=''<br />
<span style="color: blue;">set</span> @qry=<span style="color: red;">'select '</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@loop)+<span style="color: red;">'num,'</span>+@spell+<span style="color: red;">'spell'</span><br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @qry=@qry+<span style="color: red;">' union '</span>+<span style="color: red;">'select '</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@loop)+','+@spell<br />
<span style="color: blue;">End</span><br />
<span style="color: blue;">exec</span> (@qry)<br />
<br />
#Results:<br />
<b>num spell</b><br />
1 One<br />
2 Two<br />
3 Three<br />
4 Four<br />
5 Five<br />
6 Six<br />
7 Seven<br />
8 Eight<br />
9 Nine<br />
10 Ten adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-72453645723742027632012-08-27T22:26:00.000-07:002012-08-29T21:11:40.163-07:00sql exec<b>Exec sintax on sql server</b> usually use to execute parameter<br />
<br />
see example below:<br />
<span style="color: blue;">select</span> (<span style="color: red;">'soemaryanto'</span>)usr_name,(<span style="color: red;">'admin'</span>)access_sttus,('active')active_status<br />
<br />
#Results:<br />
<b>usr_name access_sttus active_status</b><br />
soemaryanto admin active<br />
<br />
<span style="color: blue;">declare</span> @qry <span style="color: blue;">nvarchar</span>(100)<br />
<span style="color: blue;">set</span> @qry=(<span style="color: red;">'select (''soemaryanto'')usr_name,(''admin'')access_sttus,(''active'')active_status'</span>)<br />
<span style="color: blue;">select</span> (@qry)text_qry<br />
<br />
<br />
<b><span style="color: blue;">exec</span></b> (@qry)<br />
<br />
#Results:<br />
<b>text_qry</b><br />
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status<br />
<br />
<b>usr_name access_sttus active_status</b><br />
soemaryanto admin active<br />
<br />
another example exec sql server statement <br />
<span style="color: blue;">declare</span> @count <span style="color: blue;">int</span><br />
<span style="color: blue;">declare</span> @qry <span style="color: blue;">nvarchar</span>(100)<br />
<span style="color: blue;">declare</span> @qryBuffer <span style="color: blue;">nvarchar</span>(200)<br />
<span style="color: blue;">set</span> @count=0<br />
<span style="color: blue;">set</span> @qry=''<br />
<span style="color: blue;">set</span> @qryBuffer=''<br />
<br />
<span style="color: blue;">WHILE</span> not @count =10<br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;">set</span> @count=@count+1<br />
<span style="color: blue;">if</span> @qryBuffer=<span style="color: red;">''</span><br />
<span style="color: blue;">set</span> @qryBuffer=(<span style="color: red;">'select ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@count)+<span style="color: red;">')AutoNum'</span>)<br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @qryBuffer=@qryBuffer+<span style="color: red;">' union '</span>+(<span style="color: red;">'select ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@count)+<span style="color: red;">')'</span>)<br />
<span style="color: blue;">END</span><br />
<b><span style="color: blue;">exec</span></b> (@qryBuffer)<br />
<br />
#Results:<br />
<b>AutoNum</b><br />
1<br />
2<br />
3<br />
4<br />
5<br />
6<br />
7<br />
8<br />
9<br />
10adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-6891751936351852942012-08-26T21:40:00.000-07:002012-08-29T20:50:54.039-07:00day name in sql<span style="color: blue;">SELECT</span> <span style="color: magenta;">DATENAME</span>(dw, 1)NameofThisDay<br />
<br />
#Results:<br />
<b>NameofThisDay</b><br />
Tuesday<br />
<br />
<span style="color: blue;">SELECT</span> <span style="color: magenta;">DATENAME</span>(dw,<span style="color: red;"> '2012-08-27 11:12:38.803'</span>)NameofThisDay<br />
<br />
#Results:<br />
<b>NameofThisDay</b><br />
Monday<br />
<br />
Another example with complex case to get days a week with <b>while loop sql server</b> statement<br />
<br />
<span style="color: blue;">declare</span> @start <span style="color: blue;">int</span>,@end <span style="color: blue;">int</span><br />
<span style="color: blue;">declare</span> @qry <span style="color: blue;">nvarchar</span>(100)<br />
<span style="color: blue;">declare</span> @daysAWeek <span style="color: blue;">nvarchar</span>(1000)<br />
<span style="color: blue;">set</span> @start=1<br />
<span style="color: blue;">set</span> @end=8<br />
<span style="color: blue;">set</span> @daysAWeek=''<br />
<span style="color: blue;">while</span> not (@start=@end)<br />
<span style="color: blue;">begin</span><br />
<span style="color: blue;">if</span> @start=@end-1<br />
<span style="color: blue;">set </span>@qry=(<span style="color: blue;">select</span> (<span style="color: red;">'SELECT ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')dayNumber,</span><br />
<span style="color: red;"> DateName(dw,'</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')dayName'</span>)qry)<br />
<span style="color: blue;">else</span><br />
<span style="color: blue;">set</span> @qry=(<span style="color: blue;">select</span> (<span style="color: red;">'SELECT ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')dayNumber,</span><br />
<span style="color: red;"> DateName(dw,'</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')dayName union '</span>)qry)<br />
<br />
<span style="color: blue;">set</span> @daysAWeek=@daysAWeek+@qry <br />
<span style="color: blue;">set</span> @start=@start+1<br />
<span style="color: blue;">end</span><br />
<span style="color: blue;">exec</span> (@daysAWeek)<br />
<br />
#Results:<br />
<b>dayNumber dayName</b><br />
1 Tuesday<br />
2 Wednesday<br />
3 Thursday<br />
4 Friday<br />
5 Saturday<br />
6 Sunday<br />
7 Monday adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0tag:blogger.com,1999:blog-5061177345618672545.post-18181690938070425562012-08-26T20:00:00.000-07:002012-08-30T21:37:20.671-07:00sql month name<span style="color: blue;">declare</span> @start <span style="color: blue;">int</span>,@end <span style="color: blue;">int</span><br />
<span style="color: blue;">declare</span> @qry <span style="color: blue;">nvarchar</span>(100)<br />
<span style="color: blue;">declare</span> @summary <span style="color: blue;">nvarchar</span>(1200)<br />
<span style="color: blue;">set</span> @start=1<br />
<span style="color: blue;">set</span> @end=13<br />
<span style="color: blue;">set</span> @summary=''<br />
<span style="color: blue;">while</span> not (@start=@end)<br />
<span style="color: blue;"> begin</span><br />
<span style="color: blue;">if</span> @start=@end-1<br />
<span style="color: blue;">set</span> @qry=(select (<span style="color: red;">'SELECT ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')monthNumber, DateName(mm,DATEADD(mm,'</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">',-1))monthName'</span>)qry)<br />
<span style="color: blue;"> else</span><br />
<span style="color: blue;">set</span> @qry=(select (<span style="color: red;">'SELECT ('</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">')monthNumber, DateName(mm,DATEADD(mm,'</span>+<span style="color: magenta;">convert</span>(<span style="color: blue;">nvarchar</span>,@start)+<span style="color: red;">',-1))monthName </span><br />
<span style="color: red;"> union '</span>)qry)<br />
<br />
<span style="color: blue;">set </span>@summary=@summary+@qry <br />
<span style="color: blue;">set</span> @start=@start+1<br />
<span style="color: blue;">end</span><br />
<span style="color: blue;">exec</span> (@summary)<br />
<br />
#Results:<br />
<b>monthNumber monthName</b><br />
1 January<br />
2 February<br />
3 March<br />
4 April<br />
5 May<br />
6 June<br />
7 July<br />
8 August<br />
9 September<br />
10 October<br />
11 November<br />
12 December<br />
<br />
<span style="color: blue;">select</span> <b><span style="color: magenta;">DateName</span>(mm,<span style="color: red;">'2012-08-27 10:10:17.327'</span>)</b>currMonthName<br />
<br />
#Results:<br />
<b>currMonthName</b><br />
August <br />
<br />adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com2tag:blogger.com,1999:blog-5061177345618672545.post-6669169376843155082012-08-26T19:43:00.001-07:002012-08-29T20:49:48.899-07:00sql loop<span style="color: blue;">DECLARE</span> @count <span style="color: blue;">int</span><br />
<span style="color: blue;">SELECT</span> @count =11<br />
<br />
<span style="color: lime;">--Loop sql statement start here</span><br />
<b><span style="color: blue;">WHILE</span> @count > 0</b><br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;">SELECT</span> @count=@count-1<br />
<span style="color: blue;">IF</span> @count=0<br />
<span style="color: lime;">--BEGIN</span><br />
<span style="color: lime;"> -- BREAK</span><br />
<span style="color: lime;"> --END</span><br />
<br />
<span style="color: blue;">IF</span> @count=11<br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;">CONTINUE</span><br />
<span style="color: blue;">END</span><br />
<span style="color: lime;">--Show @count on Query Screen</span><br />
<span style="color: blue;">PRINT</span> @count<br />
<span style="color: blue;">END</span><span style="color: lime;"> </span><br />
<span style="color: lime;">--End of Loop</span><br />
<br />
#Results:<br />
10<br />
9<br />
8<br />
7<br />
6<br />
5<br />
4<br />
3<br />
2<br />
1<br />
0adminhttp://www.blogger.com/profile/10062629512582697162noreply@blogger.com0