www.hkvforums.com

So basically I have a students table and a discipline table. Each row of the discipline table is a student id, date and columns we call "jugs" and "demerits". I'm trying to build a view that would allow a user to search by date any students that have a matching record in the discipline table for that date and give me the total demerits and jugs for ALL the dates for that student (GROUP BY student id). I cannot however do this because adding the date to the select statement breaks the group by. Is there a way to add the date search into my asp.net maker app by declaring variables? Something like this.

DECLARE @StartDate DATE
DECLARE @EndDate DATE

SELECT
    s.ID AS StudentID,
    s.FIRST_NAME,
    s.LAST_NAME,
    SUM(d.Demerits) AS TotalDemerits,
    SUM(d.Jugs) AS TotalJugs
FROM
    [dbo].[viewStudents] s
JOIN
    (
        SELECT *
        FROM [dbo].[Discipline]
        WHERE givenDate >= @StartDate
          AND givenDate <= @EndDate
    ) d ON s.ID = d.Student
GROUP BY
    s.ID, s.FIRST_NAME, s.LAST_NAME
ORDER BY
    s.LAST_NAME, s.FIRST_NAME;

Return the max and min date in the SQL so you can search between the dates. Something like:

SELECT
s.ID AS StudentID,
s.FIRST_NAME,
s.LAST_NAME,
SUM(d.Demerits) AS TotalDemerits,
SUM(d.Jugs) AS TotalJugs,
MAX(d.givenDate) AS MaxDate,
MIN(d.givenDate) AS MinDate
FROM
[dbo].[viewStudents] s
JOIN [dbo].[Discipline] d
ON s.ID = d.Student
GROUP BY
s.ID, s.FIRST_NAME, s.LAST_NAME
ORDER BY
s.LAST_NAME, s.FIRST_NAME;

I believe this will work, but what I want is the total records for all dates. In other words, if a student record exists for whatever date was searched, give me the students total records across all dates. Our registrar basically says "ok who got a demerit today, oh Billy did. Ok, how many total demerits does Billy have?"

The SQL merely returns the Max and Min date for your searching. It still returns the total records for all dates.