ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,671 through 8,685 (of 10,143 total)

RE: Row By Row Summing of all previous rows?

Here's another way:
-- sample data
DROP TABLE #temp
CREATE TABLE #temp ([ChapterPageTitle] VARCHAR(9), [No.OfPages] INT)
INSERT INTO #temp ([ChapterPageTitle], [No.OfPages])
SELECT 'Chapter 1', 2 UNION ALL
SELECT 'Chapter 2', 4 UNION ALL
SELECT 'Chapter...
^{“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

March 18, 2009 at 5:34 am

#961723
RE: Are the posted questions getting worse?

Bruce W Cassidy (3/17/2009)
[font="Verdana"]Happy join? :w00t:
I miss Oracle's natural join. "Just join the two tables, dammit! I've already told you what the foreign key relationship is..."
[/font]
It was...
^{“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

March 17, 2009 at 2:51 pm

#961402
RE: Are the posted questions getting worse?

Steve Jones - Editor (3/17/2009)
Green Beer!!
are we free associating now?
Beer! Hell yeah we know about this stuff! You want green beer? With or without the dye?
Check this one out 😎
SELECT...
^{“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

March 17, 2009 at 2:29 pm

#961390
RE: Are the posted questions getting worse?

Jack Corbett (3/17/2009)
GilaMonster (3/17/2009)
Bob Hovious (3/17/2009)
I'm still hoping to one day find sp_MSGoBackInTime documented in BOL. It is THE essential tool for recovering your database when you never...
^{“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

March 17, 2009 at 8:24 am

#961074
RE: Are the posted questions getting worse?

There seems to be an inverse square law operating between the amount of time and effort you put into a post, and the chance of the OP responding at all...
^{“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

March 17, 2009 at 8:19 am

#961068
RE: Are the posted questions getting worse?

GilaMonster (3/17/2009)
Bob Hovious (3/16/2009)
Gail, do you have any recommendations from your part of the world?
If you're into wines, you absolutely must not miss the Stellenbosch wines (Cape Town, South Africa)....
^{“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

March 17, 2009 at 7:47 am

#961019
RE: How to identify the which table data value is displyed resultset by using the Union operator in sql server 2000

rajendran.e (3/17/2009)
Hi,
Thanks for your reply..:)
Mentioned Query will return duplicate value
noo namee ...
^{“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

March 17, 2009 at 6:32 am

#960956
RE: How to apply SUM function multiple times on a column?

Christopher Stobbs (3/17/2009)
does my solution not work???
I'll test it with a million rows and see what happens...
OH and you haven't given us an example of a different start position?
It matches...
^{“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

March 17, 2009 at 5:01 am

#960920
RE: How to apply SUM function multiple times on a column?

What do you want the output to look like if @StartPosition contains 1 and @endposition contains 3?
How do the @StartPosition and @endposition correspond to values in the table? It's not...
^{“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

March 17, 2009 at 4:43 am

#960912
RE: how can use two subquery

Then bring the two queries together:
SELECT q1.No, q1.Nome, q1.SaldoCCO, q2.saldoLR
FROM (SELECT dt.No, dt.Nome, SUM(dt.PENDENTESDEB - dt.PENDENTESCRD) AS SaldoCCO
FROM (SELECT cc.No, cc.nome as Nome,
-- block 1
CASE WHEN origem =...
^{“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

March 17, 2009 at 4:30 am

#960902
RE: how can use two subquery

Restrict the SELECT column list to the columns we need, and get rid of the unnecessary "onion skin":
SELECT No, Nome, SUM(PENDENTESDEB - PENDENTESCRD) AS SaldoCCO
FROM (SELECT No, cc.nome as Nome,...
^{“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

March 17, 2009 at 4:15 am

#960894
RE: how can use two subquery

Since this is an "onion" select - there's a select of a select of a select - we can use one of them to calculate Saldo instead of having that...
^{“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

March 17, 2009 at 4:10 am

#960891
RE: how can use two subquery

Resolving the correlated subqueries as derived tables gives two CASE blocks - I guess debit and credit, and a third block which calculates the difference between the two as 'Saldo':
SELECT...
^{“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

March 17, 2009 at 3:07 am

#960873
RE: how can use two subquery

Here's the same query after 10 minutes spent reformatting it to make it a little more readable:
SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
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

March 17, 2009 at 2:24 am

#960857
RE: how can use two subquery

This query would be a heck of a lot simpler - and probably much faster - if all those repetitive correlated subqueries were replaced by derived tables:
SELECT No,nome, SUM(Saldo)...
^{“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

March 16, 2009 at 11:23 am

#960447

Viewing 15 posts - 8,671 through 8,685 (of 10,143 total)