Select value between two range from column in SQL Server

makumbi
 
on Jul 15, 2022 11:14 PM
729 Views

I have this code. I want to use it on my rdlc report.

here is the table structure

r1r2r3r4r5r6r7r8r9r10r11r12r13r14r15r16r17r18r19postingtimepostinguser
0 39 40 44 45 49 50 54 55 59 60 69 70 79 80 89 90 0      

How can i call it.

Please help

Public Function gradingE2(kk) As String
    If String.IsNullOrEmpty(kk) Then
        gradingE2 = ""
        'English
        Exit Function
    End If
    If (k1.Text <= kk And kk <= k2.Text) Then
        gradingE2 = "F9"
    Else
        If (k3.Text <= kk And kk <= k4.Text) Then
            gradingE2 = "P8"
        Else
            If (k5.Text <= kk And kk <= k6.Text) Then
                gradingE2 = "P7"
            Else
                If (k7.Text <= kk And kk <= k8.Text) Then
                    gradingE2 = "C6"
                Else
                    If (k9.Text <= kk And kk <= k10.Text) Then
                        gradingE2 = "C5"
                    Else
                        If (k11.Text <= kk And kk <= k12.Text) Then
                            gradingE2 = "C4"
                        Else
                            If (k13.Text <= kk And kk <= k14.Text) Then
                                gradingE2 = "C3"
                            Else
                                If (k15.Text <= kk And kk <= k16.Text) Then
                                    gradingE2 = "D2"
                                Else
                                    If (k17.Text <= kk And kk <= 100) Then
                                        gradingE2 = "D1"
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If 
End Function

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Jul 23, 2022 05:02 AM

Hi makumbi,

You need to use CASE statement to verify the specified value with in which range. Then according to the case the grade value will be returned.

You can create a Procedure of the query and use the returned value to display the Grading inside the report.

SQL

DECLARE @Test AS TABLE(r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,r6 INT,r7 INT,r8 INT,r9 INT,r10 INT,r11 INT,r12 INT,r13 INT,r14 INT,r15 INT,r16 INT,r17 INT,r18 INT)
INSERT INTO @Test VALUES(0,39,40,44,45,49,50,54,55,59,60,69,70,79,80,89,90,100)

DECLARE @Value INT = 61
SELECT CASE
	WHEN @Value >= r1 AND @Value <= r2 THEN 'F9'
	WHEN @Value >= r3 AND @Value <= r4 THEN 'P8'
	WHEN @Value >= r5 AND @Value <= r6 THEN 'P7'
	WHEN @Value >= r7 AND @Value <= r8 THEN 'C6'
	WHEN @Value >= r9 AND @Value <= r10 THEN 'C5'
	WHEN @Value >= r11 AND @Value <= r12 THEN 'C4'
	WHEN @Value >= r13 AND @Value <= r14 THEN 'C3'
	WHEN @Value >= r15 AND @Value <= r16 THEN 'D2'
	WHEN @Value >= r17 AND @Value <= r18 THEN 'D1'
	END AS 'Grade'
FROM @Test

Output

Grade

C4