Saturday, July 17, 2010

Calculate Standard Deviation Using SQL Server

SQL Procedure to get the Standard Deviation



Hello everyone, here is the formaula to calculate the standard deviation.




-- Create a temp table insert some rows as raw data.
DECLARE @tbl AS TABLE(ID INT IDENTITY(1,1) , Val DECIMAL(18,4));
INSERT INTO @tbl VALUES (10.20 );
INSERT INTO @tbl VALUES (99.27 );
INSERT INTO @tbl VALUES (23.56 );
INSERT INTO @tbl VALUES (56.92 );
INSERT INTO @tbl VALUES (02.37 );
SELECT * FROM @tbl;

-- Declare variables @AVG , @Count.
-- @AVG is the average of the "Val".
-- @Count is the number of records in the table.
DECLARE @AVG DECIMAL(18,4), @Count INT;
SELECT @AVG = AVG(VAL) , @Count = COUNT(ID) FROM @tbl;
SELECT @AVG AS Average , @Count AS RecordCount;

-- Declare the sum of Deviation
-- FORMULA = Single value is substracted by the average. The square of the result and sum of all the values will get "@SumDev".
DECLARE @SumDev DECIMAL(18,4);
SELECT @SumDev = SUM( SQUARE(VAL - @AVG) ) AS SumSquareDeviation FROM @tbl;
SELECT @SumDev;

-- Get the final stanard Deviation
-- FORMULA = Gets the last @SumDev divide it by a number less than the total number of rows(@Count - 1). Get the quareroot and get the Standard Deviation
SELECT SQRT((@SumDev /(@Count-1))) AS StandardDeviation;


Thursday, July 8, 2010

Moving and Re-Ordering List Items within listboxes using jquery

ASPX Html





<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MoveItemsFromOneListBoxToAnother.aspx.cs" Inherits="MoveItemsFromOneListBoxToAnother" EnableEventValidation="false" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
body{font-size:13px; font-family:Verdana;}
</style>
<script language="javascript" type="text/javascript" src="JS/JQuery.js"></script>
<script language="javascript" type="text/javascript">

//On Ready
$(document).ready(function() {

//Selects the option on the postback event
SetTheDefaultSelected();

//Double click settings for the LIST items
$("select[name*='lstNonSelected']").dblclick(function() {
MoveToSelection();
});
$("select[name*='lstSelected']").dblclick(function() {
MoveFromSelection();
});

});

//Move to Selection box
function MoveToSelection() {

MoveTheOptions($("select[name*='lstNonSelected']").attr("id"), $("select[name*='lstSelected']").attr("id"));
}

//Move from Selection box
function MoveFromSelection() {
MoveTheOptions($("select[name*='lstSelected']").attr("id"), $("select[name*='lstNonSelected']").attr("id"));
}

//Swap the values
function MoveTheOptions(listBox1, listBox2) {
//Check if the list box is having any options selected
if ($("#" + listBox1 + " option:selected").val() == null) {
alert("Please select an option.");
return;
}

//Get iteself to an array
var arr = $("#" + listBox1 + " option:selected").map(function() { return "<option value='" + $(this).val() + "'>" + $(this).text() + "</option>"; });

//Insert a new option(s)
$("#" + listBox2 + "").append($.makeArray(arr).join(""));

//Remove the first list box
$("#" + listBox1 + " option:selected").remove();

//Refills the paycodes
RefillHiddenTextBox();
}

//Refills the txt pay codes
function RefillHiddenTextBox() {
//Refill the screen
var txt = $("select[name*='lstSelected'] option").map(function() { return this.value }).get().join(",");

//Sets the values to the textbox
$("input[name*='txtSelected']").val(txt);
}

//Set The Default Selected
function SetTheDefaultSelected() {
//Select Pay Codes
if ($("input[name*='txtSelected']").val() != "") {
//On the basis of value select the options
var txt = $("input[name*='txtSelected']").val().split(',');
//On the basis of value select the options
for (var i = 0; i < txt.length; i = i + 1) {
//Get the values
var val = $("select[name*='lstNonSelected'] option:[value='" + txt[i] + "']").val();
var text = $("select[name*='lstNonSelected'] option:[value='" + txt[i] + "']").text();

//Insert a new option
$("select[name*='lstSelected']").append("<option value='" + val + "'>" + text + "</option>");
//Remove the first list box
$("select[name*='lstNonSelected'] option:[value='" + txt[i] + "']").remove();
}
}
}

