Viewing 15 posts - 10,711 through 10,725 (of 14,953 total)
Glad we could help.
What trouble are you having with date ranges?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:34 pm
Certainly possible with CLR, or various other solutions. I'd probably take something like that outside the database, though. Use an external service.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:25 pm
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. ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:23 pm
Jeff Moden (11/29/2008)
Heh... milliseconds per calculation mean a lot if you have millions of shipments to make.
Actually, difference is microseconds per calculation, but for the sake of argument, let's call...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:19 pm
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...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 11:50 am
Grant Fritchey (2/26/2009)
I read tons...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 11:19 am
You'll be better off building this as a DTS package, then executing that from the proc (or a scheduled job).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 9:56 am
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...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 9:55 am
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...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 9:51 am
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...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 9:42 am
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 9:32 am
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...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 8:40 am
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 8:00 am
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);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 7:58 am
Venu Dukka (2/25/2009)
Let me add more here: The application code relies on the order by clause in the view to sort the results. So I...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 7:41 am
Viewing 15 posts - 10,711 through 10,725 (of 14,953 total)