Skip to Content

I recently wrote a Crystal Report for a customer that queried their recurring bookings for gaps of more than 45 minutes. Easy enough, just sort the report by booking time and use Crystal’s previous() and next() functions to work out when the gap’s too large. A little conditional formatting to hide the rows without gaps, job done.

Two weeks later, the customer wants the report changed to show more information, and in a different order. More information, no problem. However, changing the sort order breaks the previous() and next() functionality.

I couldn’t write a stored procedure to run through the resultset as it would probably be broken by future upgrades of the application database. That didn’t seem like a very elegant solution either.

Other option I could think of was creating a giant array in Crystal, then plucking out the elements as needed. That’s not very elegant either. It’s slow and complex too.

Brief stop for a cup of tea (I am British after all), and inspiration arrived.

The database being used is MS SQL Server 2008 R2, so no method of referring to previous or next record as you can in SQL 2012 and Oracle. That means having to use the rather clunky method of joining the resultset to itself, but with a shift of 1 record.

The following queries are slightly simplified just to avoid getting bogged down in the details of the table joins.

Apologies for any errors as I can’t test the made up queries.

My original query:
SELECT DISTINCT runName, startTime, endTime, dayOfWeek, weekNumber
FROM regularBooking
ORDER BY runName, weeknumber, dayOfWeek, startTime

became (this is just for the previous record):

SELECT DISTINCT RANK() OVER (ORDER BY runName, weeknumber, dayOfWeek, startTime) as NewKey, runName, startTime, endTime, dayOfWeek, weekNumber
FROM regularBooking
LEFT OUTER JOIN (SELECT DISTINCT RANK() OVER (ORDER BY runName, weeknumber, dayOfWeek, startTime)-1 as PreviousKey, runName, startTime, endTime, dayOfWeek, weekNumber
FROM regularBooking) as PreviousBooking ON regularBooking.NewKey = PreviousBooking.PreviousKey

That worked, with 2 problems:
1) There are some duplicate bookings, so the RANK failed as ties would have the same rank, leaving gaps
2) My query that previously took less than a second to run, now took over 6 minutes and that’s before adding the join for the next booking.

Stop for another cup of tea.

I’d not used temporary tables with Crystal before. To be honest, I had no idea if they were supported or not. Most of my internet searches (thank you duckduckgo.com) drew either blanks or said it can’t be done, but no specified reason given.

I needed to be able to create a table with an index to improve performance while being able to find previous and next records with a similar method to my earlier query.

Here’s how it went:

–First create the temporary table (# signifies temporary in MS SQL)

CREATE TABLE #Gaps ( NewKey in  IDENTITY (1,1),    — Using Identity to automatically create an incrementing key
runName varchar(1000),
startTime Time,
endTime Time,
dayOfWeek varchar(10),
weekNumber tinyInt,
CONSTRAINT PK_NewKey PRIMARY KEY CLUSTERED (NewKey Asc) )

–insert the data into the temp table, ordered
INSERT INTO #Gaps SELECT DISTINCT runName, startTime, endTime, dayOfWeek, weekNumber
FROM regularBooking
ORDER BY runName, weeknumber, dayOfWeek, startTime

–Then the main query on the nicely indexed table. Original contained NULL checks, removed for simplicity
SELECT CurrentRecord.*, PreviousRecord.EndTime, NextRecord.StartTime
FROM #Gaps as CurrentRecord
LEFT OUTER JOIN #Gaps as PreviousRecord
on CurrentRecord.NewKey = PreviousRecord.NewKey+1
LEFT OUTER JOIN #Gaps as NextRecord
on CurrentRecord.NewKey = NextRecord.NewKey-1

–Finally drop the temporary table so that data can be refreshed in current session
DROP TABLE #Gaps

Once that was all tested in SQL Server Management Studio, it was simply a case of copying and pasting into a Command in Crystal Reports. The final query still took less than a second to run.
A few things to point out:
1) The user running the report needs the necessary rights to create and drop the temp table
2) I’m used to separating queries like this with GO. That’s not supported in Crystal and just returns errors
3) If you get an error during query execution for some reason, the temp table may not get dropped so you’ll get an error when you next refresh the report. Just logout of the database, refresh will now work.
4) I’ve used TIME datatypes. They were introduced in MS SQL 2008 R2. I wouldn’t use them next time as Crystal just sees them as text which then needs converting. Much easier to just use a fixed date in a DATETIME.

Time for tea.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Abhilash Kumar

    Nice!

    How about wrapping all this up inside a Stored Proc – let’s you use ‘GO’ as well.

    Much like having the cake and eating it too!

    -Abhilash

    (0) 
  2. Eric Rasenberg

    Hi, I would just like to add an idea:

    I used to create temp tables. But because of the problem with database access and rights i changed it to the following query :

    DECLARE @Or TABLE(Itemcode VARCHAR(100), Itemname VARCHAR (200))

    INSERT INTO @Or

    Select t0.itemcode, t0.itemname from OITM t0

    select * from @Or

    Regards,

    Eric

    (0) 

Leave a Reply