ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,141 through 7,155 (of 10,143 total)

RE: Is there a way to filter the parameter(s) within OVER(PARTITION BY ....) ? ? ?

john.acb (10/25/2010)
Hall of Fame, my example would be something like this:
SELECT
col1,
col2,
col3,
...
...
COUNT(CASE WHEN...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 25, 2010 at 4:58 am

#1241483
RE: Is there a way to filter the parameter(s) within OVER(PARTITION BY ....) ? ? ?

john.acb (10/25/2010)
Example:
Select
COUNT(col1) OVER(Partition By col1) as 'First' ,
COUNT(col1) OVER(Partition By col1) as 'Second' ,
COUNT(col1) OVER(Partition By col1) as 'Third',
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 25, 2010 at 4:40 am

#1241478
RE: Sequence-numbering groups

gmrose (10/21/2010)
For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes....
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 9:32 am

#1241009
RE: Today's Random Word!

Ray K (10/22/2010)
Tango. Golf. India. Foxtrot.
Heh yeah and only 2 1/2 hours until beertime! 🙂
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 6:49 am

#1240866
RE: Today's Random Word!

Brandie Tarvin (10/22/2010)
Do you do spicy chili or non-spicy?
I love a good chili flavour. This stuff takes some beating. I made some a week ago using Rick Stein's recipe and...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 6:29 am

#1240851
RE: Today's Random Word!

Brandie, please can you share your recipe? Most recipes over here suck. The one I use is modified from a Texas chili-cooking competition and takes ages.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 6:00 am

#1240841
RE: Dynamic Columns Names

hallidayd (10/22/2010)
I hope you understand I am attempting to guide you towards better practice and am not trolling. After that you can take what I say on board or disregard.
The...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 5:37 am

#1240828
RE: Dynamic Columns Names

Rootfixxxxxxer, can you please confirm which SQL Server version you are using?
The code I posted is stage 1 - optimise the query.
The new columns names would be generated in stage...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 4:33 am

#1240808
RE: Dynamic Columns Names

Blimey Gianluca, first-rate stuff and quick too. I was going to start by offering some improvements to the original query, then finish up with a cross-tab.
DROP TABLE #PontoRegistos
CREATE TABLE #PontoRegistos...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 4:26 am

#1240803
RE: insert if not exists, if it does insert into another table

Is there a question here?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 3:50 am

#1240792
RE: Today's Random Word!

CirquedeSQLeil (10/21/2010)
editing
Quite right too, your first draft was rubbish 😛
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 2:27 am

#1240760
RE: Can any one please guide,how to split this result,

sharath.chalamgari (10/21/2010)
...
Please let me know if i am wrong in any way
Nope, if the results are correct then it's not wrong. It may not be the fastest show in the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 2:24 am

#1240758
RE: Can any one please guide,how to split this result,

ColdCoffee (10/21/2010)
Chris Morris-439714 (10/21/2010)
ColdCoffee (10/21/2010)
And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.
There's a trick for that...put them in then
REPLACE(',' +...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 22, 2010 at 2:20 am

#1240755
RE: Can any one please guide,how to split this result,

ColdCoffee (10/21/2010)
And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.
There's a trick for that...put them in then
REPLACE(',' + EmpName + ',',',,',',')
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 21, 2010 at 10:09 am

#1240439
RE: select statement

Why do you think the query should return only one row (or none) regardless of how many rows may or may not match the filter?
I'm not sure if this...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 21, 2010 at 8:14 am

#1240344

Viewing 15 posts - 7,141 through 7,155 (of 10,143 total)