Comments by IsaiSelvan (Top 36 by date)

Yes i want to get median for each row.

First Row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8

Actuval Value - 4.80 (this is wrong)
Expected value - 5 (Correct Output)

Second Row- 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Actual Value - 4.47 (this is wrong)
Expected Value - 4 (Correct Output)

Am already tried that format. the below is the sample data with query.

select
(select cast(Avg(TotAvg * 1.0 )as decimal(6,2)) from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b)),(convert(decimal(6,2), c)),
(convert(decimal(6,2), d)),(convert(decimal(6,2), e)),
(convert(decimal(6,2), f)),(convert(decimal(6,2), g)),(convert(decimal(6,2), h)),(convert(decimal(6,2), i)),
(convert(decimal(6,2), j)),(convert(decimal(6,2), k)),(convert(decimal(6,2), l)),(convert(decimal(6,2), m)),
(convert(decimal(6,2), n)),(convert(decimal(6,2), o)),(convert(decimal(6,2), p)),(convert(decimal(6,2), q)),
(convert(decimal(6,2), r)),(convert(decimal(6,2), s)),(convert(decimal(6,2), t)),(convert(decimal(6,2), u))) as Totalavg(TotAvg))
as Median from temp

I changed you mentioned column. help me


column values are a to u in a row.

Table Values
============
First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Am already tried that format. the below is the sample data with query.

select
(select cast(Avg(TotAvg * 1.0 )as decimal(6,2)) from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b)),(convert(decimal(6,2), c)),
(convert(decimal(6,2), d)),(convert(decimal(6,2), e)),
(convert(decimal(6,2), f)),(convert(decimal(6,2), g)),(convert(decimal(6,2), h)),(convert(decimal(6,2), i)),
(convert(decimal(6,2), j)),(convert(decimal(6,2), k)),(convert(decimal(6,2), l)),(convert(decimal(6,2), m)),
(convert(decimal(6,2), n)),(convert(decimal(6,2), o)),(convert(decimal(6,2), p)),(convert(decimal(6,2), q)),
(convert(decimal(6,2), r)),(convert(decimal(6,2), s)),(convert(decimal(6,2), t)),(convert(decimal(6,2), u))) as Totalavg(TotAvg))
as Median from temp

I changed you mentioned column. help me


column values are a to u in a row.

Table Values
============
First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Table Values
============
First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

thank you. its working well in 2014. but i'm working 2008 r2 is not supporting in pivot and unpivot method. if i used this need to modify the compatability setting in sql server 2008 r2.

ok thanks. i'm also getting the error message. then how can i find the median value. can you please give me the suggestion

below table format is the actual result.

Expected Result:
================
--------+-------------+-------+-----+-------------+
Median | 2StanddDevi | Count | Min | Max Outlier |
--------+-------------+-------+-----+-------------+
6.25 | 4.412270768 | | | |
5.25 | 4.110490644 | | | |
4.25 | 3.055050463 | | | |
6.32 | 0.102632029 | | | |
7.35 | 1.425342064 | | | |
7.01 | 1.32378246 | | | |
7.03 | 3.001266399 | | | |
6.32 | 2.127377102 | | | |
--------+-------------+-------+-----+-------------+

the below fomat result i need.

Expected Result:
================
--------+-------------+-------+-----+-------------+
Median | 2StanddDevi | Count | Min | Max Outlier |
--------+-------------+-------+-----+-------------+
6.25 | 4.412270768 | | | |
5.25 | 4.110490644 | | | |
4.25 | 3.055050463 | | | |
6.32 | 0.102632029 | | | |
7.35 | 1.425342064 | | | |
7.01 | 1.32378246 | | | |
7.03 | 3.001266399 | | | |
6.32 | 2.127377102 | | | |
--------+-------------+-------+-----+-------------+

How to find the Median, 2 standard deviations, min range , max range in SQL Server 2008?

Actual Table:
=============
+-----+-----+------+-------+------------+
| Rat | Cat | A | B | C |
+-----+-----+------+-------+------------+
| AAA | 1 | 6.5 | 6.25 | 2.56 |
| AAA | 3 | 5.25 | 7.36 | 3.25 |
| AAA | 5 | 3.25 | 6.25 | 4.25 |
| AAA | 7 | 6.25 | 6.32 | 6.35 |
| AA | 1 | 7.23 | 8.52 | 7.35 |
| AA | 3 | 7.83 | 7.01 | 6.52 |
| AA | 5 | 7.2 | 7.03 | 4.52 |
| AA | 7 | 7.14 | 5.03 | 6.32 |
+-----+-----+------+-------+------------+

