Ok, I am definitely no guru when it comes to SQL, I will preface this by saying that. Also, hopefully the info I post will be enough for those out there to figure out what I need to do. That being said here is my query:
USE GM001 SELECT tblCalendarTemp.strSessionName AS Mission, CASE tblcalendartemp.lngslot WHEN 1 THEN '11:10 AM' WHEN 2 THEN '11:30 AM' WHEN 3 THEN '11:50 AM' WHEN 4 THEN '12:10 PM' WHEN 5 THEN '12:30 PM' WHEN 6 THEN '12:50 PM' WHEN 7 THEN '1:10 PM' ... WHEN 34 THEN '10:10 PM' WHEN 35 THEN '10:30 PM' WHEN 36 THEN '10:50 PM' WHEN 37 THEN '11:10 PM' WHEN 38 THEN '11:30 PM' WHEN 39 THEN '11:50 PM' WHEN 40 THEN '12:10 AM' END AS [Time of Session], tblCalendarTemp.lngSold AS Total, COUNT(tblItemReserve.lngQuantity) AS Reserved, tblCalendarTemp.lngSold - COUNT(tblItemReserve.lngQuantity) AS Tickets FROM tblCalendarTemp LEFT OUTER JOIN tblItemReserve ON tblCalendarTemp.dateStart = tblItemReserve.dateCalendar WHERE ( tblCalendarTemp.lngMerchItemID = 1 ) AND ( tblItemReserve.lngMerchItemID = 1 ) GROUP BY tblCalendarTemp.lngSold, tblCalendarTemp.lngSlot, tblCalendarTemp.strSessionName
The Results I am getting are in this picture
Obviously what it is doing is counting the
lngQuantity column in its entirety. I have tried
sum instead of
count with similar results. I am ok with either being used in the final result. What I would like it to say is (using only a couple rows for examples):
Neptune : 1:10PM : 29 : 20 : 9 Pluto : 1:30PM : 13 : 13 : 0 Mercury : 1:50PM : 0 : 0 : 0 Venus : 2:10PM : 13 : 13 : 0
What am I missing here? Can anyone help me out? I will post more info if needed!
edit: ok here is some more information, hopefully this will help out. This is a picture of the CalendarTemp table . This is a picture of the relevant info in the ItemReserve table . The CalendarTemp table has how many tickets were sold for each laser tag mission for the current day and updates as people at pos ring guests in. The itemreserve table shows birthday and group events that have scheduled laser tag, of which is included in the calendartemp table. What I am trying to do is show in a program I have how many guests have tickets (people that walked up and purchased tickets), how many people in a laser tag game are with a group event, and the total (obviously being the number shown in the calendartemp table). Therefore I need to add up all the lngquantity column for each slot and match that to the other table lngslot. If any other info is needed please let me know.
Edit number 2:
Alright so here is the scoop on the raw data. The tblCalendarTemp table is a table that automatically changes every day. It has an lngSlot column that increments by 1. This column signifies a laser tag mission. The missions are in 20 min increments. I have lngMerchItemID = 1 because that is the laser tag mission, there are other lngMerchItemIDs that I am not tracking (party rooms, go kart races, etc). The lngSold column shows how many people are in each game and updates whenever a group is booked into a laser tag mission or someone purchases a ticket. the dateStart column indicates the day of the calendar.
The tblItemReserve table shows reserved spots (events booked into missions) in a specific laser tag mission. It has the lngSlot column that has the relevant slot same as tblCalendarTemp. its lngSlot does not increment however and the same number can repeat. The reason for that is the lngQuantity column. There is one row for each person in each event that is booked into a specific laser tag mission. For example, the Smith Party has 10 people so there will be 10 rows with the same lngSlot and lngQuantity will always read 1.00 for each row. dateCalendar should be the same as the dateStart in the other table. I need to add up all the lngQuantity for each lngSlot and match it up to the lngSlot in the other table.
I would like to have a resulting table with the first column being the strSession column named Mission Name. The second column should be the time increments using the lngSlot column as we have already nailed down, in lngSlot numerical order. The third column should be the total amount of people in a mission (the lngSold column, no mathematic functions should need to be done to it as it is automatically updated). The fourth column should be the sum of the lngQuantity's per each lngSlot (and only for lngMerchItemID = 1) number and be named Reserved. Finally, the fifth column should be Tickets Sold and should simply subtract the fourth column from the third column.
Hopefully this info, coupled with the screenshots below is explanatory enough that you guys can help me work this query out. My query was ever so close, but it was taking the sum of the entire lngQuantity column for lngMerchItemID = 1.