Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,271 through 24,285 (of 59,099 total)

RE: exclude CTE values

pmadhavapeddi22 (2/27/2014)
select col_1 from table_1
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_2
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_3
inner join table_4
where table_4.id...
--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 27, 2014 at 9:25 am

#1692754
RE: Convert consecutive rows with a date to date range

d viz (2/27/2014)
OK this is more of an academic question but having looked all over I haven't been able to find a solution to a seemingly easy problem. I have...
--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 27, 2014 at 8:58 am

#1692737
RE: DateDiff() function reported as NonDeterministic

Sean Lange (2/27/2014)
dogramone (2/26/2014)
IS there any specific reason you are using "DAY" within the datediff or did you mean to use d or dd?
Using the explicit value is generally considered...
--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 27, 2014 at 7:50 am

#1692695
RE: Fixing CREATE TABLE

SQLRNNR (2/26/2014)
Jeff Moden (2/26/2014)
There are two answers that I've grown accustomed to when working with any flavor of SQL. The first, of course, is "It Depends". The second...
--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 27, 2014 at 6:39 am

#1692655
RE: Parse Info Out of FileName

I guess "keeping the underscores" would be a prob lem for the OP, though. His original format had no underscores.
--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 27, 2014 at 6:34 am

#1692648
RE: Datetime difference converted to int does not return same result as datediff

Koen Verbeeck (2/26/2014)
I believe the date functions are more precise in that aspect.
I certainly agree that the date functions make life a whole lot easier in certain areas but 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 27, 2014 at 6:20 am

#1692634
RE: SQL 2008 R2 - Need to create a CLR assembly that calls a Java function and implement as a UDF

I agree. Beer popsicles are in order for this one. Thanks for sharing the nature of it all. Heh... sounds like a lot of "fun". This...
--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, 2014 at 10:40 pm

#1692528
RE: Parse Info Out of FileName

Ed Wagner (2/26/2014)
... and it does 1M rows in about 12 seconds.
Heh... damn. I must be gettin' old and starting to slip. 😛
--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, 2014 at 10:32 pm

#1692527
RE: Query against partitioned tables dont return all results

ricardo_chicas (2/26/2014)
That is what I suspect, but what I do not understand is why the management studio says that the query ended succesfully...
Time to turn the tables. Ask him...
--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, 2014 at 9:56 pm

#1692525
RE: Database Growth

pietero 48156 (2/26/2014)
Hi,
Just entered a new role in my company as junior DBA.
I am looking for assistance or guidance with regards to a script to see the weekly growth of...
--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, 2014 at 9:51 pm

#1692523
RE: Capturing the Reason For Change for Type 2 changes

Lempster (1/29/2014)
If I want to populate a ReasonForChange column in a dimension table for Type 2 changes, is it best practice to populate that column in the new row (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, 2014 at 9:33 pm

#1692522
RE: Query Performance - Using Inline/Table Functions

Jako de Wet (2/26/2014)
Thanks Kevin
Will read through it tomorrow.
Interested to read how to get info back in a query that requires more than a plain join.
Do the functions have 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, 2014 at 9:25 pm

#1692521
RE: Datetime difference converted to int does not return same result as datediff

Koen Verbeeck (2/26/2014)
If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting in...
--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, 2014 at 9:19 pm

#1692518
RE: SQL Insert with Identity value AND incremental rollover column ?

Avoid the RBAR. Make a persisted computed column that uses a formula like MTRANS_ID%999+1. Yeah... it'll have gaps, now and again... just like the IDENTITY column
--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, 2014 at 9:06 pm

#1692516
RE: Replace .TIF with .pdf

amandaallen243 (2/26/2014)
2Tall (4/12/2013)
Hi to all. I want to update a column that links to an external document. The external documents have been converted from .TIF[/url] to .pdf[/url]. The file name...
--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, 2014 at 9:03 pm

#1692515

Viewing 15 posts - 24,271 through 24,285 (of 59,099 total)