Is there a function that I can use in MSSQL that will make one of my fields have a running total as it's value?

I have a database for a 'ClassSignUp'.  One of the fields is the 'TotalSeats' in each class.  
I have another table of 'Attendees' . This table contains a foreign key field of the 'ClassSignUp'.  

I want each row in the 'ClassSignUp' to show the 'TotalSeats' as entered and I also want to calculate the 'RemainingSeats' to show in my grid or table.  

For example, if the 'ClassSignUp' table has a 'class' that has been assigned 30 'TotalSeats', I want to also look into the 'Attendees' table and do a count('EventID') and subtract that number from the 'TotalSeats' column to show the 'RemainingSeats' for row table.

Thanks for any help!
Who is Participating?
Can you do it...yes.

Is it easy to do or fast?  No.

Here is an article I wrote a long time ago as to how you can do it with a user defined function...
Chap's right.  You'll have to write your own function for this and add it as a computed column.
CREATE FUNCTION dbo.GetRemainingSeats
	@EventID		INT,
	@TotalSeats		INT
  DECLARE @RemainingSeats INT
  SELECT @RemainingSeats = @TotalSeats - COUNT(EventID) FROM dbo.Attendees WHERE EventID = @EventID
  RETURN @RemainingSeats
ALTER TABLE dbo.ClassSignUp
  ADD RemainingSeats AS dbo.GetRemainingSeats(EventID, TotalSeats)

Open in new window

I think it can be done without a function.
Assume yoy have a certain ordering in your attendee table (example on attendeeid)
and and eventtable with an event and a totalseats table.

DECLARE @EventId Int
SELECT @EventId = 1
SELECT Att.AttendeeId, EventTable.TotalSeats,  
   EventTable.TotalSeats - ( SELECT count(EventId)
			  FROM Attendees Att2 
			  WHERE EventId = @EventId
			  AND Att2.AttendeeId <= Att.AttendeeId) 
                             as RunningTotal
FROM Attendees Att
JOIN EventTable ON Eventable.EventId = @EventId
ORDER BY Att.AttendeeId

Open in new window

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.