Dubli Join Us

Search About New Technology and Bug

Tuesday, August 31, 2010

MAX Function : SQL SERVER

The MAX function returns the maximum value of an expression.
The syntax for the MAX function is:

SELECT MAX(expression )
FROM tables
WHERE predicates;



Simple Example
For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) as "Highest salary"
FROM employees;


In this example, we've aliased the max(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.
Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the MAX function.
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;


Because you have listed one column in your SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Joins Operator: SQL SERVER

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.
There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)
Chances are, you've already written an SQL statement that uses an inner join. It is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.
For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;


This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Outer Join
Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).
For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.
The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as in the result set.
The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id

SUM Function: SQL SERVER

The SUM function returns the summed value of an expression.
The syntax for the SUM function is:

SELECT SUM(expression )
FROM tables
WHERE predicates;

expression can be a numeric field or formula.
Simple Example
For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year.

SELECT SUM(salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

In this example, we've aliased the sum(salary) field as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.
Example using DISTINCT

You can use the DISTINCT clause within the SUM function. For example, the SQL statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year.

SELECT SUM(DISTINCT salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values would be used in the SUM function.
Example using a Formula
The expression contained within the SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses.

SELECT SUM(income - expenses) as "Net Income"
FROM gl_transactions;


You might also want to perform a mathematical operation within a SUM function. For example, you might determine total commission as 10% of total sales.

SELECT SUM(sales * 0.10) as "Commission"
FROM order_details;


Example using GROUP BY
In some cases, you will be required to use a GROUP BY clause with the SUM function.
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;



Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Use ungroup Columns in Select Query using Group By Clause:SQL SERVER


hi,
I have face a problem many times to build some complex reports in crystal report that need some  group by column and also have some ungroup columns.
For example, let i have a table of that structure
in which i want to group data according to Areacode,AreaName,AreaRank and also want to sum of TeamCollection with other fields also such as LocalAreaCode ,LocalAreaRank, and there is one condition for having only those reords which have CollectionPer >=100 for each areacode and arearank=9 and creatdate= '11/14/2009' .
Here is sql query that provide me corect data.
SELECT     a.AreaCode, a.AreaName, a.AreaRank, a.CreatDate, a.LocalAreaCode, a.LocalAreaRank, a.SelfCollection, a.TeamCollection, a.CreatDate AS CreatDate ,
a.CollectionPer
FROM         tbl_AreaCollection AS a INNER JOIN
(SELECT DISTINCT AreaCode
FROM          tbl_AreaCollection
GROUP BY AreaCode
HAVING      (SUM(CollectionPer) >= 100)) AS b ON a.AreaCode = b.AreaCode AND a.CreatDate = '11/14/2009' AND a.AreaRank = 9
ORDER BY a.AreaCode
here is the output

AJAX Control Toolkit Release for ASP.Net 2.0

Hi all,i found that link for asp.net 2.0 ajax latest support. So if you are working on asp.net 2.0 using ajax then you can ajax tool kit from this link.
http://ajaxcontroltoolkit.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=11121

  • AjaxControlToolkit.zip is the full release package with complete source code to all controls, the test framework, VSI, and more.
  • AjaxControlToolkit-NoSource.zip contains only the sample web site and VSI and is for people who don't need or want the source code for the controls

Delete Duplicate Records Rows :SQL SERVER

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM
MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)

Get Country Name Through IP Address

There are situations where we have to track visitors. The tracking might include the country from the visitor has browsed the website. These statistics are often important.
The major advantage of this method is a single DLL. It does not require any database setup etc. You just need to add reference to the IpToCountryLib DLL provided in the solution.
To resolve the country name, the library API to be used is "GetCountry". GetCountry takes in a string parameter which is the IP Address and returns the country name as the out parameter.
This is the code of the function,
C#
public bool GetCountry(string userHostIpAddress, out string countryName)
{
bool result = false;
countryName = string.Empty;
if (string.IsNullOrEmpty(userHostIpAddress))
return false;
IPAddress ipAddress;
if (IPAddress.TryParse(userHostIpAddress, out ipAddress))
{
countryName = ipAddress.Country();
result = true;
}
return result;
}

To use the function you will do a call like this,
C#,
string country;
GetCountry("111.111.111.111",out country);

Managing State with ASP.NET and C#

Web Applications are disconnected in nature which means that there is no way for the browser to know who is using the application at present time. In classic Asp programming maintaining state was a headache for the developers. They had to write alot of code to maintain state. But Asp.net model provides easy state management. In this article we will see how we can persist state in multiple pages so the user is recognized by the browser.

Preserving State in Web ApplicationsThere are number of ways that you can use to preserve the State of the Web Application. Here are some of the possible ways listed.

1) Using Cookies (Client Side State Management technique)
2) Using Session States
3) Using Application States
4) Using HttpContext collection
5) Using ViewState (Client Side State Management technique)

These are only some ways of storing the state of the user as well as the application. In this article we will see few of them in detail.