In the first query, you need to specify a specific db to look at, or include the db name in the results.  The object_ids and index_ids are unique only within...

According to your sample data and results, the "Answer" you marked is not correct.  For example, you stated that '10000' should return a 1 -- "Anything between 00000 to 10000...

We need to see the DDL for the two tables, including all index definitions.

Btw, if the tables really were "properly indexed", you wouldn't be having big performance issues on this...

For the pure SQL part, technically this could theoretically perform better since it avoids using functions on the table columns:

WHERE (p.JCCo=@Company and p.Job=@Job AND Phase NOT LIKE...

So I'm looking for the most efficient way to pull the last 30 minutes of data from the table. This converts it to date\time I want to use DATEADD(s,t_stamp/1000,'1969-12-31...

Be careful with that!  If you need UTC and "kludge" it this way instead, it may...

To get a UTC calc, just substitute GETUTCDATE() for GETDATE() in my code.

Hmm, looks like UTC time.

Or something in the data -- i.e. it's not really ms since 1/1/70 -- or it's local time where the server is?!

I put no max time, so, yes, it would give you everything from then on.

If you want to limit to the then-current time, do this:

WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE())...

I'm unsure what value a leading '9' should yield as the slab#, it's not really clear from your OP.

SELECT 
    LEFT(ZIP, 1) + CASE...

• This reply was modified 4 years, 7 months ago by ScottPletcher.

WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE()) - (1000 * 60 * 30)

Note that, for important performance reasons, you do not want to use any function at all on the column...

IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
    EXEC ('UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,''_'',''.''),2)
FROM dbo.[tbl_Reference]')
END

Create a "base" db with the schema, tables, etc. that you need.

When you need to create a new db, restore a backup of that db to the new db name...

SELECT 
    STUFF(NAME, 1, CASE WHEN LEFT(NAME, 5) = 'RECA-' THEN 5 ELSE 0 END, '') AS NAME
FROM dbo.table_name

A potential problem with REPLACE is...

• This reply was modified 4 years, 7 months ago by ScottPletcher.

Why are using the RTM version instead of applying patches?  You'll have performance issues from that as well.