How to compare values from datatable

Question

0.00/5 (No votes)

See more:

Hi my code is

VB

If LineNo = Family Then
                        If FailCount >= 500 Then 'This is for the sum of that line 
                            'Get the highest percentage by taking Count(*) / total count * 100
                            For Each Row As DataRow In tblRawTesterShutdown.Rows
                                With objShutdownReport
                                    .ShutdownType = _ShutdownType
                                    .TesterID = LineNo
                                    .QuadID = "0"
                                    .E_Code = Row.Item("E_FIELD").ToString.Trim
                                    .F_Code = Row("F_FIELD").ToString.Trim
                                    .G_Code = Row("G_FIELD").ToString.Trim
                                    .Status = Row("HSA_STATUS").ToString.Trim
                                    .Family = Row("FAMILY").ToString.Trim
                                    .Count = CInt(Row.Item("TOTAL"))
                                    .StartTime = StartTime
                                    .Timestamp = NowTime
                                    .CACHE_FAMILY = Row.Item("CACHE_FAMILY").ToString.Trim

                                    If Percentage =  Then 
'<--- At this part it will get percentage value actually, so here it should find
'the top value and minus the second value. Anywan can help how to do the looping
'here. sample data is shown below.
                                       
                                    Else
                                        
                                    End If
                                End With
                            Next
                        Else
                            MsgBox("No shutdown")
                        End If
                    End If 'end here

This is my code. I need an help actually to get the top percentage from particular row number and minus the second top of percentage. I quite confuse with the logic. So for an example the first percentage is 0.006 should minus with 0.004 since it should ignore 0.001. With the difference then it should minus again with 0.004 to find the difference. Pls help me. Thanks in advance.

LINE_NO E_FIELD F_FIELD G_FIELD FAMILY HSA_STATUS CACHE_FAMILYCountOfLineNo TotalOfLineNo %	
23059B	00353	00418	00684	SUMMIT	   S	   WER	              3       526     0.006
64006M	00353	00418	00684	SUMMIT	   R	   WER	              2	      2838    0.001
23059B	00353	00418	00758	SUMMIT	   S	   WER	              2	      526     0.004

[edit]Code block added, Spurious bold removed - OriginalGriff[/edit]

Posted 25-Mar-13 21:24pm

kumar2413

Updated 25-Mar-13 22:05pm

OriginalGriff

v2

Add a Solution

Comments

Maciej Los 26-Mar-13 16:53pm

Sorry, but your question is unclear. Please, be more specific and provide more data. If you can't explain it a bit better, show us expected output.

kumar2413 26-Mar-13 20:29pm

Actually the above code is to retrieve data from database and then stored it into a datatable. After it is stored, the data should be compared by using the percentage. First, from the entire data it should get the highest percentage and then minus the second highest. So i'm confuse how to get the highest mean here is max percentage from the datatable. As an example, you can see the data shows the percentage 0.006 assume that is the highest percentage so it should minus the second highest 0.004. This is the logic actually. Thanks

1 solution

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)

Maciej Los · Answer 1 · 2013-03-26T23:00:00

I'm little confused... As i good remember[^] you are using MS SQL server to store data.
Comparing data on server side is much faster than comparing data on client side.

Have a look at below example:

SQL

DECLARE @tbl TABLE(LINE_NO VARCHAR(10), TotalOfLineNo INT, Percentage REAL)

INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('23059B', 526, 0.006)
INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('64006M', 2838, 0.001)
INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('23059B', 526, 0.004)

SELECT t1.LINE_NO, t1.TotalOfLineNo, COALESCE(t1.P1,0) AS P1, COALESCE(t2.P2,0) AS P2, COALESCE(CONVERT(REAL, t1.P1 - t2.P2),0) AS PDiff
FROM (
	SELECT LINE_NO, TotalOfLineNo, Percentage AS P1, ROW_NUMBER() OVER (ORDER BY Percentage DESC) + 1 AS RowNo
	FROM @tbl) AS t1 LEFT JOIN (
			SELECT LINE_NO, TotalOfLineNo, Percentage AS P2, ROW_NUMBER() OVER (ORDER BY Percentage DESC) AS RowNo
			FROM @tbl) AS t2 ON t1.RowNo = t2.RowNo

result:

LINE_NO Total.. P1      P2      PDiff
23059B	526	0,006	0,004	0,002
23059B	526	0,004	0,001	0,003
64006M	2838	0,001	0	0

Is that what you want to achieve?