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!
ScottLakeyAsked:
Who is Participating?
 
chapmandewCommented:
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...

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/
0
 
aaronakinCommented:
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
)
RETURNS INT
AS
BEGIN
  DECLARE @RemainingSeats INT
  SELECT @RemainingSeats = @TotalSeats - COUNT(EventID) FROM dbo.Attendees WHERE EventID = @EventID
  RETURN @RemainingSeats
END
 
ALTER TABLE dbo.ClassSignUp
  ADD RemainingSeats AS dbo.GetRemainingSeats(EventID, TotalSeats)

Open in new window

0
 
MarcjevCommented:
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

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.