As I write this I have a SQL expression that has been taking 4 hours to compile (and still is not done). I know the SQL is correct and returns the correct value. It took this long to compile the first time I wrote it. I just needed to make 1 simple change and of course it is now taking forever to compile again. So my questions are this, is there any way to not have it compile when you save it? Like a report option I can turn off? My assumption here is no. The second question would be, is there some trick to how you write them to make them compile quicker? I have some that go fairly quick (couple of minutes) but some (most) just take forever. To the point I have almost quit using them. Could it be related to the processing power of my PC or the sheer size of the table (currently around 4.5 million rows). Would appreciate any hints or tricks to make them compile / run faster. Thanks!

I do have access to create views and stored procedures and will often go that route if it is something that I envision re-using over time. I will use a SQL expression for one offs. In this case I am looking for a single row of data that applies to a particular patient. In order to do this in Crystal natively I would either have to use a subreport which will bog down the running of the report or drop everything in the details section and write a formula to set a shared variable value when the condition exists. The problem with going that route is that when you have 4 or 5 of these scenarios (which is typical) you soon end up with dozens of formulas instead of like 4-5 expressions. I think in this scenario I will probably just create a generic view with my data that I can re-use later unless there is some other option that I am just overlooking. From a high level I am trying to determine what room a patient was in at a specific time when they were in a specific department. I know the department so the SQL runs against the separate table to see when their entry and exit into the room occurred when the department and patient ID is equal to the department and patient ID in the main report. Sorts by entry time descending and returns the top 1 (I.E. the first room they were in during the specified period of time that they were in the department). I have to do similar things such as determining what insurance provider they had at a specified period of time, for example when lab work was done etc.

Unfortunately my SQL is just mehhhh. I write enough to do small things or accomplish something specific etc. This SQL would be gigantic and while I could probably do it, the time it would take for me to write it would be prohibitive to go that route. I may have come across a different way to do it by joining a view (that I didn't know existed until 30 minutes ago) by department which will limit my results to only the rooms while they were in the department in question. Appreciate the responses though. I am always looking to advance my knowledge and find that input from like minded folks is the best way for me to look at something differently! Have a great day!