SQL Query - Please Help - TIA

Sample 1Sample 2
Hi everyone, I need help with this SQL query, First sample the count should be 2 from the first 3 rows, I want to count all the fails where by name and date like yyyy-MM-dd... Second picture should be count "1" because in the first three rows has a RESULT of "fail" how can I do that? Thanks for the help.

Im counting it like this:
Dim CHOS As String = "%"
        Dim cmd2a As MySqlCommand = conn.CreateCommand()
        cmd2a.CommandText = "SELECT COUNT(RESULT) FROM rsv.esd_reco WHERE NUMBER = '" & TextBox1.Text & "' AND RESULT = 'fail' AND DATE LIKE '" & Label2.Text & CHOS & "'"
        cmd2a.CommandType = CommandType.Text
        Dim count2 = cmd2a.ExecuteScalar()
        cmd2a.Clone()
        Label3.Text = count2
        conn.Close()

Open in new window

Rowel VirgoVisual Studio .NETAsked:
Who is Participating?
 
Rowel VirgoVisual Studio .NETAuthor Commented:
I can't do it with query, but I got a different way to do it,
after 3 fails of result,
failsI made a 3 labels that will catch the results of a user from datagridview with this code
Label16.Text = dghistory.Rows(0).Cells(5).Value
Label17.Text = dghistory.Rows(1).Cells(5).Value
Label18.Text = dghistory.Rows(2).Cells(5).Value

Open in new window

LabelsI hide it (visibility to false) and a condition if labels got 3 fails it will warn the user(s).
warning sampleThank you sir for all the response... I appreciate it. Have a good day everyone
0
 
ste5anSenior DeveloperCommented:
You normally never use LIKE on DATETIME values. Use equlity or BETWEEN.

And use parameterized queries.
2
 
Rowel VirgoVisual Studio .NETAuthor Commented:
I did that because I need a "fail" records within the current day so I did
DATE LIKE '2017-12-05%'

Open in new window

(or the current date from label.text which has a date and time)
DATE LIKE '" & Label2.text & CHOS "'       ' as DIM

Open in new window

so that the result will display or count the current date and not from other date(s) sir.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
Is this a date column or a text column?  Normally, you would cast the datetime value into a date (only) value for such comparisons.  I've also cast dates into long integers to just extract the date value.
0
 
Rowel VirgoVisual Studio .NETAuthor Commented:
Sorry, aam I hipe I make my self clear now. . I want to create a program that will warn user if they goy 3 consecutive fails and I dont have any Idea how to do that, if the records has fail fail fail it should warn the user but if records has fail fail pass it wont warn the user because it has already pass.. Theb after pass if they got three fails it will warn them. Msgbox "you got 3 consecutive fails"
0
 
aikimarkCommented:
You might also have a problem with the name of the column = "DATE"

Do you want this only for one date or all dates with three 'fail' values?
0
 
aikimarkCommented:
What is the type of database engine you're using?
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your grid only shows date with hour and minutes. How do you know which one happened first if you don't show the seconds?
And for your screenshots in both there are 3 fails so the count should be correct.
1
 
Pawan KumarDatabase ExpertCommented:
Your data looks incorrect to me.
One student at one given time can only be pass or fail. I cannot be pass/fail at the same time or fail/fail at the same time.

Also you do not have to pass date as a parameter if only last 3 entries per student needs to be checked. This parameter will help if you want to check pass/fail for a particular date only.

Also what if the student has given only 2 tests and failed in both.

Assuming you are using SQL Server please use below query. Also do not use inline query use stored procedure.

The below query will give you a column Warning and the value of the column will be Pass or Fail. If Fail then you need send warning to those students.

SELECT CASE WHEN CountFails <= 3 THEN 'Fail' ELSE 'Pass' END warning 
FROM 
(
	SELECT TOP 1 SUM( CASE WHEN Result = 'fail' THEN 1 ELSE 0 END ) OVER() CountFails  
	FROM 
	(
		SELECT * FROM
		(
			select *,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY DT DESC) rnk from testPassFail
			WHERE Name = 'k' /*Name of the student you are passing from the application*/
		)u WHERE rnk <= 3
	)r
)u 

Open in new window

1
 
Rowel VirgoVisual Studio .NETAuthor Commented:
What if lets disregards the date and time, How to detect if the user have a 3 consecutive fails in row in the program VB...

Thanks I tried the query

here's my database

DATABASE
0
 
Pawan KumarDatabase ExpertCommented:
Apart from Date column you do not have any other column which can give you latest 3 records you have to use it. Have you tried my last solution ?
1
 
Pawan KumarDatabase ExpertCommented:
great...
1
 
Rowel VirgoVisual Studio .NETAuthor Commented:
Thank you everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.