How to count the row's values

Question

Hi!

I have this simple table generated in a datagrid, a label where the default amount is 1.029. What I need to learn?

For each row there is a specific operation. For example, the official fees amount is 1.029. Then Fred doesnt paid for July and Agust. Then its should pay two months (1.029 x 2 = 2.058 ). Brune should pay only one month: Agust (1.029). But Shandi is three months: June, July, August (1.029 x 3 = 3.087).

How can I in a simple way count the month(s) is (or are) on each row as we can see on this table?

Name Month Amount

-------------|-------------------------|--------------

Fred July, Agust 1.029

-------------|-------------------------|--------------

Brune Agust 1.029

-------------|-------------------------|--------------

Shandi June, July, Agust 1.029

-------------|-------------------------|--------------

In another way, any month inserted in each row is considered as "1". If we add another month on the same row is "1" + "1" = 2.

Israel · Answer

@Sekhar I make the table as you do EXACTLY. Its works when I do this in my queries: //work well select * months*amount as 'amounttobepaid' from temptbl What happen? its shows all operation made perfectly. But in one condition: ONLY when I fill the months column with numbers of months. When I dont fill numbers in its shows 0. This normal! But when I insert on top of this that line I mentioned its doesnt shows the right result as you posted (just only the same error message appear): //Dont work with temptbl as (SELECT [Name][month]len(month)-len(REPLACE(month''''))+1 as months [amount] FROM [CS9DB1].[dbo].[rowsvalues]) Please see well the line.

Sekhar Babu · Answer

replace the objects according to your dbtablename columns. It will work.

Israel · Answer

@Sekhar Thanks. But this is the error message I received when I run my Sql queries: SQL Execution Error. Executed SQL statement WITHtemptbl As (SELECT Name month LEN(month)-LEN(REPLACE)month'''')) + 1 AS months amount FROM CS9DB1.dbo.rowsvalues) SELECT Name month months amount months * amount AS 'amounttobepaid' FROM temptbl AS temptbl_1 Error Source: Net SqlClient DAta Provider Error Message: Invalid object name 'CS9DB1.dbo.rowsvalues'

Sekhar Babu · Answer

Hi try this. we have to get the months count based on comma seperation the use that count to multiply amount. with temptbl as (SELECT [Name] [month]len(month)-len(REPLACE(month''''))+1 as months [amount] FROM [CS9DB1].[dbo].[rowsvalues]) select * months*amount as 'amounttobepaid' from temptbl. Output like this

Hemlata Kushwaha · Answer

First Create Student Table:- ID Name Course 1 A BA 2 B BCOM 3 C MA Second Create Fee Details Table:- ID Student_id Month Fee Date 1 1 June 1029 2013-01-06 2 1 July 1029 2013-01-07 3 2 June 1029 2013-01-06 4 2 July 1029 2013-01-07 5 2 August 1029 2013-01-08 6 3 June 1029 2013-01-06 Design Grid View --------------------------------------------- After these Process to fill Data in Gridview ------------------------------------------------------------ protected void Page_Load(object sender EventArgs e) { GetFeeDetail(); } private void GetFeeDetail() { DataUtility du = new DataUtility(); DataTable dt = du.GetDataTable(select * from student); if (dt.Rows.Count > 0) { gridData.DataSource = dt; gridData.DataBind(); foreach (GridViewRow gr in gridData.Rows) { string id = gr.Cells[0].Text; Label lbmonth = (Label)gr.FindControl(Label1); Label lbfee = (Label)gr.FindControl(Label2); DataTable dtFee = du.GetDataTable(select * from feedetails where student_id=' + id + '); if (dtFee.Rows.Count > 0) { string month = ; double feeAmount = 0; for (int i = 0; i { month = month+dtFee.Rows[i][month].ToString()+; feeAmount = feeAmount + Convert.ToDouble(dtFee.Rows[i][Fee].ToString()); } lbmonth.Text = month; lbfee.Text = feeAmount.ToString(); } } } } If You agree with this solution please reply.