|
Hi,
I'm binding a sqldtatasource to a gridview to obtain some calculations from the data. However something is wrong for the query because I know the calculations are wrong. I hope I can explain clearly:
I have data in database as follows:
I have a table with time slots (Slots):
SlotID Slot
=================
1 9-9.30
2 9.30-10
3 10-10.30
4 10.30-11
I have a table with events (Events):
ID Name SlotID ExtraSlotID Limit1 Limit2
===========================================
1 Event1 1 n/a 20 20
2 Event2 1 n/a 20 20
3 Event3 2 n/a 40 40
4 Event4 2 3 20 20
5 Event5 3 4 40 40
6 Event6 3 4 20 20
7 Event7 3 4 10 10
I'm querying my database to get the total of people attending events in each time slot:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID
However, this is giving me wrong results (I need this table to give me sum of attendees for each slot as Main slot (PeopleLimit1) and as extra slot (PeopleLimit2) but it seems PeopleLimit2 is in a kind of loop and and sums twice when there are events in that slot but as main slot
SlotID PeopleLimit1 PeopleLimit2
================================
1 40 0
2 60 0
3 70 60 *wrong
4 0 70
If I use just main slot with this query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID
GROUP BY s.Slot, s.SlotID
I get:
SlotID PeopleLimit1
====================
1 40
2 60
3 70
4 0
If I use only the extra slot in my query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID
I get this:
SlotID PeopleLimit2
1 0
2 0
3 20 *correct
4 70
The problem is how to put the last two tables together and sum PeopleLimit1 and PeopleLimit2.
Thanks for your help!
| 
