Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,356 through 35,370 (of 59,102 total)

RE: Help! SQL select statement for an uncommon task

Here's one possible answer to the problem you requested. It does not, however, fix the fact that you're relating tables that have no relationship to each other...
--===== This 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)

January 17, 2011 at 8:46 pm

#1274451
RE: Help! SQL select statement for an uncommon task

picant (1/17/2011)
Does anybody have a simple explanation what's wrong with such structure?
Yes... it produces data that doesn't actually exist in the real world. Instead, it produces randomized results from...
--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)

January 17, 2011 at 8:23 pm

#1274445
RE: How to fill in nulls in a set with zeroes?

s_osborne2 (1/17/2011)
Hi,
Change your select statement to have the following:
;
WITH cte
AS ( SELECT
...
--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)

January 17, 2011 at 7:44 pm

#1274438
RE: How to fill in nulls in a set with zeroes?

CELKO (1/17/2011)
CASE
WHEN B.account_change_pcT > 0.1 THEN 'Win'
WHEN B.account_change_pct < -0.1 THEN 'Loss'
WHEN B.account_change_pct <= 0.1 THEN 'BreakEven'
WHEN B.account_change_pct >=...
--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)

January 17, 2011 at 7:28 pm

#1274434
RE: Error while executing cursors

Remove the single quote encapsulation from the table name. Use square brackets, instead.
--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)

January 17, 2011 at 6:59 pm

#1274433
RE: Help! SQL select statement for an uncommon task

picant (1/17/2011)
...Okay, any particular order? ... Also,
why is the Red Apple Sweet and the Green Apple Bitter? Why couldn't it be the other way around? ...
--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)

January 17, 2011 at 6:55 pm

#1274432
RE: Database size issue

Everyone recommending a "shrink" on this thread is forgetting a very, very important thing to do after the shrink... you MUST rebuild your indexes after the shrink. Shrinking an...
--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)

January 17, 2011 at 6:53 pm

#1274431
RE: Please help with SQL code

LutzM (1/17/2011)
Jeff,
I'm expecting the sample section to be
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
At least that's what the sample data look like.
The new columns are [POL_LOCATION_CD] and [POD_LOCATION_CD]....
--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)

January 17, 2011 at 6:04 pm

#1274429
RE: Please help with SQL code

pwalter83 (1/17/2011)
Hi Jeff,
Thanks for your reply and sorry about not providing the updated tables and sample data. Please find the same here:
First one is the DHL_TRADE_ASSIGNMENT table-
-----------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER...
--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)

January 17, 2011 at 1:31 pm

#1274380
RE: Group Islands of Contiguous Dates (SQL Spackle)

Sachin Nandanwar (1/17/2011)
I realize the intentions are good here and thank you for that but there are a couple of problems with the code there. For one, it breaks several...
--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)

January 17, 2011 at 1:22 pm

#1274376
RE: Group Islands of Contiguous Dates (SQL Spackle)

tskelley (1/17/2011)
rstarr-916208 (1/16/2011)
Ok, I consider myself duly chastised.
By the way, what does SLA mean?
SLA is Service Level Agreement. Basically, a performance and quality agreement between the vendor/programmer and company...
--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)

January 17, 2011 at 1:00 pm

#1274370
RE: Are the posted questions getting worse?

Roy Ernest (1/17/2011)
Oh yea...!!!! My non technical article is going to be published this week. It will be on 20th - 21st of Jan. It is regarding Netmeeting.
Thanks Steve.
Heh......
--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)

January 17, 2011 at 12:49 pm

#1274366
RE: Group Islands of Contiguous Dates (SQL Spackle)

Sachin Nandanwar (1/16/2011)
Well I just stumbled upon this article.I tried to do it using quirky update method and seems to be working but haven't tested it on a huge no...
--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)

January 16, 2011 at 9:37 am

#1273989
RE: Group Islands of Contiguous Dates (SQL Spackle)

rstarr-916208 (12/8/2010)
I ran a test on 500 dates over the span of four years. It returned in 6 seconds. In the environment I come from that would be sufficient for...
--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)

January 16, 2011 at 9:12 am

#1273986
RE: Group Islands of Contiguous Dates (SQL Spackle)

venoym (12/8/2010)
Great post Jeff!
I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day...
--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)

January 16, 2011 at 8:43 am

#1273984

Viewing 15 posts - 35,356 through 35,370 (of 59,102 total)