CODEWITHSUNDEEP
sql-serverfunctionaggregatewhere-clausehaving
user5989560
user5989560

Reputation:

SQL Server, Having Clause, Where, Aggregate Functions

In my problem which I am trying to solve, there is a performance values table:

Staff       PerformanceID   Date        Percentage
--------------------------------------------------
StaffName1  1               2/15/2016   95
StaffName1  2               2/15/2016   95
StaffName1  1               2/22/2016   100
...
StaffName2  1               2/15/2016   100
StaffName2  2               2/15/2016   100
StaffName2  1               2/22/2016   100

And the SQL statement as follows:

SELECT TOP (10)     
    tbl_Staff.StaffName, 
    ROUND(AVG(tbl_StaffPerformancesValues.Percentage), 0) AS AverageRating
FROM                
    tbl_Staff 
INNER JOIN
    tbl_AcademicTermsStaff ON tbl_Staff.StaffID = tbl_AcademicTermsStaff.StaffID 
INNER JOIN
    tbl_StaffPerformancesValues ON tbl_AcademicTermsStaff.StaffID = tbl_StaffPerformancesValues.StaffID
WHERE
    (tbl_StaffPerformancesValues.Date >= @DateFrom) 
    AND (tbl_AcademicTermsStaff.SchoolCode = @SchoolCode) 
    AND (tbl_AcademicTermsStaff.AcademicTermID = @AcademicTermID)
GROUP BY            
    tbl_Staff.StaffName
ORDER BY            
    AverageRating DESC, tbl_Staff.StaffName

What I am trying to do is, from a given date, for instance 02-22-2016, I want to calculate average performance for each staff member.

The code above gives me average without considering the date filter.

Thank you.

Upvotes: 1

Views: 68

Answers (2)

user5989560
user5989560

Reputation:

Thanks for the replies, the code above, as you all say and as it is also expected is correct.

I intended to have a date filter to see the results from the given date until now.

The code

WHERE tbl_StaffPerformancesValues.Date >= @DateFrom

is correct.

The mistake i found from my coding is, in another block i had the following:

Protected Sub TextBoxDateFrom_Text(sender As Object, e As System.EventArgs) Handles TextBoxDate.PreRender, TextBoxDate.TextChanged
        Try

            Dim strDate As String = Date.Parse(DatesOfWeekISO8601(2016, WeekOfYearISO8601(Date.Today))).AddDays(-7).ToString("dd/MM/yyyy")

            If Not IsPostBack Then
                TextBoxDate.Text = strDate
            End If

            SqlDataSourcePerformances.SelectParameters("DateFrom").DefaultValue = Date.Parse(TextBoxDate.Text, CultureInfo.CreateSpecificCulture("id-ID")).AddDays(-7)

            GridViewPerformances.DataBind()

        Catch ex As Exception

        End Try
End Sub

I, unintentionally, applied .AddDays(-7) twice. I just noticed it and removed the second .AddDays(-7) from my code.

SqlDataSourcePerformances.SelectParameters("DateFrom").DefaultValue = Date.Parse(TextBoxDate.Text, CultureInfo.CreateSpecificCulture("id-ID"))

Because of that mistake, the SQL code was getting the performance values 14 days before until now. So the average was wrong.

Thanks again.

Upvotes: 0

FallAndLearn
FallAndLearn

Reputation: 4135

Apart from your join conditions and table names which looks quite complex, One simple question, If you want the results for a particular date then why is the need of having

WHERE tbl_StaffPerformancesValues.Date >= @DateFrom

As you said your query is displaying average results but not for a date instance. Change the above line to WHERE tbl_StaffPerformancesValues.Date = @DateFrom.

Correct me if I am wrong.

Upvotes: 1

Related Questions