How to count the row's values

Israel

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Israel11y ago
@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.
0
Sekhar Babu11y ago
replace the objects according to your db,tablename, columns. It will work.
0
Israel11y ago
@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'
0
Sekhar Babu11y ago
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

0
Hemlata Kushwaha11y ago
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 < dtFee.Rows.Count;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.
0

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

1	A	BA
2	B	BCOM
3	C	MA