I hope the title of this thread makes sense.

Please take a look at the screenshot below.

Top half shows the db with two users who have signed up for training.

By default, there is a 45 seat capacity

• After userB signs up, there are 44 left.

• Then userA signs up, now there are 43 seats available.

So far so good.

But please take a look at the bottom half of that screenshot. Notice that the screen shows 43 Seats available as well as 44 seats available. This is very confusing to our users. How can we just show the least available number? In other words, after each sign up, display only the number representing the remaining seat. In the example above, it will be 43.

Next time another user signs up, the number decreases by 1 and becomes 42 and that's the number we would like to show per the location where the user signed up for that class and for the date of the class.

Below is the code that I am using.

How can I tweak it to show just the most current number which is the smallest of the number of availableSeats?

<asp:SqlDataSource ID="sqlDataSourceloc" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    SelectCommand="SELECT locationId, Location FROM tblLocations order by location asc"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
 SelectCommand="select DISTINCT l.LocationId,c.courseId, c.coursename, (case when isnull(t.availableSeats,30) = 0 then 'Class is full'
                else  str(isnull(t.availableSeats,30)) end) availableSeats,d.dateid,d.trainingDates, d.trainingtime, c.CourseDescription,i.instructorName, l.location,l.seating_capacity
                from tblLocations l
                Inner Join tblCourses c on l.locationId = c.locationId
                left join tblTrainings t on l.locationId = t.LocationId and c.courseId = t.courseId
                Inner Join tblTrainingDates d on c.dateid=d.dateid
                Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId
                Inner Join tblInstructors i on ic.instructorId = i.instructorId"
      FilterExpression="LocationId = '{0}'" >
<FilterParameters>
 <asp:ControlParameter ControlID="ddlLocation" Name="LocationId"
  PropertyName="SelectedValue" Type="Int32" />
 </FilterParameters>
</asp:SqlDataSource>

You could try a subquery to count the rows and subtract from the defualt Capacity_Seating :

select DISTINCT l.LocationId,
c.courseId,
c.coursename,
c.Capacity_Seating - (select count(*)
      from tblTrainings t1
      where l.locationId = t1.LocationId and c.courseId = t1.courseId) as
availableSeats,
d.dateid,
d.trainingDates,
d.trainingtime,
c.CourseDescription,
i.instructorName,
l.location,
l.seating_capacity
                    from tblLocations l
                    Inner Join tblCourses c on l.locationId = c.locationId
                    left join tblTrainings t on l.locationId = t.LocationId and c.courseId = t.courseId
                    Inner Join tblTrainingDates d on c.dateid=d.dateid
                    Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId
                    Inner Join tblInstructors i on ic.instructorId = i.instructorId