ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,351 through 1,365 (of 10,143 total)

RE: Please throw some light on how this complicated query works

Often simply formatting a query nicely is sufficient to be able to figure out what's going on. The Simple Talk prettifier will get you most of the way there. I've...
^{“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

August 1, 2016 at 8:41 am

#1892964
RE: Select Statement SubQuery in the From Clause

SELECT mu.Id, mu.name, mu.Code,
Count(mu.Id) AS UnitCount
Into TN -- temporary table?
FROM Ref.MilitaryUnit mu
LEFT JOIN Person.DMDC dmdc ON mu.Id = dmdc.Id
LEFT JOIN Person.Military mp ON mu.Id = mp.UnitId
Group By mu.Id,...
^{“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

August 1, 2016 at 8:21 am

#1892956
RE: How do we avoid junk in the VARCHAR fields ?

Luis Cazares (8/1/2016)
Maybe like this? Why do you have different validations for names and other values?
UPDATE [MEMBER].[IMP_MEMBER_INT]
SET
[ContactLastName] = 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

August 1, 2016 at 7:43 am

#1892947
RE: Are the posted questions getting worse?

Sean Lange (7/29/2016)
ChrisM@Work (7/29/2016)
Sean Lange (7/29/2016)
ChrisM@Work (7/29/2016)
Sean Lange (7/29/2016)
Good grief...the spammers are out in force today.
Reported as spam
Sweet!!! Maybe I can get banned!?!?!?!?!
It's Friday, you can get anything you like...
^{“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

July 29, 2016 at 9:54 am

#1892678
RE: Are the posted questions getting worse?

Sean Lange (7/29/2016)
ChrisM@Work (7/29/2016)
Sean Lange (7/29/2016)
Good grief...the spammers are out in force today.
Reported as spam
Sweet!!! Maybe I can get banned!?!?!?!?!
It's Friday, you can get anything you like 🙂
^{“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

July 29, 2016 at 9:23 am

#1892670
RE: Are the posted questions getting worse?

Sean Lange (7/29/2016)
Good grief...the spammers are out in force today.
Reported as spam
^{“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

July 29, 2016 at 9:03 am

#1892661
RE: UPDATE SQL not working

BigB (7/28/2016)
Hi
Yeah I know how outer join works. Just wondering why it is not updating tab1.id when select returning the result set. Anyway, how can this be achieved then?
The 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

July 28, 2016 at 5:12 am

#1892440
RE: UPDATE SQL not working

The answer to your question is "there are no qualifying rows in t1 to update". You're mistaking NULL as a placeholder for NULL as a 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

July 28, 2016 at 4:24 am

#1892433
RE: UPDATE SQL not working

bhushanbagul (7/28/2016)
tab1 contains only one column which is null in all records and tab2 also contains one column which is not null and having data. I want to update tab1.id...
^{“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

July 28, 2016 at 3:35 am

#1892429
RE: Are the posted questions getting worse?

BrainDonor (7/28/2016)
I would like to know why they even bother.
How many people out there are going to think "I could do with a new kitchen, where should I look? I...
^{“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

July 28, 2016 at 3:17 am

#1892423
RE: How to write tsql for report

JoNTSQLSrv (7/28/2016)
Sergiy (7/28/2016)
Can you be more specific about the term "completely different logic"?
Are the tables at least the same for those different logics?
Thanks for your reply Sergiy, i meant...
^{“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

July 28, 2016 at 1:38 am

#1892414
RE: Using variables with the LIKE operator

Like this:
SELECT ReturnValue
FROM (
SELECT
ReturnValue = ISNULL(cast(replace(left(QueryValue,charindex('|',QueryValue)-1),'{','') as float)/1000,0),
rn = ROW_NUMBER() OVER (PARTITION BY SourceID, PcsAssmntDataID ORDER BY IdentifierID DESC)
FROM livefdb.dbo.PcsAssmntData_Queries
WHERE SourceID = 'BRO'
and QuerySetID = 1
and QueryNumberID = 1
and PcsAssmntDataID...
^{“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

July 27, 2016 at 7:07 am

#1892258
RE: Using variables with the LIKE operator

NineIron (7/27/2016)
Thanx but, in the meantime I came up with this solution..............................
alter function dbo.fnWeight(@VisitID varchar(100))
RETURNS float as
BEGIN
DECLARE @Return float
set @VisitID=@VisitID + '{A^VS.WEIGHT}'
select @Return=cast(replace(left(PADQ.QueryValue,charindex('|',PADQ.QueryValue)-1),'{','') as float)/1000
from livefdb.dbo.PcsAssmntData_Queries PADQ
inner join (selectSourceID,
PcsAssmntDataID,
max(IdentifierID) 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

July 27, 2016 at 7:03 am

#1892257
RE: Using variables with the LIKE operator

You don't need the wildcard on the left-hand side of the variable because you're matching from the left. It's also not SARGable (cannot exploit seeks). Try this instead:
where PcsAssmntDataID LIKE...
^{“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

July 27, 2016 at 6:26 am

#1892247
RE: sql query

sanjayasamantaray (7/27/2016)
I have week codes like 201551 , 201552
I want the sql query to return firstday and last day for those weeks
How are the weeks defined?
Is the first day...
^{“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

July 27, 2016 at 6:22 am

#1892244

Viewing 15 posts - 1,351 through 1,365 (of 10,143 total)