Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,761 through 35,775 (of 59,102 total)

RE: Group Islands of Contiguous Dates (SQL Spackle)

nigel. (12/7/2010)
Nice Jeff, very nice indeed.
Now, all I need is to find somewhere I can use this 🙂
Thanks for the feed. With a bit of extrapolation, you can also...
--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)

December 7, 2010 at 10:00 am

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

autoexcrement (12/6/2010)
Okay, I think this is starting to make sense now. Contiguous is defined here as "at least one date entry per day during a range". Okay. And now 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)

December 7, 2010 at 9:58 am

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

tommyh (12/6/2010)
Nice article as a whole... a bit weak/confusing intro though. But thx to Craigs little comment it became much more clear what/why was being done.
/T
The folks that know...
--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)

December 7, 2010 at 9:56 am

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

autoexcrement (12/6/2010)
But how can you tell which dates are "start", "end" or "start and end" without any additional information (like the comments)? I don't see any rhyme or reason here,...
--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)

December 7, 2010 at 9:53 am

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

Craig Farrell (12/7/2010)
Alright, let's make the data a little more real world scenario.
You have a contractor who comes by and does work as needed. You're looking for a per...
--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)

December 7, 2010 at 9:46 am

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

dadam (12/7/2010)
When Jeff said, "even though the DateGroup looks like a date, it really doesn't mean anything," he was fibbing a bit, probably to keep unnecessary detail out of 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)

December 7, 2010 at 9:43 am

#1259631
RE: Reorganizing my indexes during working hours

noncentz (12/3/2010)
Good Morning everyone,
I have some slightly fragmented indexes that I would like to reorganize but the database is in use most of the day. I know index rebuilds happen...
--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)

December 6, 2010 at 9:52 pm

#1259359
RE: Convert computed column into a Date Type

Lowell (12/3/2010)
just alter the definition of the computed column so it includes a cast or convert;
however, since a DATE datatype doesn't have the time portion, i think you could...
--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)

December 6, 2010 at 9:39 pm

#1259355
RE: Finding non-contiguous data

CELKO (12/6/2010)
A little change in the DDL makes life more relational. Never sue BIT flags in SQL; that is assembley language.
Since SQL Server doesn't actually qualify as a relational database...
--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)

December 6, 2010 at 9:22 pm

#1259348
RE: Finding non-contiguous data

mister.magoo (12/6/2010)
Probably not the most elegant answer you will receive....
select *,
CASE WHEN
...
--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)

December 6, 2010 at 9:18 pm

#1259347
RE: Which data type don't accept "Blank Value"

rocky_498 (12/6/2010)
I am facing unique problem, My source file 60k rows, and source has mix data, data/blank/Null, Whenever i tried to run this package, package giving me error package transfer...
--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)

December 6, 2010 at 8:45 pm

#1259341
RE: Need advice to improve SQL Server 2000 performance

thang_ngo_2002 (12/5/2010)
Recently it runs slowly.
If you can figure out what changed (it may just be the size of the data), then you'll know what to do. Credits to Navy-Beans...
--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)

December 6, 2010 at 8:43 pm

#1259339
RE: TSQL CSV issue

JKSQL (12/6/2010)
What do I have wrong here?
DECLARE @MappingIds NVarchar(Max)
SET @MappingIds = 'E371F678-45DC-4C83-9D91-286E2BD8ACCF,E371F678-45DC-4C83-9D91-286E2BD8ACCB,E371F678-45DC-4C83-9D91-286E2BD8ACCC'
SET @MappingIds = REPLACE(@MappingIds,'|',',') +','
----Flip csv into a table
;WITH
cteSplit AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
SUBSTRING(@MappingIds,N+1,CHARINDEX(',',@MappingIds,N+1)-N-1) AS...
--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)

December 6, 2010 at 8:35 pm

#1259337
RE: Arithmetic overflow error converting expression to data type datetime.

pavargasq (12/5/2010)
id appreciate your help
Heh... we'd appreciate a little feedback. 😉 Did that fix it for you or not?
--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)

December 6, 2010 at 8:28 pm

#1259335
RE: Combining / crunching data?

Mike John (12/6/2010)
where .... "whatever determins the masters"
Can you quantify that in code using the example because that is the crux of this problem.
Also... you're using an UPDATE which modifies...
--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)

December 6, 2010 at 8:24 pm

#1259334

Viewing 15 posts - 35,761 through 35,775 (of 59,102 total)