ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,801 through 1,815 (of 10,143 total)

RE: Pivoting Problem

SELECT t.RBR, t.Value, x.Value
FROM #Test t
OUTER APPLY (SELECT ti.Value FROM #Test ti WHERE ti.RBR = t.RBR AND ti.Attribute = 'CRE') x
WHERE t.Attribute = 'XLOC'
GROUP BY t.RBR, t.Value, x.Value
^{“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

February 1, 2016 at 7:44 am

#1855682
RE: select with a while loop

As Phil has pointed out the performance of this code will be poor. Can you post the code for function dbo.isarbeitstag? Does it reference a calendar 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

February 1, 2016 at 7:34 am

#1855676
RE: Trying to replace UNION

SQL006 (1/31/2016)
Thanks chris, Luis for the solution.
This is just the scenario i created, In production there is no temporary table #Test only physical table which consists of 2 million data....
^{“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

February 1, 2016 at 1:29 am

#1855624
RE: Trying to replace UNION

Luis Cazares (1/29/2016)
Yet another option. However, I'm not sure which of the proposed versions would be the best option.
SELECT Id,
Amount,
CashFlowDate
FROM #Test
CROSS APPLY( VALUES(Principal, 'Principal'), (Interest, 'Interest')) x(Amount,AmtType)
WHERE...
^{“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

January 29, 2016 at 9:16 am

#1855277
RE: Are the posted questions getting worse?

Koen Verbeeck (1/29/2016)
Apparently got over 15,000 points today. Yay. :w00t:
What? In one day? Fingers on fire!
^{“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

January 29, 2016 at 9:07 am

#1855270
RE: Trying to replace UNION

You could use row constructors for this too:
SELECT
t.Id,
x.Amount,
t.CashFlowDate
FROM #Test t
CROSS APPLY (
VALUES
(CASE WHEN t.CashFlowDate between '2016-01-01' AND '2016-01-05' THEN t.Principal ELSE NULL END),
(CASE WHEN t.CashFlowDate between...
^{“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

January 29, 2016 at 5:55 am

#1855207
RE: Trying to replace UNION

SELECT
t.Id,
x.Amount,
t.CashFlowDate
FROM #Test t
CROSS APPLY (
SELECT [Amount] = CASE
WHEN t.CashFlowDate between '2016-01-01' AND '2016-01-05' THEN t.Principal ELSE NULL END
UNION ALL
SELECT [Amount] = 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

January 29, 2016 at 5:43 am

#1855206
RE: Why do I get this error ?

Ed Wagner (1/27/2016)
As a bit of a sidebar, I have to ask about the performance of your procedure. I saw a scalar function, non-SARGable join predicates, non-SARGable where clause...
^{“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

January 28, 2016 at 1:51 am

#1854880
RE: Are the posted questions getting worse?

Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)
Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed...
^{“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

January 27, 2016 at 7:34 am

#1854695
RE: Are the posted questions getting worse?

Grant Fritchey (1/27/2016)
ChrisM@Work (1/27/2016)[hrMy jaw hit the floor when I read the last paragraph. No support for cross-database queries? Two databases on the same "instance" isn't allowed or the two...
^{“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

January 27, 2016 at 5:34 am

#1854644
RE: Are the posted questions getting worse?

GilaMonster (1/27/2016)
ChrisM@Work (1/27/2016)
Two databases on the same "instance" isn't allowed or the two databases can't reference each other using three-part naming?
With Azure SQL DB, there's no such thing as...
^{“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

January 27, 2016 at 3:01 am

#1854631
RE: Are the posted questions getting worse?

Hugo Kornelis (1/26/2016)
Grant Fritchey (1/25/2016)
Hugo Kornelis (1/25/2016)
Grant Fritchey (1/25/2016)
A few questions for the threadizens. Interested in this from everyone, but especially interested in what you guys who consult think.
What blockers...
^{“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

January 27, 2016 at 2:02 am

#1854619
RE: Help -I am trying to create a local database on my PC. Why is it failing

mw112009 (1/25/2016)
Please see attached.
The highlighted field represents my PC name.
All this time I have been connecting to other servers. This time I thought I will have something locally on my...
^{“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

January 26, 2016 at 2:35 am

#1854378
RE: Convert a Date

ben.brugman (1/25/2016)
Luis Cazares (1/25/2016)
Another option:
SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-'))
Be carefull with dates, very often the language or the regional settings are important.
For the solution of Luis...
^{“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

January 25, 2016 at 9:17 am

#1854205
RE: Count issue/replace between!!!

Luis Cazares (1/25/2016)
Of course, there's a syntax error as you're following the FORMAT syntax instead of the CAST syntax.
Worth a try:
Dim SQLStringtempprd123 As String = "SELECT CAST([create_dtim] AS DATE), COUNT(*)...
^{“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

January 25, 2016 at 8:15 am

#1854177

Viewing 15 posts - 1,801 through 1,815 (of 10,143 total)