Glad we could help.

What trouble are you having with date ranges?

Certainly possible with CLR, or various other solutions. I'd probably take something like that outside the database, though. Use an external service.

Gail, my point about the sort being caused by the Union (All) wasn't because of the operator directly, it's because the query is sorting the results of a union. ...

Actually, difference is microseconds per calculation, but for the sake of argument, let's call...

Please post the execution plans for the query with the indexes you already have, and with the index I proposed (on each table).

The Union operator is what is going to...

Grant Fritchey (2/26/2009)

---

Since there are so many hard core SF readers on here, I thought I'd pass on what I just heard. Phlip Jose Farmer has died. http://reason.com/blog/show/131886.html

I read tons...

You'll be better off building this as a DTS package, then executing that from the proc (or a scheduled job).

You can't declare a variable in the calling proc, then use it in the dynamic SQL. sp_executesql allows for output parameters. Set up one of those, you'll be...

I would dump the second index you create, and replace the first one with one that started with service date, then client ID, and then use Include on the other...

select *

from #TranTable t1

inner join #TranTable t2

on t1.CustomerID = t2.CustomerID

and t2.Type = 'With'

and t1.Type = 'Dep'

and t2.DateEntered between

dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)

and

dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0)

left outer join #TranTable...

MS Sales and Licensing questions don't generally have a cost associated with them, so far as I know. It's dev support that usually costs money.

That's probably overkill, but it should work. Why would you do that, though?

The whole point of the key columns in indexes is that you will be using them in...

That's normal.

Generally, if the first column in the index has an equality test in the query, you'll get a seek. The rest of it doesn't matter as much.

Try this:

select t1.*, t2.TranID

from dbo.TranTable t1

inner join dbo.TranTable t2

on t1.CustomerID = t2.CustomerID

and t2.Type = 'With'

and t1.Type = 'Dep'

and t2.DateEntered between

dateadd(day, datediff(day, 0, t1.DateEntered)+1, 0)

and

dateadd(day, datediff(day, 0, t1.DateEntered)+2, 0);

Venu Dukka (2/25/2009)

---

Thanks for the replies you guys.

Let me add more here: The application code relies on the order by clause in the view to sort the results. So I...