Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a iTVF.

SELECT TOP...

Stefan_G (8/13/2013)

---

If you want maximum performance you could use something like this:

create function fixop2(@a varchar(20)) returns table

as

return select

case

when right(@a,1) between 'A' and 'I' then +cast(left(@a, len(@a)-1) as int)*10 + (ascii(right(@a,1))...

Also, actual plan for this query:

SELECT

i.Item_SK,

[Class]= i.class,

Department= i.dept,

Division= i.div,

Subclass= i.subclass,

[Rows]= COUNT(*)

FROM SalesDate s

INNER JOIN Item i

ON i.Item_SK = s.Item_SK

WHERE s.dateid >= '20110201'

AND...

An execution plan or two would help loads. I'd suggest an estimated plan for the query shown, and an actual plan for a shorter time period so you're not waiting...

SELECT

t.blp_proposalno,

t.blp_documentattach,

t.blp_fund,

t.blp_branch,

x.[bld_Document attach]

FROM #t t

OUTER APPLY (

SELECT [bld_Document attach] =

STUFF((

SELECT ',' + bld_document

FROM #m m

WHERE m.bld_fund = t.blp_fund

AND t.blp_documentattach LIKE '%'+CAST(m.bld_documentid AS VARCHAR(3))+'%'

FOR XML...

Really? It's just a query. What error message does RS fart out?

Stefan's code and my code now return a result set which exactly matches your posted requirements. If your requirements have extended or changed, then please use a sample data set...

SELECT

x.Name,

base.ls_id,

base.date_lease_start,

base.date_lease_end,

base.lease_term,

base.amount_current_rent

FROM base_table AS base

LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id

LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id

LEFT OUTER...

Run this query and study the results. If you are still unsure then ask:

SELECT

DateAndTime,

AbsoluteMinutes, -- minutes since 19000101.

AbsoluteMinutes/60.00, -- INT divided by DECIMAL: decimal fraction retained.

AbsoluteMinutes/60, -- INT divided...

Picking the right columns to use for a covering index is mostly straightforward. List all of the columns used in the (sub)query. If the column is used as a filter...

Try this index, Mike:

CREATE INDEX ix_Test ON Jobtran (oper_num, trans_num DESC, job, suffix) INCLUDE (emp_num)

Edit: CREATE INDEX ix_New ON Jobtran (job, suffix, oper_num, trans_num DESC) INCLUDE (emp_num)

It is referenced:

CASE -- All std time captured on Move*, as this is where quantity is recorded

-- where Move trans types don't exist, qty is recorded on other trans types...

This, I think, would make a reasonable test query:

SELECT

jt.job,jt.suffix, jt.oper_num, jt.trans_num, jt.emp_num,

x.oper_num, x.trans_num, x.emp_num,

jt2.trans_num

FROM jobtran jt (NOLOCK)

OUTER APPLY (

SELECT TOP 1

jtx.emp_num, jtx.oper_num, jtx.trans_num

FROM jobtran jtx(NOLOCK)

WHERE jtx.job =...

I'd focus on the one table for now, which gives you this query:

SELECT

jt.trans_num,

emp_num = CASE

WHEN jt.emp_num IS NOT NULL

THEN jt.emp_num

ELSE

(SELECT TOP 1

jtx.emp_num

FROM jobtran jtx(NOLOCK)

WHERE jtx.job = jt.job

ANDjtx.suffix =...

mike.dinnis (8/12/2013)

---

The main query (from which this was the identified 'slow' bit) uses; emp_num, trans_type, a_hrs, qty_moved, wc and CreateDate in the returned fields; job, suffix, wc, oper_num and trans_num...