Jeff Moden

Forum Replies Created

Viewing 15 posts - 27,166 through 27,180 (of 59,101 total)

RE: Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Megha P (3/22/2013)
Hello ,
you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1...
--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)

March 23, 2013 at 7:40 pm

#1600094
RE: How to report on historical movements within a changing hierarchy

polkadot (3/22/2013)
Yes, except that my question is
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?
I will create a model 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)

March 23, 2013 at 7:35 pm

#1600092
RE: Join yeilds different results if used with CTE or a real table

Evil Kraig F (3/21/2013)
Jeff Moden (3/21/2013)
You're joining to an indeterminent value and at execution plan time, it can't determine what to join on and so it ignores the predicate. ...
--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)

March 23, 2013 at 7:29 pm

#1600091
RE: Best Approach to Archieve DB

opc.three (3/21/2013)
In this case SSIS is a good choice regardless of the complexity of the tranformation because the source and destination databases are going to be on different instances.
If you...
--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)

March 23, 2013 at 7:24 pm

#1600089
RE: Best Approach to Archieve DB

david.alcock (3/22/2013)
Jeff Moden (3/21/2013)
david.alcock (3/21/2013)
SSIS is normally utilised when the copy/move is complex, if it involves data transformations, validations etc.
I'll go ahead an disagree with that. I've built some...
--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)

March 23, 2013 at 6:51 pm

#1600084
RE: Global System UDF to return ISO Date string

Lynn Pettis (3/23/2013)
I'm thinking this would work on SQL Server 2000 when you could update system tables.
I'm thinking the same thing but wanted to make sure because it sure would...
--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)

March 23, 2013 at 6:41 pm

#1600083
RE: Global System UDF to return ISO Date string

The following code doesn't work.
EXEC sp_changeobjectowner 'fn_DateTimeToISODate', 'system_function_schema'
It produces the following error in SQL Server 2008.
Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 111
Database principal or schema 'system_function_schema' does...
--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)

March 23, 2013 at 5:03 pm

#1600079
RE: Broadcast calender generator

Alan.B (3/21/2013)
The query below should do the trick. I don't know what day counts as "Media Start of Week" but I went with monday (isowk).
Oh, be careful now. ...
--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)

March 21, 2013 at 7:09 pm

#1599648
RE: Join yeilds different results if used with CTE or a real table

You're joining to an indeterminent value and at execution plan time, it can't determine what to join on and so it ignores the predicate. Look at the execution plan...
--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)

March 21, 2013 at 6:47 pm

#1599644
RE: i want update the 2nd column based on first column:

carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
...
--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)

March 21, 2013 at 12:11 pm

#1599521
RE: Best Approach to Archieve DB

david.alcock (3/21/2013)
SSIS is normally utilised when the copy/move is complex, if it involves data transformations, validations etc.
I'll go ahead an disagree with that. I've built some killed ETL systems...
--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)

March 21, 2013 at 12:07 pm

#1599517
RE: Best Approach to Archieve DB

runal_jagtap (3/21/2013)
Jeff, Yup The Srever & the Database would be different...
Why it would be overkill :w00t: explain me.. coz i am going to do it.. i dont know how 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)

March 21, 2013 at 12:02 pm

#1599513
RE: How to report on historical movements within a changing hierarchy

polkadot (3/20/2013)
K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.
Helen
Check the following link for the different types 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)

March 21, 2013 at 11:52 am

#1599510
RE: i want update the 2nd column based on first column:

This is a classic problem and the classic solution, which runs on all versions of SQL Server, turns out to be quite fast.
--===== Create the test data.
...
--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)

March 21, 2013 at 7:50 am

#1599386
RE: How to report on historical movements within a changing hierarchy

Vedran Kesegic (3/20/2013)
Never delete. On move, set enddate instead, and copy to new location with startdate=enddate+1. Add date filter to queries.
+100. A "Type 2 Slowly Changing Dimension" will work...
--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)

March 20, 2013 at 6:33 pm

#1599198

Viewing 15 posts - 27,166 through 27,180 (of 59,101 total)