//Moves the options up or down using the buttons on the right hand side of the Selected List
function MoveUpOrDown(isUp)
{
var listbox = $("select[name*='lstSelected']");
if ($(listbox).find("option:selected").length == 0) {
alert("You need to select atleast one selection.");
}
else if ($(listbox).find("option:selected").length > 1) {
alert("You can only select one option to move Up or Down.");
}
else {
//Get the values
var val = $(listbox).find("option:selected").val();
var text = $(listbox).find("option:selected").text();
var index = $(listbox).find("option:selected").index();

//Get the length now
var length = $(listbox).find("option").length;

//Move it up or down
if (isUp == true) {
//Move the option a row above
index = (index == 0 ? index : index - 1);
//Insert the options
$("<option value='" + val + "'>" + text + "</option>").insertBefore($(listbox).find("option:eq(" + index + ")"));
}
else // Down
{
//Move the option a row down
index = (index == length - 1 ? index : index + 1);

$("<option value='" + val + "'>" + text + "</option>").insertAfter($(listbox).find("option:eq(" + index + ")"));
}

//Remove the options
$(listbox).find("option:selected").remove();
//Set the value as selected
$(listbox).val(val);

//Refill the textbox
RefillHiddenTextBox();
}
}

</script>
</head>
<body>
<form id="form1" runat="server">
<h1>Moving and Re-Ordering List Items within listboxes</h1>
<table cellspacing="5">
<tr>
<td colspan="4" align="center" style="color:Red"><asp:Literal ID="ltrlViewOption" runat="server" /></td>
</tr>
<tr>
<td><i><b>Non Selected Items</b></i></td>
<td>&nbsp;</td>
<td><i><b>Selected Items</b></i></td>
<td>&nbsp;</td>
</tr>
<tr>
<td><asp:ListBox ID="lstNonSelected" runat="server" Rows="10" CssClass="form" Width="250px" SelectionMode="Multiple" /></td>
<td>
<input type="button" class="button" value=">" onclick="MoveToSelection();" /><br /><br /><input type="button" class="button" value="<" onclick="MoveFromSelection();" />
</td>
<td><asp:ListBox ID="lstSelected" runat="server" Rows="10" CssClass="form" Width="250px" SelectionMode="Multiple" /></td>
<td>
<input type="button" class="button" value="Up" onclick="MoveUpOrDown(true) ;" style="width:50px" /><br /><br /><input type="button" class="button" value="Down" onclick="MoveUpOrDown(false) ;" style="width:50px" />
</td>
</tr>
<tr>
<td colspan="4" align="center"><asp:TextBox ID="txtSelected" runat="server" /><asp:Button ID="btnView" runat="server" Text="View Selected Options On Server Side" /></td>
</tr>
</table>
</form>
</body>
</html>



C# Code behind





using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class MoveItemsFromOneListBoxToAnother : System.Web.UI.Page
{
//The list of data to be bound to the list
private IList<BindListClass> BindList
{
get
{
IList<BindListClass> obj = new List<BindListClass>();
obj.Add(new BindListClass() { Id = 1, Name = "James Bond" });
obj.Add(new BindListClass() { Id = 2, Name = "Bruce Wayne" });
obj.Add(new BindListClass() { Id = 3, Name = "Peter Parker" });
obj.Add(new BindListClass() { Id = 4, Name = "Tonk Stark" });
obj.Add(new BindListClass() { Id = 5, Name = "Ethen Hunt" });
return obj;
}
}

protected void Page_Load(object sender, EventArgs e)
{
//Hide the selected option
txtSelected.Style.Add("display", "none");

if (!IsPostBack)
{
//Bind the non selected list
lstNonSelected.DataSource = BindList;
lstNonSelected.DataTextField = "Name";
lstNonSelected.DataValueField = "ID";
lstNonSelected.DataBind();
}
else
{
//Show the selected values on the postback of the screen
ltrlViewOption.Text = "\""+ txtSelected.Text +"\" are the selected options after postback.";
}
}
}

public class BindListClass
{
public int Id { get; set; }

public string Name { get; set; }
}



Snapshot of Screen






Important Features





  • Can move the items from one listbox to another using buttons with text "<"
    and ">".

  • On double clicking will work on the listitems.

  • "Up" and "Down" buttons on the right of the Selected list will reorder the liteitems.

  • "View Selected Options On Server Side" will show the items selected on the server side.


