SELECT TOP (1000) --remove after testing!

t.OPENDATE, t.ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

...

Steve-0 (5/5/2014)

---

Hi Everyone thanks for the replies.

For the first 2, I'm not using static dates as per your queries.

Scott, your query is just casting values for actual field...

SELECT

OPENDATE, ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

RIGHT('0' + CAST(minutes_diff % 60...

DECLARE @date_with_month datetime

SET @date_with_month = GETDATE()

;WITH

cteDays AS (

SELECT 1 AS day# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT...

I'd try to do this in the most straightforward way possible. Therefore, I'd go with db snapshots unless the modification activity on the dbs during the ETL process was...

Look at existing index usage and missing index stats. The first thing is to verify that you have the best clustered index. Once that is done, you can...

Edit: Based on the index missing and index usage stats you posted (thanks!) I would say: /Edit.

JID, not DID, seems like the best clustering index key for SELECTs. We...

Jeff Moden (4/29/2014)

---

ScottPletcher (4/29/2014)

---

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

I believe OP said that Enterprise Edition was not an option.

Eirikur Eiriksson (4/29/2014)

---

ScottPletcher (4/29/2014)

---

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the...

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes...

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

What indexes does SQL report are missing? What is the usage of existing indexes on that table?

There's a reasonable chance that the table should be clustered by [DID] rather...

You probably want to do the grouping in the inner query rather than the outer query. Also, can limit parameter values to 1-4 in the inner query itself.

SELECT a.BoxId,...

below86 (4/23/2014)

---

Lynn Pettis (4/23/2014)

---

below86 (4/23/2014)

---

Wouldn't it be better to use the BETWEEN with these dates?

WHERE (ath_postype = 'NTC' OR ath_postype='NTD')

AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' ...