Get ticket sales and reservations grouped by time slot

Question

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.

Answer 1

For the first part of your query and all those hard-coded CASE expressions, there is a much better way to do that:

;WITH n(i) AS 
(
  SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects
),
d(lngSlot, [Time of Session]) AS 
(
  SELECT i, RIGHT(CONVERT(CHAR(19), DATEADD(MINUTE, 20*i, '10:50 AM'), 0),7) 
  FROM n
)
SELECT lngSlot, [Time of Session]
FROM d
ORDER BY lngSlot;

Partial results:

lngSlot    Time of Session
-------    ---------------
1          11:10AM
2          11:30AM
3          11:50AM
4          12:10PM
5          12:30PM
6          12:50PM
7           1:10PM
...        
34         10:10PM
35         10:30PM
36         10:50PM
37         11:10PM
38         11:30PM
39         11:50PM
40         12:10AM

More examples of generating sets without loops or tedious CASE expressions:

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3

So knowing that, and making some assumptions about the raw data that fed into your desired output, perhaps the query you are looking for is:

;WITH n(i) AS 
(
  SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects
),
d(lngSlot, [Time of Session]) AS 
(
  SELECT i, RIGHT(CONVERT(CHAR(19), DATEADD(MINUTE, 20*i, '10:50 AM'), 0),7) 
  FROM n
)
SELECT 
  Mission    = t.strSessionName,
  d.[Time of Session],
  [Total]    = SUM(t.lngSold),
  [Reserved] = COALESCE(SUM(r.lngQuantity),0),
  Tickets    = SUM(t.lngSold) - COALESCE(SUM(r.lngQuantity),0) 
FROM d
INNER JOIN dbo.tblCalendarTemp AS t
ON d.lngSlot = t.lngSlot
LEFT OUTER JOIN dbo.tblItemReserve AS r
ON t.dateStart = r.dateCalendar
AND t.lngMerchItemID = r.lngMerchItemID
WHERE t.lngMerchItemID = 1
GROUP BY t.strSessionName, d.[Time of Session];

It's very important to include sample data in your question, otherwise we have no clue how you arrived at your "answer"...