Jeff Moden

Forum Replies Created

Viewing 15 posts - 52,351 through 52,365 (of 59,098 total)

RE: Complex (??) Query

Jack Corbett (2/26/2008)
Jeff,
Can you post how you would solve this? I don't care the delimiter I just want to see the easy solution. Since I have not found...
--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 26, 2008 at 5:33 pm

#782309
RE: Need a View to calculate Occupancy

Jack Corbett (2/26/2008)
When is a CTE the best tool for the job.
Just about anytime, Jack. Like I said, internally they're no different than a derived table and they...
--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 26, 2008 at 3:52 pm

#782279
RE: Need a View to calculate Occupancy

CTE... derived table... doesn't matter to me... they both do the same thing. I will admit a bit of a preference towards CTE's because it allows "top down" programming...
--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 26, 2008 at 10:36 am

#782110
RE: Need a View to calculate Occupancy

Lynn Pettis (2/26/2008)
This may not answer your question, but it may help you figure out what you need to do
Nice!
--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 26, 2008 at 10:27 am

#782104
RE: Complex (??) Query

darkins (2/26/2008)
Hey,
I am not the greatest TSQL guy (not even the not so greatest) so here is a question.
I have two tables. One has 1 "A" Record (table a) 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)

February 26, 2008 at 9:16 am

#782056
RE: Updating 9 million records takes too much time...

If you really want this to fly, use a SELECT/INTO with the correct calculation... once you're sure the data is correct, rename the tables and add the correct indexes 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 26, 2008 at 7:49 am

#781998
RE: why is my fragmentation so high on non-leaf index levels

That's likely the problem then... Not 100% sure, but I believe that if you don't actually have a clustered index on the table, it will never show defragmentation correctly.
Good...
--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 26, 2008 at 7:19 am

#781978
RE: Totals to date

antonio.collins (2/25/2008)
My $0.02. Most accounting systems would populate the outstanding balance either via a trigger or during some nightly process. So, you may want to consider adding 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 26, 2008 at 7:16 am

#781976
RE: CASE statement versus dynamic Query

Marios Philippopoulos (2/26/2008)
As Jack mentioned earlier, do use sp_executesql in your dynamic SQL for efficient reuse of execution plans.
Even with the 100 possible columns involved here, I'm sure a handful...
--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 26, 2008 at 7:11 am

#781974
RE: Not sure of best way to select different status values

Matt's solution will work perfectly for statuses up to 7998 characters long if stored in a VARCHAR(8000) and much more than that if in a VARCHAR(MAX)...
If you end up...
--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 26, 2008 at 7:06 am

#781971
RE: Breaking one column into two

I've never had the luxury (or maybe its "PITA") of working with something like Crystal reports or ASP or any of that nice GUI reporting stuff... I agree, it should...
--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 26, 2008 at 12:15 am

#781865
RE: retrieving duplicate consecutive records.

nazish,
It would be a big help if you provided some sample data according to the following article... help us help you... thanks...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--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 25, 2008 at 11:05 pm

#781859
RE: Dumb question, but how to launch SSRS?

Wow... looks like I have a bit of checking to do... thanks, Matt.
--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 25, 2008 at 4:29 pm

#781822
RE: CASE statement versus dynamic Query

Oh, I agree... the suggestion of sorting at the client means that you're sorting only the desired result set and no extra/unwanted data. Don't wanna "pop the pipe". ...
--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 25, 2008 at 2:51 pm

#781791
RE: Subquery problem (what should I use!)

If you can write an "IN" like that, then you can also write it as a JOIN to a derived table. A derived table is a query in parenthesis...
--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 25, 2008 at 1:40 pm

#781757

Viewing 15 posts - 52,351 through 52,365 (of 59,098 total)