ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,681 through 4,695 (of 10,143 total)

RE: is it possible to union all two CTEs?

How about a sample table script?
^{“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

May 1, 2013 at 9:15 am

#1611593
RE: SUM CASE, COLUMN MINUS COLUMN

Sean Lange (5/1/2013)
ChrisM@Work (5/1/2013)
Sean Lange (5/1/2013)
So what exactly is the issue here?
I would suggest you use the newer join constructs. With this query you don't even need a where clause.
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

May 1, 2013 at 8:11 am

#1611565
RE: SUM CASE, COLUMN MINUS COLUMN

Sean Lange (5/1/2013)
So what exactly is the issue here?
I would suggest you use the newer join constructs. With this query you don't even need a where clause.
Here is the syntax:
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

May 1, 2013 at 7:48 am

#1611558
RE: SUM CASE, COLUMN MINUS COLUMN

kabaari (5/1/2013)
select s.partNo, p.descript,
sum(case when battState = '99' then qty else '0'end) as CLNT,
sum(case when battState = '1' then qty-newQty else '0'...
^{“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

May 1, 2013 at 7:46 am

#1611557
RE: SUM CASE, COLUMN MINUS COLUMN

Where's [NewQty]?
^{“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

May 1, 2013 at 7:38 am

#1611551
RE: Random Number Generator

todd.ayers (4/30/2013)
LOL!! yeah sorry hope I didnt offend you?
Heck no!
Have you tested the code yet?
^{“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

May 1, 2013 at 7:10 am

#1611542
RE: is it possible to union all two CTEs?

KoldCoffee (5/1/2013)
Oh yeah! Nice. Thanks Chris.
In the real world case I have several CTEs in each statement. ie. ct1, followed by ct2, followed by final select. Which CTE...
^{“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

May 1, 2013 at 7:04 am

#1611538
RE: Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror:

You should consider replacing the slow slow cursor with a fast set-based equivalent:
UPDATE a SET
Transferred = 'Y'
OUTPUT
a.LoanTypeId,
a.LoanAmount,
a.LoanRate,
a.LoanYears,
MonthlyPayment = a.LoanAmount/(y.Result-1) * (a.LoanRate*y.Result),
a.[Status]
INTO Loans
FROM Applications a
CROSS APPLY (SELECT NumberOfPeriods...
^{“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

May 1, 2013 at 6:46 am

#1611533
RE: Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror:

...
/**Check if Loan exists**/
SELECT @RowCount = Count(*)
FROM Applications
WHERE ApplicationID = @ApplicationID
IF @RowCount = 0
BEGIN
PRINT 'Applicación' + @ApplicationID + ' no está aprobada o activa';
ROLLBACK TRANSACTION -- complete the transaction...
^{“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

May 1, 2013 at 6:28 am

#1611528
RE: is it possible to union all two CTEs?

KoldCoffee (5/1/2013)
Chris,
thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of...
^{“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

May 1, 2013 at 6:20 am

#1611525
RE: Query for non-existent entries

Left join the scores table to the weeks table. If you mock up sample data scripts, folks will show you exactly how.
^{“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

May 1, 2013 at 6:03 am

#1611523
RE: Exclude rows with blanks in a particular field

Beatrix Kiddo (5/1/2013)
Cool, thanks. I've amended it to.
and LEN(CURRENT_POINT) > 0
🙂
Is there any reason not to use this? CURRENT_POINT > ' '
^{“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

May 1, 2013 at 3:39 am

#1611511
RE: Exclude rows with blanks in a particular field

Beatrix Kiddo (5/1/2013)
Ah, ChrisM@Work, I think I get what you were saying now.
and LEN(LTRIM(RTRIM(CURRENT_POINT))) > 0
Seems to have fixed it.
Thanks all!
LEN ignores leading spaces, you don't need to trim. However,...
^{“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

May 1, 2013 at 3:27 am

#1611508
RE: Exclude rows with blanks in a particular field

Beatrix Kiddo (5/1/2013)
I know this is a popular one and I've Googled it but I haven't found anything that has worked yet, so apologies if this is incredibly obvious.
I'm trying...
^{“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

May 1, 2013 at 2:43 am

#1611494
RE: is it possible to union all two CTEs?

create table #temp (ID int, Name varchar(20))
insert into #temp
values
(10, 'Helen'),
(20, 'Joe'),
(30, 'Blake');
;WITH OnlyHelen AS (
SELECT CTE = 1, *
FROM #temp
WHERE Name = 'Helen'
),
ExceptHelen AS (
SELECT CTE = 2,...
^{“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

May 1, 2013 at 1:43 am

#1611483

Viewing 15 posts - 4,681 through 4,695 (of 10,143 total)