You're doing (the equivalent of) a CROSS JOIN on the WORKC and PLNT_WORKC tables (JOIN ON 1 = 1).  That could generate a huge number of rows.  You then filter...

You don't need a temp table, you can just create a view of the existing table.  The really good this is the view can be created using dynamic SQL, so...

You can defer the ON clause for a JOIN until after other JOINs.  Normally you wouldn't do that unless it's absolutely necessary.

LEFT OUTER JOIN TABLEC CC ON (CC.hFKey4 = CHG.hPKey...

Thank you all!

Sorry but I made a mistake on my original posting. Revised DATA representation is below

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
 ...

WHERE t_stamp >= (CAST(DATEDIFF(SECOND, '19691231 20:00:00', GETDATE()) AS bigint) * 1000) - (1000 * 60 * 30)

ARPRINCE,

Have you looked at the query plans for the two queries?  The LEFT JOIN method is doing a full table scan per join.  That could be a (big) performance issue,...

SELECT 
    ID,
    MAX(CASE WHEN Referrence = 'OrderNo' THEN Value END) AS OrderNo,
    MAX(CASE WHEN Referrence = 'OrderDate'...

Here's the "standard" cross-tab method:

SELECT 
    ID,
    MAX(CASE WHEN Referrence LIKE 'ON%' THEN Referrence END) AS OrderNo,
    MAX(CASE...

The use of the prefixed "TBL_" is a design error so bad it has a name; it's called the tibble.

This is not a design error.  It does not violate...

I use

;WITH

just to help prevent possible errors.  Sure, I could be technically correct and blame someone else when there was a problem with the code, but I prefer to avoid...

I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in...

I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in my OLAP...

I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate...

I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate the table...

>> The professorID is likely both the natural ID for the professor and the identity of the row. <<

The professor_id is probably a tax identification number of some kind....