Jeff Moden

Forum Replies Created

Viewing 15 posts - 54,091 through 54,105 (of 59,072 total)

RE: How can I group these codes and total them?

Or, better yet... best of both worlds
select sum(CorporateRevenue07.CommissionableRevenue) as totals,
CorporateRevenue07.Code
from CorporateRevenue07
where CorporateRevenue07.ProcessDate = '6/1/07'
and CorporateRevenue07.CommissionableRevenue > 0
and CorporateRevenue07.Code like 'WCSSHORE%'
AND CorporateRevenue07.RevDate = '1/1/07'
and CorporateRevenue07.Gate = 'WW'
and CorporateRevenue07.RevType = 'TB'
group by...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 9:49 pm

#751700
RE: How can I group these codes and total them?

select sum(CorporateRevenue07.CommissionableRevenue) as totals,
LEFT(CorporateRevenue07.Code,8) AS Code
from CorporateRevenue07
where CorporateRevenue07.ProcessDate = '6/1/07'
and CorporateRevenue07.CommissionableRevenue > 0
and CorporateRevenue07.Code like 'WCSSHORE%'
AND CorporateRevenue07.RevDate = '1/1/07'
and CorporateRevenue07.Gate = 'WW'
and CorporateRevenue07.RevType = 'TB'
group by LEFT(CorporateRevenue07.Code,8)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 9:46 pm

#751698
RE: Query Help

Is it possible to say, "Starting at the right of the DestHost field, go left until the 2nd dot and drop that dot and everything to the left of it....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 9:35 pm

#751696
RE: Pivot a list of dates into date intervals

The keyword is contiguous.
Give me the start and end date of all contigous date sequences.
Actually, Michael... according to that bit of information, I don't think you give a rat's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 9:04 pm

#751694
RE: Urgent! - How to extract number from a string

Matt is correct... he's beat the snare right off that drum... and guess what? He's right! Matt and I have done a huge amout of testing and using...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 5:24 pm

#751681
RE: is there any difference between != and <>

Took a while to get the code realigned correctly after the copy/paste operation
Matt,
Reformatting code that you've copied from those "Code" windows is actually pretty easy (although I wish they'd just...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 3:16 pm

#751670
RE: Concatenation of rows in function

Actually, you can get to the precise post Matt is talking about by clicking on the following... not need to hunt pages...
http://www.sqlservercentral.com/Forums/Topic419472-65-2.aspx#bm422799
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 2:59 pm

#751664
RE: Dynamically create cursor

Not sure, but I think dynamically creating a cursor is like dynamically creating a temp table... you may get it to work, but it'll likely be out of scope just...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 2:54 pm

#751662
RE: is there any difference between != and <>

Sorry... must still have the "ranger eye's" going on from lack of sleep the other day... I just say the results you posted for the run.
Thanks for fixing my boo-boo....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 2:34 pm

#751656
RE: is there any difference between != and <>

==============================================================================
The XML path() method
------------------------------------------------------------------------------
00:00:08:423 Duration (hh:mi:ss:mmm)
==============================================================================
Now... THAT's impressive! It reduced the processing time by almost ²/₃rds. Rare thing when you find a gem like that. Thanks!
EDIT: ...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 2:25 pm

#751654
RE: is there any difference between != and <>

No noticeable difference in processing time.
You mean on the dynamic SQL by itself or the whole Phone Test #1?
Thanks for the great tip (we call them "oolies" in the US...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 2:22 pm

#751653
RE: is there any difference between != and <>

Oh, I will add in an additional challenge if you two feel like it.
Take data of the following form, with a lot, lot more data (say 100 000 rows and...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 1:13 pm

#751636
RE: is there any difference between != and <>

Bottom of the page. It's actually a CSQ, but it seems to run like the wind with proper indexing (8 seconds re-concatenating the 3.54M phone numbers I extracted previously). Those...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 10:47 am

#751596
RE: is there any difference between != and <>

Ok... first things first... after a (finally) decent night's sleep, I did the Phase II code for the phone number problem. Assuming you still have the Tally table active,...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 9:50 am

#751580
RE: is there any difference between != and <>

Me too! Anyone know where there might be a posting for it? Or, maybe post it here so we can do some testing on it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

November 15, 2007 at 5:47 am

#751461

Viewing 15 posts - 54,091 through 54,105 (of 59,072 total)