Jeff Moden

Forum Replies Created

Viewing 15 posts - 33,586 through 33,600 (of 59,099 total)

RE: query for continuous period

LutzM (6/15/2011)
You might want to have a look at the following blog for a better performing alternative:
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/sql-and-contiguous-data-ranges.aspx
Hi Lutz,
I strongly recommend that you delete that method from your briefcase. It...
--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)

June 18, 2011 at 3:47 pm

#1340824
RE: query for continuous period

Unfortunately, ColdCoffee's code breaks.
CREATE TABLE TEST( SRC varchar(6)not null,
OrgNo varchar(5)not null,
Sdate datetime Not nUll,
Edate datetime Not Null)
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/1999')
INSERT INTO TEST (SRC ,OrgNo...
--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)

June 18, 2011 at 2:31 pm

#1340818
RE: query for continuous period

@Cold Coffee.
There is a ticking time bomb in it in the form of "Hidden RBAR" because of an "accidental Cross Join". Using the following test data and looking at...
--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)

June 18, 2011 at 11:32 am

#1340803
RE: query for continuous period

Mark-101232 (6/15/2011)
This should work for you, not particularly efficient though
WITH Sdates AS (
SELECT a.Sdate,a.SRC,a.OrgNo
FROM TEST a
WHERE NOT EXISTS(SELECT * FROM TEST b
...
--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)

June 18, 2011 at 10:50 am

#1340799
RE: Need to Create File Listing through T-SQL

mail2payan (6/17/2011)
Thank You Sir.
1: This T-SQL is not accessible in network, works only in Local Machine, if I tried with local path, I am getting "Access Denied" stored 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)

June 18, 2011 at 10:01 am

#1340794
RE: Split the data in the row seperated by comma

As a bit of a side bar, using the following as search term in Google. It produces some pretty good hits according to the returned titles...
split CSV 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)

June 18, 2011 at 9:57 am

#1340793
RE: Split the data in the row seperated by comma

kamala_moorthy (6/17/2011)
...without using PL/SQL...
You might want to try a good Oracle forum or maybe give "Ask Tom" a try. This forum is heavily T-SQL oriented.
I am curious, though. ...
--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)

June 18, 2011 at 9:51 am

#1340791
RE: What is the output if used more than one DML command on same object at the same time?

Just to be sure... pay close attention to what Gail said. There are two inferrals to her answer. To explicity state the not-so-obvious inferral, it [font="Arial Black"]is[/font] possible...
--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)

June 18, 2011 at 9:47 am

#1340789
RE: Simple Join taking too much time

sqlnaive (6/17/2011)
Hello All, I don't know why but changing the order of join made the difference. The query is running quite fast now. How does it works anyways ? Anyone...
--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)

June 18, 2011 at 9:35 am

#1340788
RE: Using Identity and Key Value

Just a thought. I definitely wouldn't do things this way.
First, because of the 3 digits you allow, you're limiting each department to only 999 tickets. Further, using a...
--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)

June 18, 2011 at 9:00 am

#1340787
RE: Help in creating a query

It's not my job to judge, but nice job, Mark. 🙂
--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)

June 17, 2011 at 7:31 pm

#1340748
RE: Need help in writting SQL

rajg (6/17/2011)
So I would like to write a single query
Why does it have to be a "single query"?
--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)

June 17, 2011 at 7:19 pm

#1340746
RE: Cloud Outages

Most of the folks on this thread have echoed my "Cloud Concerns". I don't see a difference between the "Cloud Providers" of today or the "3rd party providers" 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)

June 17, 2011 at 7:08 pm

#1340745
RE: Use of temporary tables

MarvinTheAndriod (6/17/2011)
When designing a database I often hear it is not a good idea to use temporary tables.
Taken by itself, that's a myth and a fair amount of hear-say....
--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)

June 17, 2011 at 6:58 pm

#1340744
RE: PerfectDisk Vs DisKeeper

SQLRNNR (6/9/2011)
Unfortunately, if you have heavy fragmentation at the OS level - you would need to stop SQL Server for proper disk defrag using any util.
Why? I've never had...
--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)

June 17, 2011 at 6:41 pm

#1340743

Viewing 15 posts - 33,586 through 33,600 (of 59,099 total)