ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,066 through 7,080 (of 10,143 total)

RE: Linking to the Previous Row

David McKinney (11/12/2010)
Alistair,
Just looking at it, I'd tend to agree with you. While I agree with the assertion that the CTE approach can't benefit from indexes (there are none),...
^{“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

November 12, 2010 at 9:17 am

#1250104
RE: How to achieve a non grouped column in an aggregate function

glyn.walters (11/12/2010)
It's 2005 Express on my dev environment and 2008 in production
Use ROW_NUMBER() like this:
SELECT
Price,
Vendor,
Item,
rn = ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Price, Vendor)
FROM...
^{“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

November 12, 2010 at 7:18 am

#1249994
RE: How to achieve a non grouped column in an aggregate function

Glyn, what version of SQL Server are you using?
^{“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

November 12, 2010 at 6:53 am

#1249975
RE: Transpose rows into columns

jeff.henrique.tatsoft (11/12/2010)
CREATE TABLE #First (Id INT, [DateTime] numeric(18,0), LogType int, TAGA INT, _TAGA_Q INT, TAGB INT, _TagB_Q INT) INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 37,634145506545000000,1,0,192,0,192
Msg 121, Level 15,...
^{“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

November 12, 2010 at 6:46 am

#1249972
RE: Transpose rows into columns

jeff.henrique.tatsoft (11/12/2010)
sorry
In the first post I put a simplified example
This table described in XML is First table
The second table is like the first post. I found some difficult ...
^{“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

November 12, 2010 at 6:14 am

#1249959
RE: Transpose rows into columns

It looks a little different to your first post: "The First table is loaded by external software, and i need that after each incert in first table load second table...
^{“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

November 12, 2010 at 5:44 am

#1249939
RE: Transpose rows into columns

Hi Jeff
If possible, can you post a few examples of how the incoming data will look?
Cheers
ChrisM
^{“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

November 12, 2010 at 4:58 am

#1249920
RE: Transpose rows into columns

SET DATEFORMAT DMY
DROP TABLE #First
CREATE TABLE #First (Id INT, [DateTime] DATETIME, TAGA INT, TagA_Qualyty INT, TAGB INT, TagB_Qualyty INT)
INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 1, '25/11/2010', 6,...
^{“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

November 12, 2010 at 4:20 am

#1249900
RE: Today's Random Word!

RichardDouglas (11/11/2010)
Juneau
11:11pm? Good Lord Rich, couldn't sleep? 😛
^{“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

November 12, 2010 at 2:13 am

#1249879
RE: Are the posted questions getting worse?

CirquedeSQLeil (11/11/2010)
I have a few articles that I am finishing up currently. Is there anybody that would like to give them a look-over before I submit them?
Go for it...
^{“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

November 12, 2010 at 2:09 am

#1249878
RE: Are the posted questions getting worse?

WayneS (11/11/2010)
Chris Morris-439714 (11/11/2010)
WayneS (11/10/2010)
Has anyone heard anything about Barry lately?
I had the same thought a couple of weeks ago, but a quick look revealed he'd been quite active, though...
^{“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

November 11, 2010 at 9:41 am

#1249570
RE: Are the posted questions getting worse?

jcrawf02 (11/11/2010)
Craig Farrell (11/10/2010)
Must be the week...
http://www.sqlservercentral.com/Forums/Topic1019000-392-1.aspx
Nice sig 😉 To be perfect, should start with "Yea" instead of "'lo", and have an extra thy in "Thy spool and thy...
^{“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

November 11, 2010 at 6:41 am

#1249461
RE: Finding Repeating Amount (more than 1 as group)

Your sample data doesn't match your expected output, Karthik.
DROP table #balance_due
create table #balance_due
(
[name] varchar(15),
balance_due_as_on datetime,
Amount float
)
insert into #balance_due
select 'John','11/01/2010',100.25 union all --
select 'John','11/02/2010',100.25 union all --
select 'John','11/03/2010',105.00 union all
select 'John','11/05/2010',200.75...
^{“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

November 11, 2010 at 5:33 am

#1249409
RE: Are the posted questions getting worse?

WayneS (11/10/2010)
Has anyone heard anything about Barry lately?
I had the same thought a couple of weeks ago, but a quick look revealed he'd been quite active, though not on 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

November 11, 2010 at 2:27 am

#1249345
RE: Help with SELECT query between certain time-frame

You're welcome. Thanks for providing enough information to crack the problem.
^{“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

November 10, 2010 at 9:29 am

#1248994

Viewing 15 posts - 7,066 through 7,080 (of 10,143 total)