ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,006 through 1,020 (of 10,143 total)

RE: Stored Procedure help

ma701ss (1/3/2017)
Forget returning the user id or data from any other columns, is it possible to amend the code to just return one value, i.e. either -2, -1 or 1?...
^{“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 4, 2017 at 1:27 am

#1920548
RE: Conditional Join

thbaig (1/3/2017)
No. It return all rows all time.
I have updated description
If you want results from either one table or the other, then use IF EXISTS()
^{“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 3, 2017 at 6:58 am

#1920353
RE: Conditional Join

thbaig (1/3/2017)
because if table2 has Reference_No, i want to display only these. Left join will display table1 data all the time
Do you mean this?
SELECT Reference_No
FROM (
SELECT Reference_No
FROM dbo.tblOptionalRefNo
UNION ALL
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

January 3, 2017 at 6:41 am

#1920344
RE: Conditional Join

thbaig (1/3/2017)
because if table2 has Reference_No, i want to display only these. Left join will display table1 data all the time
Can you write a better description of what you are...
^{“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 3, 2017 at 6:35 am

#1920339
RE: Are the posted questions getting worse?

Ed Wagner (1/3/2017)
I hope everyone had a safe and happy New Year celebration. Welcome back to work.
You too, Ed. Best wishes for 2017.
^{“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 3, 2017 at 6:28 am

#1920335
RE: Stored Procedure help

ma701ss (1/1/2017)
Hi, happy new year, been away and just coming back to this now.
What I'm not understanding is why the return value is not either -1, -2 or 1. Why...
^{“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 3, 2017 at 5:20 am

#1920327
RE: Help Eliminating Duplicate Data

Phil Parkin (12/28/2016)
Note also that
CASE WHEN height IS NULL THEN 0 ELSE 1 END
may be shortened to
IIF(Height IS NULL, 0, 1)
It's shortened...but is it fastened too? :-))
^{“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

December 28, 2016 at 6:15 am

#1919848
RE: A New Vehicle

"Twitter" wasn't one of the answers, so I used this clue instead.
^{“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

December 28, 2016 at 2:59 am

#1919832
RE: Help Eliminating Duplicate Data

drew.allen (12/19/2016)
Aggregate functions automatically exclude NULL values, so this can be simplified.
SELECT *
FROM (
SELECT
*,
rn = ROW_NUMBER() OVER(PARTITION BY ProductId, ObjectId ORDER BY CheckNumber DESC)
FROM EuroLuxProductBE.dbo.pdt_multidimensions m
CROSS APPLY (
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

December 28, 2016 at 2:49 am

#1919831
RE: Help With Query !!!

SELECT
[Source],
[Target],
StartDate,
EndDate = ISNULL(EndDate,LEAD(StartDate,1) OVER(PARTITION BY Source, Target ORDER BY StartDate,EndDate) - 1)
FROM #TEMP
^{“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

December 23, 2016 at 5:11 am

#1919501
RE: SQL Help- Pivot/Case

greeshatu (12/23/2016)
@thom-2 a:Thank you for formatting my question .
That is what I exactly want . Could pls help me out
Actually My data will be huge So When I use 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

December 23, 2016 at 4:39 am

#1919495
RE: Parsing paired relationships.

The sample data set has a row where both columns have the same value which messes up rCTE's unless you remove it. Even then, some of the parent values have...
^{“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

December 22, 2016 at 9:47 am

#1919380
RE: Parsing paired relationships.

Hi Richard, how many rows are you expecting back from this query?
^{“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

December 22, 2016 at 9:25 am

#1919374
RE: Help with delimited SQL

nadersam (12/22/2016)
Hi Guys,
I am trying to execute an array of comma delimited SQLs in a stored procedure.
I call it as below
declare @Parameter as nvarchar(max)
set @Parameter ='insert into Orderlog (trans_no)...
^{“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

December 22, 2016 at 5:51 am

#1919309
RE: Using Mod to Generate Work Shifts

That's not bad, Joe - I think MOD is the way to go with this.
The code can be trimmed down and tuned a little:
SELECT ShiftLabel = CASE WHEN n...
^{“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

December 22, 2016 at 2:05 am

#1919273

Viewing 15 posts - 1,006 through 1,020 (of 10,143 total)