Expected Result:
================
--------+-------------+-------+-----+-------------+
Median | 2StanddDevi | Count | Min | Max Outlier |
--------+-------------+-------+-----+-------------+
6.25 | 4.412270768 | | | |
5.25 | 4.110490644 | | | |
4.25 | 3.055050463 | | | |
6.32 | 0.102632029 | | | |
7.35 | 1.425342064 | | | |
7.01 | 1.32378246 | | | |
7.03 | 3.001266399 | | | |
6.32 | 2.127377102 | | | |
--------+-------------+-------+-----+-------------+


The below query am using, but not able to get median value

while (Rec > 0)
begin

set Id=Id+1;

if(Id=1)
begin

set Sqlqry = 'insert into tempCal (Median) '
set Sqlqry = Sqlqry + ' SELECT cast(AVG(1.0 * '+olval+') as decimal(10,2)) AS Median from
(SELECT isnull('+colval+','''') AAA, ra = ROW_NUMBER() OVER (ORDER BY Rating, Id ),
rd = ROW_NUMBER() OVER (ORDER BY Rat DESC, Id desc) from temptbl) as x WHERE ra BETWEEN rd - 1 AND rd + 1;'
exec(Sqlqry)


end

select * from tempCal


set @Rec=@Rec-1;

end

yes rt. Yesterday i'm forgot to add a one more column in this post so that, i'm added today please help me. this is very urgent.

thanks for you reply. I using sql 2008 pivot functionality not support in 2008. max also not possible to bind it. Kindly help me.

Hi Animesh,

I've some questions for that format so, that am asking. Kindly help me. Last two days am trying but not able retrieve exact result.

ok thanks. I'll try

I'm trying to this way but i'm getting error. Can you please provide a query for above mention table format

ok. i'll check. because some of them given then solution, but it's not working for the appln. thanks for you response. if you find anything let me know.

thanks for your reply. i want to uninstall any one of the software (my .exe) using windows application not for web application.

I checked the code but, its not working. Can you please update the code.

ok. ill check. thanks.

yes right. this is web application. few months before working on this concept but, now its working this. need to fix asap.

thanks vinay.

I am also getting same error this code. Do you know why this error came on runtime?

Ok vinay. But i need to bind the images from local drive not for the project solution image folder. Is this possible to bind the images from local drive?

yes am debug on this code. the image path will be bind in variable. but i cant to show on runtime event.

I am getting same error message deepgalley

this is my code behind code:

private void FillDataGrid()
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
string CmdString = string.Empty;
using (SqlConnection con = new SqlConnection(ConString))
{
CmdString = "SELECT emp_id, name, dept, salary FROM employee";
SqlCommand cmd = new SqlCommand(CmdString, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("employee");
sda.Fill(dt);
grdemployee.ItemsSource = dt.DefaultView;
}
}

i'm already tried this way but, its working in my code. Actually, i'm trying to bind the data's in grid view inside text boxes, i can able to bind the single row in grid. But, I can't bind the more than one rows in at the same time. I'm posted the code here.

protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)
{
// int RowIndex = 0;

con.Open();


DataSet ds = new DataSet();

if (e.CommandName.Equals("Select"))
{
GridViewRow clickedRow = ((LinkButton)e.CommandSource).NamingContainer as GridViewRow;
Label lblbill = (Label)clickedRow.FindControl("lblno");
Label lbld = (Label)clickedRow.FindControl("lbldate");
Label lblt = (Label)clickedRow.FindControl("lbltime");
Label lblwaiter = (Label)clickedRow.FindControl("lblwaiterno");
Label lblcover = (Label)clickedRow.FindControl("lblcovers");

MySqlCommand cmd = new MySqlCommand("select bd.prod_name, bd.qty, bd.prod_rate, bd.prod_price, bt.qty as qtybill, bt.bill_amt, bt.bill_vat, bt.bill_service_tax, bt.bill_service_charge, bt.bill_total from bill_totals bt left join bill_details bd on bt.bill_no = bd.bill_no where bd.bill_no = '" + lblbill.Text + "'", con);
MySqlDataReader dr = cmd.ExecuteReader();

txtcovers.Text = lblcover.Text;
txtcovers.ReadOnly = true;
txtwaiter.Text = lblwaiter.Text;
txtwaiter.ReadOnly = true;
Label3.Text = lblbill.Text;
Label17.Text = lbld.Text;
Label7.Text = lblt.Text;

Label29.Visible = false;
txttendamt.Visible = false;
Label31.Visible = false;
lblbalance.Visible = false;

// Original Query
// MySqlCommand cmd = new MySqlCommand("select distinct(bt.no), bd.prod_name, bd.qty, bd.prod_price, bt.qty as qtybill, bt.bill_amt, bt.bill_vat, bt.bill_service_tax, bt.bill_service_charge, bt.bill_total from bill_totals bt left join bill_details bd on bt.no = bd.bill_no where bt.no = '" + lblbill.Text + "'", con);

// MySqlCommand cmd = new MySqlCommand("select * from bill_totals where no = '" + lblbill.Text + "'", con);

dr.Read();
for (int i = 0; i < Gridview1.Rows.Count; i++)
// foreach (GridViewRow row in Gridview1.Rows)
{
((TextBox)Gridview1.Rows[i].FindControl("TextBox1")).Text = dr[0].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox2")).Text = dr[1].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox3")).Text = dr[2].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox4")).Text = dr[3].ToString();
/* ((TextBox)row.FindControl("TextBox2")).Text = dr[1].ToString();
((TextBox)row.FindControl("TextBox3")).Text = dr[2].ToString();
((TextBox)row.FindControl("TextBox4")).Text = dr[3].ToString(); */
}
if (dr.HasRows)
{
//foreach (GridViewRow row in Gridview1.Rows)
//{
// ((TextBox)row.FindControl("TextBox1")).Text = dr[0].ToString();
// ((TextBox)row.FindControl("TextBox2")).Text = dr[1].ToString();
// ((TextBox)row.FindControl("TextBox3")).Text = dr[2].ToString();
// ((TextBox)row.FindControl("TextBox4")).Text = dr[3].ToString();
//}

//Gridview1.DataSource = dr;
//Gridview1.DataBind();
txthide.Text = dr[4].ToString();
Label10.Text = dr[4].ToString();
lblamt.Text = dr[5].ToString();
lblvat.Text = dr[6].ToString();
Label26.Text = dr[7].ToString();
Label25.Text = dr[8].ToString();
lbltotamt.Text = dr[9].ToString();

Desgin Code Here:-

<asp:gridview ID="Gridview1" runat="server"
AutoGenerateColumns="False" Width="175px" style="font-weight: 700"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" onselectedindexchanged="Gridview1_SelectedIndexChanged">
<columns>
<asp:BoundField DataField="RowNumber" HeaderText="Row Number" Visible="false"
ItemStyle-Font-Size="15px" >
<itemstyle font-size="15px">

<asp:TemplateField HeaderText="Item Name" HeaderStyle-Font-Size="Smaller">
<itemtemplate>
<asp:TextBox ID="TextBox1" runat="server" Width="75" BorderWidth="0">

<HeaderStyle Font-Size="Small" HorizontalAlign="Left" />

<asp:TemplateField HeaderText="QTY" HeaderStyle-Font-Size="Smaller">
<itemtemplate>
<asp:TextBox ID="TextBox2" CssClass="textalign" runat="server" Width="60" BorderWidth="0" >

<HeaderStyle HorizontalAlign="Center"/>

<asp:TemplateField HeaderText="RATE" HeaderStyle-Font-Size="Smaller">
<itemtemplate>
<asp:TextBox ID="TextBox3" CssClass="textalign" runat="server" Width="60" AutoPostBack="true" BorderWidth="0" OnTextChanged="Update_Click">

<HeaderStyle HorizontalAlign="Left"/>

<asp:TemplateField HeaderText="AMOUNT" HeaderStyle-Font-Size="Smaller">
<itemtemplate>
<asp:TextBox ID="TextBox4" CssClass="textalign" runat="server" Width="60" BorderWidth="0">

<%--<HeaderStyle Font-Size="Smaller" />
<footerstyle horizontalalign="Left">
<footertemplate>
<asp:Button ID="ButtonAdd" runat="server" Text="Add New Item" OnClick="Update_Click" Width="90" />

<HeaderStyle HorizontalAlign="Left" />--%>

<HeaderStyle Font-Size="Smaller" />



<footerstyle backcolor="#99CCCC" forecolor="#003399">
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<rowstyle backcolor="White" forecolor="#003399">
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<sortedascendingcellstyle backcolor="#EDF6F6">
<sortedascendingheaderstyle backcolor="#0D4AC4">
<sorteddescendingcellstyle backcolor="#D6DFDF">
<sorteddescendingheaderstyle backcolor="#002876">

protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)
{
// int RowIndex = 0;

con.Open();


DataSet ds = new DataSet();

if (e.CommandName.Equals("Select"))
{
GridViewRow clickedRow = ((LinkButton)e.CommandSource).NamingContainer as GridViewRow;
Label lblbill = (Label)clickedRow.FindControl("lblno");
Label lbld = (Label)clickedRow.FindControl("lbldate");
Label lblt = (Label)clickedRow.FindControl("lbltime");
Label lblwaiter = (Label)clickedRow.FindControl("lblwaiterno");
Label lblcover = (Label)clickedRow.FindControl("lblcovers");

MySqlCommand cmd = new MySqlCommand("select bd.prod_name, bd.qty, bd.prod_rate, bd.prod_price, bt.qty as qtybill, bt.bill_amt, bt.bill_vat, bt.bill_service_tax, bt.bill_service_charge, bt.bill_total from bill_totals bt left join bill_details bd on bt.bill_no = bd.bill_no where bd.bill_no = '" + lblbill.Text + "'", con);
MySqlDataReader dr = cmd.ExecuteReader();

txtcovers.Text = lblcover.Text;
txtcovers.ReadOnly = true;
txtwaiter.Text = lblwaiter.Text;
txtwaiter.ReadOnly = true;
Label3.Text = lblbill.Text;
Label17.Text = lbld.Text;
Label7.Text = lblt.Text;

Label29.Visible = false;
txttendamt.Visible = false;
Label31.Visible = false;
lblbalance.Visible = false;

// Original Query
// MySqlCommand cmd = new MySqlCommand("select distinct(bt.no), bd.prod_name, bd.qty, bd.prod_price, bt.qty as qtybill, bt.bill_amt, bt.bill_vat, bt.bill_service_tax, bt.bill_service_charge, bt.bill_total from bill_totals bt left join bill_details bd on bt.no = bd.bill_no where bt.no = '" + lblbill.Text + "'", con);

// MySqlCommand cmd = new MySqlCommand("select * from bill_totals where no = '" + lblbill.Text + "'", con);

dr.Read();
for (int i = 0; i < Gridview1.Rows.Count; i++)
// foreach (GridViewRow row in Gridview1.Rows)
{
((TextBox)Gridview1.Rows[i].FindControl("TextBox1")).Text = dr[0].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox2")).Text = dr[1].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox3")).Text = dr[2].ToString();
((TextBox)Gridview1.Rows[i].FindControl("TextBox4")).Text = dr[3].ToString();
/* ((TextBox)row.FindControl("TextBox2")).Text = dr[1].ToString();
((TextBox)row.FindControl("TextBox3")).Text = dr[2].ToString();
((TextBox)row.FindControl("TextBox4")).Text = dr[3].ToString(); */
}
if (dr.HasRows)
{
//foreach (GridViewRow row in Gridview1.Rows)
//{
// ((TextBox)row.FindControl("TextBox1")).Text = dr[0].ToString();
// ((TextBox)row.FindControl("TextBox2")).Text = dr[1].ToString();
// ((TextBox)row.FindControl("TextBox3")).Text = dr[2].ToString();
// ((TextBox)row.FindControl("TextBox4")).Text = dr[3].ToString();
//}

//Gridview1.DataSource = dr;
//Gridview1.DataBind();
txthide.Text = dr[4].ToString();
Label10.Text = dr[4].ToString();
lblamt.Text = dr[5].ToString();
lblvat.Text = dr[6].ToString();
Label26.Text = dr[7].ToString();
Label25.Text = dr[8].ToString();
lbltotamt.Text = dr[9].ToString();