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