Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
180 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
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 336 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 332 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 332 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 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