Color adjustable CSS Buttons


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Web2CssButtonStyle.aspx.cs" Inherits="Web2CssButtonStyle" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.div-overlay-btn {float: left;clear: both;background: url(images/btn-left.png) no-repeat;padding: 0 0 0 10px;margin: 5px 0;}
.div-overlay-btn a{float: left;height: 40px;background: url(images/btn-center.png) repeat-x left top;line-height: 40px;padding: 0 10px;color: #fff;font-size: 1em;text-decoration: none;}
.div-overlay-btn span {background: url(images/btn-right.png) no-repeat;float: left;width: 10px;height: 40px;}
.btn-overlay-red{background-color:red}
.btn-overlay-blue{background-color:blue}
.btn-overlay-green{background-color:green}
.btn-overlay-yellow{background-color:yellow}
.btn-overlay-black{background-color:black}
</style>
</head>
<body>
<form id="form1" runat="server">
<h1>Color adjustable CSS Buttons</h1>
<div class="div-overlay-btn btn-overlay-red"><a href="#">Red</a><span></span></div>
<br /><div class="div-overlay-btn btn-overlay-blue"><a href="#">Blue</a><span></span></div>
<br /><div class="div-overlay-btn btn-overlay-green"><a href="#">Green</a><span></span></div>
<br /><div class="div-overlay-btn btn-overlay-yellow"><a href="#">Yellow</a><span></span></div>
<br /><div class="div-overlay-btn btn-overlay-black"><a href="#">Black</a><span></span></div>
</form>
</body>
</html>



The images used are








Click here to find more colors.

Centered overlay using DIV, CSS and JQuery

ASPX Html





<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DivOverlayUsingCSSAndJquery.aspx.cs" Inherits="DivOverlayUsingCSSAndJquery" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body{font-family:Verdana;}
.div-overlay{}
.div-overlay-background{top:0;left:0;position:absolute;background-color: #0B0F0C;filter: alpha(Opacity=50);opacity: 0.5;-moz-opacity: 0.5;-khtml-opacity: 0.5;z-index:4;}
.div-overlay-box{position:absolute;width:698px;height:197px;background:url(images/overlay-box-700X200.png) no-repeat left top;z-index:1000;padding:5px}
.div-overlay-close{position:absolute;padding-left:655px;padding-top:5px;cursor:hand;}
.div-overlay-title{font-size:30px;height:75px;width:275px;padding-top:20;color:#004DBA;padding:15px;}
.div-overlay-message{font-size:12px;height:40px;width:600px;}
</style>
<script language="javascript" type="text/javascript" src="JQuery.js"></script>
<script language="javascript" type="text/javascript">

//Shows the overly once ready
$(document).ready(function() {

//Set the image event
$("#imgClose").click(function() { $("#divMainOverlay").hide(); });

});

//Shows the box
function ShowOverlay() {
//Set the overlay
$("#divMainOverlay").show();
$(window).scrollTop(0);

//Declarations
var winHeight = $(window).height();
var winWidth = $(window).width();

var divTop = (winHeight / 2) - ($("#divOverlayBox").height() / 2);
var divLeft = (winWidth / 2) - ($("#divOverlayBox").width() / 2);

$("#divOverlay").height(winHeight + 400);
$("#divOverlay").width(winWidth + 400);

$("#divOverlayBox").css("top", divTop + "px");
$("#divOverlayBox").css("left", divLeft + "px");

}
</script>
</head>
<body>
<form id="form1" runat="server">
<input type="button" value="Click to open the overlay using DIV, CSS & Jquery" onclick="javascript:ShowOverlay();" />

<!-- Main DIV -->
<div id="divMainOverlay" name="divMainOverlay" style="display:none">
<!-- Background Settings -->
<div id="divOverlay" name="divOverlay" class="div-overlay-background"></div>
<!-- Background Settings -->
<!-- Content Div -->
<div id="divOverlayBox" name="divOverlayBox" class="div-overlay-box">
<div class="div-overlay-close"><img id="imgClose" name="imgClose" alt="Close" title="Close" src="images/overlay-box-close.png" /></div>
<span class="div-overlay-title">Div based overlay</span><br /><br />
<blockquote class="div-overlay-message">Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. Div, Css & Jquery works perfect. </blockquote>
</div>
<!-- Content Div -->
</div>
<!-- Main DIV -->
</form>
</body>
</html>



Please use these images











Final view


Tuesday, July 6, 2010

Multiple checkbox selction with jquery

ASPX HTML



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MultipleCheckBoxMultipleGridSelectionOnSinglePage.aspx.cs"
Inherits="MultipleCheckBoxMultipleGridSelectionOnSinglePage" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script language="javascript" type="text/javascript" src="JQuery.js"></script>
<style>
body{font-family:Arial;}
table{border:1px solid #888;width:200px;}
td{font-size:15px;border:1px solid #efefef; border-collapse:collapse;padding:5px;}
.trheader{background:#000;color:#fff;font-weight:bold;}
</style>
<script language="javascript" type="text/javascript">

//Document ready function to bind all the actions
$(document).ready(function() {

//Find all the main checkbox and assign an event
$("span[ChkType='MAIN']").click(function() {

//Get the TAG and Values
var TAG = $(this).attr("TAG");
var IsChecked = $(this).find("input[type='checkbox']").attr("checked");

//Bind the list
$("span[ChkType='SELECT'][TAG='" + TAG + "']").find("input[type='checkbox']").attr("checked", IsChecked);
});

//The other checkboxes with SELECT as Type
$("span[ChkType='SELECT']").click(function() {

//Get the TAG and Values
var TAG = $(this).attr("TAG");
var IsChecked = ($("span[ChkType='SELECT'][TAG='" + TAG + "']").find("[checked='true']").length == $("span[ChkType='SELECT'][TAG='" + TAG + "']").length);

//Bind the list
$("span[ChkType='MAIN'][TAG='" + TAG + "']").find("input[type='checkbox']").attr("checked", IsChecked);
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<h1>Multiple Checkbox Selection With Mutiple Grid on Single Page</h1>
<asp:Repeater ID="rptList1" runat="server">
<HeaderTemplate>
<table cellpadding="0" cellspacing="0" border="0">
<tr class="trheader">
<td align="center">
<asp:CheckBox ID="chkMain" runat="server" ChkType='MAIN' TAG='LIST-1' />
</td>
<td align="center">
<b>List 1</b>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center">
<asp:CheckBox ID="chkSelect" runat="server" ChkType='SELECT' TAG='LIST-1' />
</td>
<td>
<%#Eval("Name") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
<br /><br />
<asp:Repeater ID="rptList2" runat="server">
<HeaderTemplate>
<table cellpadding="0" cellspacing="0" border="0">
<tr class="trheader">
<td align="center">
<asp:CheckBox ID="chkMain" runat="server" ChkType='MAIN' TAG='LIST-2' />
</td>
<td align="center">
<b>List 2</b>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center">
<asp:CheckBox ID="chkSelect" runat="server" ChkType='SELECT' TAG='LIST-2' />
</td>
<td>
<%#Eval("Name") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
<br /><br />
<asp:Repeater ID="rptList3" runat="server">
<HeaderTemplate>
<table cellpadding="0" cellspacing="0" border="0">
<tr class="trheader">
<td align="center">
<asp:CheckBox ID="chkMain" runat="server" ChkType='MAIN' TAG='LIST-3' />
</td>
<td align="center">
<b>List 3</b>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center">
<asp:CheckBox ID="chkSelect" runat="server" ChkType='SELECT' TAG='LIST-3' />
</td>
<td>
<%#Eval("Name") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>



C# Code



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class MultipleCheckBoxMultipleGridSelectionOnSinglePage : System.Web.UI.Page
{
private IList<BindListClass> BindList
{
get
{
IList<BindListClass> obj = new List<BindListClass>();
obj.Add(new BindListClass() { Id = 1, Name = "James Bond" });
obj.Add(new BindListClass() { Id = 2, Name = "Bruce Wayne" });
obj.Add(new BindListClass() { Id = 3, Name = "Peter Parker" });
obj.Add(new BindListClass() { Id = 4, Name = "Tonk Stark" });
obj.Add(new BindListClass() { Id = 5, Name = "Ethen Hunt" });
return obj;
}
}

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Bind List 1
rptList1.DataSource = BindList;
rptList1.DataBind();

//Bind List 2
rptList2.DataSource = BindList;
rptList2.DataBind();

//Bind List 3
rptList3.DataSource = BindList;
rptList3.DataBind();
}
}
}

public class BindListClass
{
public int Id { get; set; }

public string Name { get; set; }
}



Final Preview of the Screen