SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor

If you have ever had the need to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time, but were not exactly sure how to tackle the problem then this article might help. In this article I will show you a few different techniques for calculating and summing information on multiple rows without using a cursor. The techniques I will show you will just use a basic SELECT statement. Of course, the calculations of the running total, subtotals and grand total will be done using other SQL clauses and functions like SUM and CASE.

create table Orders
(OrderID int identity,
OrderAmt Decimal(8,2),
OrderDate SmallDatetime)

OrderID OrderAmt OrderDate
----------- ---------- --------------------
1 10.50 2003-10-11 08:00:00
2 11.50 2003-10-11 10:00:00
3 1.25 2003-10-11 12:00:00
4 100.57 2003-10-12 09:00:00
5 19.99 2003-10-12 11:00:00

Running Total On Each Record

This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The "Running Total" column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.

select OrderId, OrderDate, O.OrderAmt
,(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders O

When I run this query against my Orders table I get the following results:

OrderId OrderDate OrderAmt Running Total
----------- -------------------- ---------- -------------
1 2003-10-11 08:00:00 10.50 10.50
2 2003-10-11 10:00:00 11.50 22.00
3 2003-10-11 12:00:00 1.25 23.25
4 2003-10-12 09:00:00 100.57 123.82
5 2003-10-12 11:00:00 19.99 143.81

As you can see, there is a "Running Total" column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.
Running Total for Each OrderDate

This example is similar to the one above, but I will calculate a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day. Here is the code to accomplish this:

select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID

Here are the results returned from the query using my sample Orders Table:

OrderId Order Date OrderAmt Running Total
----------- ---------- ---------- ---------------
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56

Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
Running Totals With Subtotals and Grand totals

In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.

select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID

Output from the SELECT statement looks like this:

OrderID Order Date OrderAmt Sub Total Grand Total
----------- ---------- ---------- ---------- -----------
1 10/11/2003 10.50
2 10/11/2003 11.50
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14
7 10/13/2003 10.08

In this example the first CASE statement controls the printing of the "Sub Total' column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the "Grand Total", which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the "Grand Total".


Conclusion

Hopefully these examples will help you understand different methods that can be used to calculate running totals, sub totals, and a grand total. As you can see you don't need to use a cursor to calculate these different totals. With the creative use of correlated sub queries and other SELECT clauses like CASE you can easily create all these different totals. Next time you need to calculate totals consider using one of these non-cursor based solutions.

Replace field values with static values in ms sqlserver

In certain cases we have to substitute static values instead of the actual database respresentation.ie sometimes we will represent the availability of a product using integer datatype and 0- will represent an available product, 1-will represent an Occupied product and 3 will represenet a blocked product. Inorder to convey the message to the end user we have to represent the status in the form of Available,Occupied etc. in such a case sql server provides you an option to use case statement.

select fldId,
case
when fldStatus = 0 then 'Available'
when fldStatus = 1 then 'Occupied'
else 'Blocked'
end as status,
fldPrice
from tblProduct

with regards,
Praveen.S.L
www.linesandgraphs.com

Generating Connection String from web.config

web.config
add name="myConnectionString" connectionString="Data Source=PRAVEEN;Initial Catalog=dbOoh;Persist Security Info=True;User ID=user;Password=pwd"



String constring = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
SqlConnection con = new SqlConnection(constring);

SEO

http://www.seocompany.ca/tool/seo-tools.html#link-popularity-tools
https://adwords.google.com/select/KeywordSandbox
http://www.seocompany.ca/tool/9-keyword-tools.html
http://www.digitalpoint.com/tools/suggestion/
http://tools.organicseo.us/seo-tools1.htm

Resizing the picture according to browser size using javascript

You can include this code on the head section of your html code and the window will automatically trigger the window resize event.

window.onresize=function(event)
{
var myWidth = 0, myHeight = 0;
if( typeof( window.innerWidth ) == 'number' )
{
//Non-IE
myWidth = window.innerWidth;
myHeight = window.innerHeight;
}
else if( document.documentElement && ( document.documentElement.clientWidth || document.documentElement.clientHeight ) )
{
//IE 6+ in 'standards compliant mode'
myWidth = document.documentElement.clientWidth;
myHeight = document.documentElement.clientHeight;
}
else if( document.body && ( document.body.clientWidth || document.body.clientHeight ) )
{
//IE 4 compatible
myWidth = document.body.clientWidth;
myHeight = document.body.clientHeight;
}
//window.alert( 'Width = ' + myWidth );
// window.alert( 'Height = ' + myHeight );

img= document.getElementById('myimage');
img.width= myWidth;
img.height=myHeight;

}
also include the below code on your body section.
img id="myimage" src="pr_shopping_bag.jpg" width="500" height="555"

Please make sure that u will use the same id on the image tag and the head section "document.getElementById('myimage')".


best wishes,
Praveen
www.linesandgraphs.com

Using Viewstate for Saving datatable in asp.net c#

Using a viewstate to save a datatable is useful for developting shopping cart applications. In such a case we have to keep track of all the products that has been selected by the customer on a particular session. For this we use a combination of view state or session to hold the product information in datatable.

Creating a DataTable and saving it on the view state :

private void createMyDataTable()
{

DataTable dtCart = new DataTable();
DataColumn dc = new DataColumn();
DataColumn dc1 = new DataColumn();
DataColumn dc2 = new DataColumn();

dc.DataType = Type.GetType("System.Int32");
dc.ColumnName = "Id";
dc.AutoIncrement = true;
dc.AutoIncrementSeed = 1;
dtCart.Columns.Add(dc);
dc1.DataType = Type.GetType("System.String");
dc1.ColumnName = "ProductName";
dtCart.Columns.Add(dc1);
dc2.DataType = Type.GetType("System.Int32");
dc2.ColumnName = "Price";
dtCart.Columns.Add(dc2);

ViewState["dtMyCart"] = dtCart;

}
from the above code we can create a structure of a datatable which can hold the information of the products selected by the client. Once the structure of the table is created we have to save the datatable to a viewstate object. This is done to acess the table later in the page.

Once the datatable structure is saved on our viewstate, we can start using the datatable for saving products. Procedure for adding a new product is as follows

private void addDataToMyTable(string paramProductName, int paramProductId, Int32 paramPrice)
{
MyNewDataTable = (DataTable)ViewState["dtMyCart"];
DataRow row;
row = MyNewDataTable.NewRow();
row["ProductName"] = paramProductName;
row["Id"] = paramProductId;
row["Price"] = paramPrice;


MyNewDataTable.Rows.Add(row);
ViewState["dtMyCart"] = MyNewDataTable;

}

Here in this function, we have four parameters each one has a value for the product. First of all we have to create an instance of the datatable from the viewstate after that we can start adding the product into the user cart.


with regards,
Praveen

Rowbound Property for Gridview Control

This can be a simple tutorial but I think it is useful for beginners. You can use this code snippet to dynamically change the value of an ImageButton while the row is being generated. It is useful in cases where we are listing Images of a product and we have to add a noimage pic ,if there is no image available.

protected void gvLastUpdated_RowDataBound(object sender, GridViewRowEventArgs e)
{
try
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
ImageButton imgButton = (ImageButton)e.Row.FindControl("ImageButton1");

if (imgButton.ImageUrl.ToString().Trim() == "")
{
imgButton.ImageUrl = "~\\uploads\\noimage.jpg";
}
else
{
imgButton.ImageUrl = "~\\uploads\\" + imgButton.ImageUrl.ToString();

}
}
}
catch (Exception e1)
{
lblMessage.Text = (e1.ToString());
}
}

with Regards,
Praveen
http://www.linesandgraphs.com

Identifying controls in a row that generates an event in a datagrid control

suppose if we are using a Template control to place so many controls in a gridrow and we need to track the row from which control has generated an event in such a case we can use the following code.

protected void btnCart_Click(object sender, EventArgs e)
{
Button btn= (Button)sender;
GridViewRow grdRow =(GridViewRow) btn.Parent.Parent;
}

Here i have a button inside my template control and once i click my button from a data row i can create an object of the row that has generated the click event.

by using the grdRow instance you can acess the elements within that row.
Label lbProdTitle = (Label)(grdRow.Cells[0].FindControl("fldPropertyTitleLabel1"));
Label lbPrice = (Label)(grdRow.Cells[0].FindControl("fldPrice"));

with regards,
Praveen
www.linesandgraphs.com