Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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