Sql-server – Get ticket sales and reservations grouped by time slot

sql server

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

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

Related Solutions

Sql-server – Storing date increments in tables

You can create a time table which stores time in minutes. Then have a column as the foreign key to the record which has got half an hour difference to it. If you need to get half an hour before a time slot and half an hour after it, you will have 2 columns. One referring to the record which has got the half an hour prior value and another has got the half an hour after value. In means the foreign key referenced to the table itself. The table definition would be something like below:

Create Table TimeTbl

(

Id int not null primary key,

TimeValue time not null,

HalfHrBeforeId int null,

HalfHrAfterId int null

)

HalfHrAfter Foreign Key referenced TimeTbl(Id)

HalfHrBeforeId Foreign Key referenced TimeTbl(Id)

In this table you will have:

1, 00:00:00, null, 30

2, 00:01:00, null, 31

...

30, 00:30:00, 1, 60

31, 00:31:00, 2, 61

...

60,01:00:00, 30, 90

...

So if you query this table each includes all time records of a day in minute, you will have foreign key to half an hour before and after and can get and compare their values, to choose the minimum one(the closest half an hour increment).

Sql-server – How to write windowing query which sums a column to create discrete buckets

I am not sure what type of performance you are looking for, but if CLR or external app is not an option, a cursor is all that is left. On my aged laptop I get through 1,000,000 rows in about 100 seconds using the following solution. The nice thing about it is that it scales linearly, so I would be looking at a little about 20 minutes to run through the entire thing. With a decent server you will be faster, but not an order of magnitude, so it would still take several minutes to complete this. If this is a one off process, you probably can afford the slowness. If you need to run this as a report or similar regularly, you might want to store the values in the same table un update them as new rows get added, e.g. in a trigger.

Anyway, here is the code:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable(
 Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3) DEFAULT ABS(CHECKSUM(NEWID())%100)/100.0
);


MERGE dbo.MyTable T
USING (SELECT TOP(1000000) 1 X FROM sys.system_internals_partition_columns A,sys.system_internals_partition_columns B,sys.system_internals_partition_columns C,sys.system_internals_partition_columns D)X
ON(1=0)
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;

--SELECT * FROM dbo.MyTable

DECLARE @st DATETIME2 = SYSUTCDATETIME();
DECLARE cur CURSOR FAST_FORWARD FOR
  SELECT Id,v FROM dbo.MyTable
  ORDER BY Id;

DECLARE @id INT;
DECLARE @v NUMERIC(5,3);
DECLARE @running_total NUMERIC(6,3) = 0;
DECLARE @bucket INT = 1;

CREATE TABLE #t(
 id INT PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3),
 bucket INT,
 running_total NUMERIC(6,3)
);

OPEN cur;
WHILE(1=1)
BEGIN
  FETCH NEXT FROM cur INTO @id,@v;
  IF(@@FETCH_STATUS <> 0) BREAK;
  IF(@running_total + @v > 1)
  BEGIN
    SET @running_total = 0;
    SET @bucket += 1;
  END;
  SET @running_total += @v;
  INSERT INTO #t(id,v,bucket,running_total)
  VALUES(@id,@v,@bucket, @running_total);
END;
CLOSE cur;
DEALLOCATE cur;
SELECT DATEDIFF(SECOND,@st,SYSUTCDATETIME());
SELECT * FROM #t;

GO 
DROP TABLE #t;

It drops and recreates the table MyTable, fills it with 1000000 rows and then goes to work.

The cursor copies each row into a temp table while running the calculations. At the end the select returns the calculated results. You might be a little faster if you don't copy the data around but do an in-place update instead.

If you have an option to upgrade to SQL 2012 you can look at the new window-spool supported moving window aggregates, that should give you better performance.

On a side note, if you have an assembly installed with permission_set=safe, you can do more bad stuff to a server with standard T-SQL than with the assembly, so I would keep working on removing that barrier - You have a good use case here where CLR really would help you.

Best Answer

Related Solutions

Sql-server – Storing date increments in tables

Sql-server – How to write windowing query which sums a column to create discrete buckets

Related Question