Solved

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?

Posted on 2008-06-23
5
172 Views
Last Modified: 2010-03-20
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!
0
Comment
Question by:ScottLakey
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 84 total points
ID: 21849476
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
 
LVL 11

Assisted Solution

by:aaronakin
aaronakin earned 83 total points
ID: 21849548
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
 
LVL 10

Assisted Solution

by:Marcjev
Marcjev earned 83 total points
ID: 21850467
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now