getting max value from a row in sql table

Question

0.00/5 (No votes)

See more:

Hi..

I have a table containing list of the employee salaries according to month wise (each employee data as a row as per the month and year wise)..

It is inserted from a excel sheet..

where the columns will be as empid,dept,desg,Jan-11,Feb-11.Mar-11

and so on it continues.. and I have that of 3 years data.. from that i want to know what is the max salary of an individual employee and know the date and hike salary if any hikes are there to that particular employee.

I want to retrieve the highest value of the salary of the employee as per the empid given by the user in the .aspx page..

If any hikes are present i want to retrieve that too..

how could i compare and get the max value of the salary using the code behind(C#) of .aspx page

Posted 6-Nov-13 19:01pm

Harsha24

Updated 6-Nov-13 19:42pm

v5

Add a Solution

Comments

Thanks7872 7-Nov-13 1:11am

id in the UI by the user What does this mean? To get an answer to your question,don't assume that we have very good guess. Explain the whole scenario clearly.

Harsha24 7-Nov-13 1:14am

sry..i did it in a hurry..it is updated now.. thanks for the info rohan

satyaanupama 7-Nov-13 1:19am

you want to know the code behind .aspx page or just want to know the query in sql databse... mention it clearlyy so we can easily ans ur que... thanking you..

Harsha24 7-Nov-13 1:20am

yup.. i need it from the code behind

2 solutions

Solution 2

Hey Harsha,
For simplicity i guess you have 2 tasks.
1. get max. salary for employee.
2. get any hikes in salary.

So, first create 1 temporary table for this task
SalHikes(Empid,Hike,Date)

Now use loop in code behind same as RK suggested.

Assuming there is only one record of any employee id

C#

var maxSal;
 maxSal=0;
 for (int j = 3; j < dt.Columns.Count ; j++)  {
   if (maxSal < dt.Rows[i][j])
   {
     //Insert hike in table
     ExecuteSQL("INSERT INTO SalHikes(Empid,Hike,Date) SELECT '"+ EmpId +"','"+ Convert.ToDecimal(dt.Rows[i][j].ToString()) - maxSal +"','"+ dt.Columns[j].ColumnName +"'")     
maxSal = Convert.ToDecimal(dt.Rows[i][j].ToString());
   }
}

Now maxSal will be the max. salary of employee, and temp. table SalHikes will store the hikes of employee.
display max. salary for the employee and use sql query to fetch salary hikes of employee and display on aspx page, i assume there can be many salary hikes thats why i have used temp. table for storing hikes, if there is only one hike then you can directly use a variable to store thr hike and hike date.

Regards
Rakshit

Posted 6-Nov-13 20:29pm

Rak_Avasthy.

Updated 6-Nov-13 21:50pm

v4

Comments

Harsha24 7-Nov-13 2:36am

my boss will insert the excel using an upload button from the .aspx page.. and now i want to fetch the records from the db as per the id given in the .aspx page

Rak_Avasthy. 7-Nov-13 3:50am

i have updated the answer kindly check

♥…ЯҠ…♥ 7-Nov-13 3:10am

What is the use of reading maximum salary and saving the same in another table?

Rak_Avasthy. 7-Nov-13 3:24am

so that max. salary corresponding to each employee can be obtained

♥…ЯҠ…♥ 7-Nov-13 3:30am

Before that we are getting all details for the particular employee id.. so i think there is no need to again insert the same in another table.

Rak_Avasthy. 7-Nov-13 3:39am

yes there is no particular need for keeping it in a table unless you need it later to display somewhere....and using temporary table is definitely a suitable option for me to keep that data

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

♥…ЯҠ…♥ · Accepted Answer · 2013-11-06T19:21:00

Solution 1

Hi Harsha,

After reading your actual requirements from comments,
I suggest some idea to do,

SQL

SELECT * FROM employeeTableName WHERE empID = 101;

Update it in datatable and do the below manipulation

empid,dept,desg,Jan-11,Feb-11.Mar-11 .....

Read the cell value(Jan-11,Feb-11.Mar-11 .....) from datatable and there you can find the max value
Here is the code snippet you can make use of

 private void button1_Click(object sender, EventArgs e)
        {
var maxSal = 0;
 //Sample Datatable         
DataTable dt=new DataTable();
        DataColumn dc=new DataColumn("Name",typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Desg", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("SAl1", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal2", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal3", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal4", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("sal5", typeof(string));
            dt.Columns.Add(dc);
            DataRow dr = dt.NewRow();
//Sample record that matches your record
            dr[0] = "RK";
            dr[1] = "Software engineer";
            dr[2] = "1000";
            dr[3] = "500";
            dr[4] = "2000";
            dr[5] = "9000";
            dr[6] = "6000";
            dt.Rows.Add(dr);

        for (int i = 0; i < dt.Rows.Count; i++)
            for (int j = 3; j < dt.Columns.Count; j++) //As per you comment, need to find max value from Jan-11(3rd column) to last column
            {
                //Now you need to store it in temp variable
                if (maxSal < Convert.ToInt32(dt.Rows[i][j].ToString()))
                {
                    maxSal = Convert.ToInt32(dt.Rows[i][j].ToString());
                }
            }
  MessageBox.Show("Maximum salary is " + maxSal);
        }

Now maxSal has maximum salary value.
Try this......
Hope this could help you a bit.

Regards,
RK

Posted 6-Nov-13 19:21pm

♥…ЯҠ…♥

Updated 7-Nov-13 1:57am

v13

Comments

Harsha24 7-Nov-13 1:30am

thanks for the reply prabakr.. but the thing is like i don't have a column called salary and all the data is saved as a row to the empid..

To clearly tel u.. it is inserted from a excel sheet..

where the columns will be as empid,dept,desg,Jan-11,Feb-11.Mar-11

and so on it continues.. an I have that of 3 years data.. from that i want to know what is the max salary of an individual employee and know the date and hike salary if any hikes are there to that particular employee..

♥…ЯҠ…♥ 7-Nov-13 1:33am

Bit complex.... but do-able..... how you importing data from excel into C#?
manipulating from excel itself or importing data to the application?

Harsha24 7-Nov-13 1:39am

I have created an .aspx page which is used to upload the excel file and submit it into the database..I have written the back end code to do so for the button click..Now the data is saving in sql table..but now how to get the values according to the requirement is where i am struck..

♥…ЯҠ…♥ 7-Nov-13 1:54am

Updated my answer.... try this and lemme know...... :)

Harsha24 7-Nov-13 2:37am

on the process.. will update u as soon as i am done..

♥…ЯҠ…♥ 7-Nov-13 2:52am

Okay buddy....

Harsha24 7-Nov-13 7:15am

it is showing error at comparing i.e., maxSal < dt.Rows[i][j] as invalid arguments

♥…ЯҠ…♥ 7-Nov-13 7:29am

try typecasting the row value like if (maxSal < Convert.ToInt32(dt.Rows[i][j].ToString()))

♥…ЯҠ…♥ 7-Nov-13 7:58am

Updated the solution, try to debug the solution and understand it.

Harsha24 7-Nov-13 23:53pm

ty rk..it works.. now how to do this with hikes..i want to carry hike date and hike salary to a new table and show it in a gridview in .aspx page

Rak_Avasthy. 8-Nov-13 1:31am

use
ExecuteSQL("INSERT INTO SalHikes(Empid,Hike,Date) SELECT '"+ EmpId +"','"+ Convert.ToDecimal(dt.Rows[i][j].ToString()) - maxSal +"','"+ dt.Columns[j].ColumnName +"'")
to insert the hike and date in a temporary table or permanent table and then
fetch this data in data table and bind to the grid.

Harsha24 8-Nov-13 1:34am

where should i use this??

Harsha24 8-Nov-13 1:50am

("INSERT INTO SalHikes(Empid,Hike,Date) values('"+ EmpId +"','"+ Convert.ToDecimal(dt.Rows[i][j].ToString()) - maxSal +"','"+ dt.Columns[j].ColumnName +"'")...

Getting error at converting process..

♥…ЯҠ…♥ 8-Nov-13 3:09am

In SalHikes table, what is Hike data type? according to that convert it.... then it might work

Harsha24 8-Nov-13 4:18am

got it bro..ty very much

♥…ЯҠ…♥ 8-Nov-13 5:11am

Very nice...!!!! I think, solution needs up vote. what do you think? ;-)

Harsha24 8-Nov-13 5:20am

yup bro.. i did it even before...

♥…ЯҠ…♥ 8-Nov-13 5:22am

:)