3

I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per event or category of event per record in the output CSV.

The database structure requires that I use nested select statements to get the necessary data into columns for each record--each column containing a quantity of tickets for that type of event. I know how to do this in theory, but each embedded select statement is so large that the full report would blow through the 8,000 character limit on my DB interface. One of these statements looks like this:

(select count (*)
FROM
guest ig, event2 e, eventseat es, "order" o
WHERE ig.guestid = g.guestid
and ig.guestid = o.guestid
and o.orderid = es.orderid
and e.eventid = es.eventid
and e.incometype = 'T'
and e.eventtype in ('SS', 'BMF')
and es.status in ('2','4')
and es.price <> '0.00'
and es.price <> '5.00'
and e.year = '2010'       <-- THESE LINES ARE THE ONLY ONES THAT 
and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT 
) as 'SS-Dance',

That's about 400 characters, which only leaves enough room for <20 iterations in a single query without going over the character limit.

Is there a way to reduce this or generalize it so that I don't need to waste all those characters on duplicated information in each SELECT?

I've put together an example at the following link which contains a simplified version of my DB structure and some sample data. Objective is to get the same result data without using so many characters per nested SELECT.

http://sqlfiddle.com/#!3/fc316/2

1
  • 4
    My first suggestion: please stop using old-style joins. Explicit INNER JOIN syntax is going to be much easier for most of your audience to read and parse, and much less prone for you to inadvertently derive the Cartesian product. Also can you explain where g. refers to? Jun 22, 2012 at 22:14

1 Answer 1

7
;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

  • try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
  • always use dbo. (or whatever schema prefix is appropriate).
  • as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.
3
  • Ooh, that's definitely worth the accept. Unfortunately I can't change the database schema, but I had been wondering about your use of brackets instead of double quotes. This gives me a lot of new stuff to figure out; thanks.
    – NReilingh
    Jun 23, 2012 at 0:16
  • Is this query, to your knowledge, compatible with SQL Server 2005? My production environment gives me Line 21: Incorrect syntax near '('. when I try to run it verbatim.
    – NReilingh
    Jul 8, 2012 at 23:40
  • @NReilingh your database needs to be in 90 compatibility level or higher. If you're running 2005, you must currently have compatibility level set to 80 if you are getting this error. If this is not intentional, ALTER DATABASE your_db_name SET COMPATIBILITY_LEVEL = 90. Or check that you really are running SQL Server 2005. What does SELECT @@VERSION; yield? Jul 9, 2012 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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