Jeff Moden

Forum Replies Created

Viewing 15 posts - 34,981 through 34,995 (of 59,099 total)

RE: Every 3rd Friday of the Month

Dave Ballantyne (2/18/2011)
Surprised that no one has mentioned using a calendar table so far.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
Makes it all pretty simple.
Check my code where I mentioned the possible use of a Calendar...
--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)

February 18, 2011 at 5:52 am

#1288755
RE: Help needed to decode comma delimited string and insert into tables

I got a late start on this one. It's after 2AM here and I have to be up-and-at-em in 3 hours. I apologize but, unless someone else wants...
--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)

February 18, 2011 at 12:07 am

#1288656
RE: Every 3rd Friday of the Month

No doubt about it... both of those pieces of code find the 3rd Friday of each month. But that's only the first part of the problem. If we...
--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)

February 17, 2011 at 11:22 pm

#1288645
RE: Every 3rd Friday of the Month

Sometimes I just overlook the obvious. Thanks for the hint, Chris. I'll see if I can do it with a formula.
--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)

February 17, 2011 at 9:31 pm

#1288635
RE: How to summarise, listing on same line

timotech (2/17/2011)
Thanks Jeff, I'll be expecting.
Ummmm.... based on what I showed you in the previous code, couldn't you be "attemping" instead of "expecting"? 😉
--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)

February 17, 2011 at 9:24 pm

#1288634
RE: TSQL Help

ColdCoffee (2/17/2011)
Jeff Moden (2/17/2011)
CC... try your code against the data I posted. You may have a surprise in store.
Oh yeah, now i get it , Jeff.. completely missed 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)

February 17, 2011 at 9:16 pm

#1288632
RE: Every 3rd Friday of the Month

chris_n_osborne (2/17/2011)
Jeff Moden (2/17/2011)
SELECT CASE
WHEN GETDATE() <= CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month
...
--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)

February 17, 2011 at 8:59 pm

#1288630
RE: How to split/extrapolate single records by date range into multiple records

It's not my job to judge but I have to say it anyway, Craig... well done not only on the usage of the Tally Table, but also on conveying the...
--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)

February 17, 2011 at 8:50 pm

#1288627
RE: Extending minimum datetime values

danielagger98 (2/17/2011)
I want to extend the minimum datetime value(1973), some of the oracle date fields have date 01-01-0006, not that it makes any sense.
But if the management wants it, then...
--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)

February 17, 2011 at 8:42 pm

#1288624
RE: In what circumstances are there no other option than to use cursors?

Gromey (2/17/2011)
However, I still believe that any query you can do with a cursor, you can also do with a loop.
With only very rare exceptions, I believe that any query...
--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)

February 17, 2011 at 8:31 pm

#1288622
RE: In what circumstances are there no other option than to use cursors?

GSquared (2/16/2011)[hr"Quirky update" is definitely a cursor. It's just not a declared one. It's all about counting on the engine to process row-by-row in some hoped-for sequence. ...
--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)

February 17, 2011 at 8:25 pm

#1288621
RE: In what circumstances are there no other option than to use cursors?

tfifield (2/17/2011)
Quirky updates ...[snip]... are just too messy in some cases.
Considering that a Quirky update and a cursor written to do the necessary previous row calculations are nearly identical, I...
--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)

February 17, 2011 at 8:19 pm

#1288619
RE: Hidden RBAR: Triangular Joins

YSLGuru (2/24/2009)
corey lawson (12/5/2007)
So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to...
--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)

February 17, 2011 at 8:09 pm

#1288617
RE: TSQL Help

Actually, these are "double Triangular Joins". Please see the following article for the different types of "square" and "triangular joins".
http://www.sqlservercentral.com/articles/T-SQL/61539/
--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)

February 17, 2011 at 7:38 pm

#1288611
RE: TSQL Help

Heh... dang it... CC beat me this time. 😀
CC... try your code against the data I posted. You may have a surprise in store.
--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)

February 17, 2011 at 7:32 pm

#1288609

Viewing 15 posts - 34,981 through 34,995 (of 59,099 total)