Calculating Remaining Places on a Event

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating Remaining Places on a Event

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-06-28 : 04:27:01

I have a tblEventsBookings

with columns such as

event ID - quantity

and a tblEvents with columns such as

id - maxNumberPlaces

Id like to create a stored procedure where I add up all the bookings for a particular event and then subract this from the maxnumber of places of the event all in 1 go so my stored procedure just return the remaining places figure.

If you guys could help me would be great

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-28 : 04:48:39

[code]
select e.ID, e.maxNumberPlaces, Remaining = e.maxNumberPlaces - b.qty
from tblEvents e
inner join
(
select eventID, qty = sum(quantity)
from tblEventsBookings
group by eventID
) b on e.ID = b.eventID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-06-28 : 05:01:45

Cool that works pretty good...one small change if possible..

If there is no bookings that it is also returned in this query and i suppose that then the remaining places should be same as maxnumplaces

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-06-28 : 05:27:42

I ended up using
SELECT tblEvents.id, tblEvents.crmId, tblEvents.eventName, tblEvents.eventTypeId, tblEvents.maxNumAttendees, tblEvents.eventDate,
tblEvents.attendeeRate, tblEvents.eventStatus, tblEvents.promotionalCode, tblEvents.description,
tblEvents.numPeoplePerTable, tblEvents.sponsorCompany, tblEvents.internationalEvent, tblEvents.comments, tblEvents.venue,
tblEvents.cancellationPolicy, tblEvents.eventManager, tblEvents.itemCode, tblEvents.tableItemCode, tblEvents.paymentType,
tblEvents.prefferedCategory, tblEvents.dateUpdated, tblEvents.sageDateModified, tblEvents_Categories.id AS Expr1, tblEvents_Categories.category,
DAY(tblEvents.eventDate) AS eventDay, LEFT(DATENAME(month, tblEvents.eventDate), 3) AS eventMonth, YEAR(tblEvents.eventDate) AS eventYear,
tblEvents_Categories.category AS Expr2, tblProducts.unitPrice AS seatRate,case when tblEvents.maxNumAttendees - b.qty >= 0
then tblEvents.maxNumAttendees - b.qty
else tblEvents.maxNumAttendees end as remainingPlaces
FROM tblEvents INNER JOIN
tblEvents_Categories ON tblEvents.eventTypeId = tblEvents_Categories.id INNER JOIN
tblProducts ON tblEvents.itemCode = tblProducts.itemCode
LEFT JOIN
(SELECT eventId, SUM(quantity) AS qty
FROM tblEventAttendees
GROUP BY eventId) AS b ON tblEvents.id = b.eventId
WHERE (CONVERT(DATETIME, eventDate, 103) > CONVERT(DATETIME, GETDATE() - 1, 103)) AND (eventStatus = 1)
ORDER BY eventDate

Any issues with that ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources