ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,761 through 2,775 (of 10,143 total)

RE: SQL Query....

James.Rivers (12/3/2014)
Thanks for all your responses.
I wanted to avoid creating a temp table for this process so I have opted to use the
SELECT LocationId, MaterialId, Duration
FROM (
SELECT LocationId,...
^{“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 3, 2014 at 9:34 am

#1762443
RE: Renumbering the remaining records in a table after some recs have been deleted

Andrew Kernodle (12/3/2014)
Rough guess here, as there isn't too much information about your table; I'd guess you have some sort of patient ID number, or something like it, to identify...
^{“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 3, 2014 at 9:31 am

#1762442
RE: Collation issue

Have you tried sending the whole query to the linked server, something like this?
SELECT *
FROM OPENQUERY([mhsvi-sql2012a\instance1],
'SELECT
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5),101)
,
''/''
,
CASE
WHEN x.Age <= 24 THEN ''A''
WHEN x.Age BETWEEN 25 AND 34 THEN...
^{“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 3, 2014 at 6:47 am

#1762355
RE: SQL Query....

or
SELECT LocationId, MaterialId, Duration
FROM (
SELECT LocationId, MaterialId, Duration,
VAR(Duration) OVER (partition by MaterialID) AS VarDuration
FROM #t
) d
WHERE VarDuration > 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

December 3, 2014 at 6:12 am

#1762335
RE: SQL Query....

-- Sample data
IF OBJECT_ID ('tempdb..#Locations') IS NOT NULL DROP TABLE #Locations
CREATE TABLE #Locations (LOCATIONID VARCHAR(5), MATERIALID VARCHAR(5), DURATION SMALLINT)
INSERT INTO #Locations VALUES
('PlayM', 'Clip1', 626),
('PlayB', 'Clip1', 626),
('Ing', 'Clip1', 626),
('PlayM', 'Clip2',...
^{“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 3, 2014 at 4:51 am

#1762305
RE: Calculate age - Most easiest way

andrew_dale (12/2/2014)
A person born on 2012-02-29 should be how old on 2014-02-28?
1 year old still, the day has not rolled over to 1st March
select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) ,...
^{“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 2, 2014 at 6:22 am

#1762033
RE: Calculate age - Most easiest way

dave.ott 20779 (12/2/2014)
I've always liked this to get the age in years
select cast(datediff(d,@dob,getdate())/365.25 as int)
Try any date pair (DOB and reference date) where the month and the day are 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 2, 2014 at 6:08 am

#1762023
RE: Calculate age - Most easiest way

andrew_dale (12/2/2014)
If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a...
^{“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 2, 2014 at 6:01 am

#1762018
RE: Proper use of CTE in scalar valued function

Yes it is. Why not code it up as an inline table-valued function instead of a UDF? You're likely to get far better performance. Get the query to work and...
^{“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 1, 2014 at 4:45 am

#1761654
RE: Finding a word within a string

Boby B Jacob (11/27/2014)
ChrisM@Work (11/27/2014)
If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.
Hi Chris, scenario is made available by...
^{“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 27, 2014 at 5:29 am

#1761285
RE: Is there any chance to rearrange the execution plan manually?

vignesh.ms (11/27/2014)
...
1. Why scan happens before the join occurs? if it happens after join then the filter would be lighter. Even if optimizer chooses the scan to execute first....
You 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

November 27, 2014 at 5:07 am

#1761274
RE: Finding a word within a string

If you can provide some readily-consumable sample data, someone will be more likely to put together a query for you.
^{“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 27, 2014 at 4:06 am

#1761248
RE: IF EXISTS vs @@ROWCOUNT

TomThomson (11/26/2014)
Hugo Kornelis (11/26/2014)
Sorry for the harsh words, but this is absolute rubbish.
The code in option 2 will always do two lookups. One for the EXISTS, and then another one...
^{“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 26, 2014 at 7:23 am

#1761083
RE: IF EXISTS vs @@ROWCOUNT

skanker (11/26/2014)
I misunderstood what the question was asking so chose neither.
I can see now from the discussion that this was testing other things - however based on just the question...
^{“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 26, 2014 at 6:39 am

#1761071
RE: help writing this subquery

An alternative to Luis' method:
SELECT
e.*,
x.Addresscode, x.AddressType, x.[Address]
FROM #employee e
OUTER APPLY (
SELECT TOP 1 *
FROM #EmpAddress a
WHERE a.Eid = e.Eid
ORDER BY AddressType, Addresscode
) x
^{“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 26, 2014 at 4:21 am

#1761039

Viewing 15 posts - 2,761 through 2,775 (of 10,143 total)