Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Query Results.

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 CalendarTemp. This is a picture of the relevant info in the ItemReserve table ItemReserve. 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.

share|improve this question
2  
First off, using "tblItemReserve.lngMerchItemID = 1" in the WHERE clause effectively changes your left join to an inner join. In a left join, if tblItemReserve has no match for the tblCalendarTemp table, then lngMerchItemID would be Null. Since null is not the same as 1, it's an inner join. –  Robert L Davis Feb 4 '13 at 17:40
2  
Ignore what you have posted: can you state what you are trying to accomplish in English terms? "I am attempting to something about planets" Can you provide sample data from your two tables? –  billinkc Feb 4 '13 at 17:41
    
It's hard to say without seeing the data. Perhaps you need to add criteria stating where tblItemReserve.lngQuantity > 0. –  Robert L Davis Feb 4 '13 at 17:45
    
My SQL server knowledge is crap, but surely you can divide a time into 20 minute groups without using a CASE statement? –  FreshPhilOfSO Feb 4 '13 at 18:50
1  
Here's what we need to properly help you. Go to sqlfiddle.com and put in script that creates all the relevant tables (you can just script out the tables you actually have in your own database), then put script that populates them with some sample data. Also put in the query you've tried that is returning the wrong result. Post the link to the sqlfiddle here. For that set of sample data, post an example resultset that you want to see, with explanation of any of it that you think will help. THEN we can help you quickly, and correctly! –  ErikE Feb 9 '13 at 3:27
show 2 more comments

1 Answer

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"...

share|improve this answer
    
Aaron, you are quite close to what I am looking for, can you look at my edits and see if that helps you at all? –  Kyle Supe Feb 5 '13 at 13:14
2  
@Kyle Can you explain how I am "quite close" instead of sending me on an expedition? Show me some rows, what my query produces, and what my query should produce. I'm not going to cycle through refactoring my query repeatedly trying to match your results if I don't have more information. –  Aaron Bertrand Feb 5 '13 at 13:50
    
sure thing, sorry to leave you in the dark, didn't have much time when replying earlier. Your Statement Results is what your statement produces. I didn't need to sum the lngsold column however, so I changed the statement slightly and basically got the same results I had earlier, which was summing the entire lngquantity column My Statement. Have you by chance seen my edits to my original post? I posted the relevant data from each table separately. Thanks for all your help in this and let me know what other info you need! –  Kyle Supe Feb 5 '13 at 20:36
    
@Kyle I did look at the screen shots of your sample data (which aren't entirely helpful). Unfortunately the sample data doesn't seem to line up with your expected results. Can you explain to me, given the data shown, how you arrived at Neptune/29/20/9? Or why your screen shots only contain Mars and Saturn, but neither of those missions are in your desired results? Or can you adjust your desired results so that they match the screen shots? I know it's easy for you to see what result you want, but do you see how hard it is for us to see it without complete information? –  Aaron Bertrand Feb 5 '13 at 21:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.