CODEWITHSUNDEEP
asp.netsql-server
Kenny
Kenny

Reputation: 1054

How do I show only one record - the record with smallest value?

I hope the title of this thread makes sense.

Please take a look at the screenshot below.

enter image description here

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

By default, there is a 45 seat capacity

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>

Upvotes: 2

Views: 175

Answers (2)

mortb
mortb

Reputation: 9859

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

Upvotes: 1

000
000

Reputation: 27247

When a new user signs up for the course, you need to 

UPDATE table_name
SET AvailableSeats = ...
WHERE TrainingID=... AND CourseId=...

rather than doing an INSERT.

There should only be one row with a unique (DateId, LocationId, CourseId) set of values.

Upvotes: 1

Related Questions