ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,246 through 1,260 (of 10,143 total)

RE: SQL to sum last 12 month values from same table

DuncEduardo (8/23/2016)
I wouldn't say it's completely a "running total" problem as you require only previous 12 records from the one in scope.
Running Total will keep adding next value but not...
^{“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 23, 2016 at 6:58 am

#1896881
RE: How to prevent a table from being truncated or dropped

JasonClark (8/23/2016)
Try to run the below code to prevent accidental delete:
CREATE TRIGGER [TR_ProtectCriticalTables]
ON DATABASE
FOR
DROP_TABLE
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
...
^{“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 23, 2016 at 6:55 am

#1896880
RE: Temp Table 'vs' Table Variable 'vs' CTE.

The second article is written in growlingly poor English. I read it this morning. Despite some relief - in the comments - from the frustrating language and the glaring omissions...
^{“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 23, 2016 at 6:48 am

#1896875
RE: SQL to sum last 12 month values from same table

DuncEduardo (8/23/2016)
What's the quickest way to sum "last 12 months" values from one date column and update 2nd column?
e.g. original data contains "Date" and "Value", and we need to update...
^{“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 23, 2016 at 6:14 am

#1896866
RE: Duplicate Record In Result

Messi (8/23/2016)
ChrisM@Work (8/23/2016)
You have two rows in each table where Risk_01_Id_Num = '201600015'. Your result set with four rows is expected behaviour. If you wish to limit the result set...
^{“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 23, 2016 at 5:46 am

#1896862
RE: Optimize update with an index

WhiteLotus (8/22/2016)
Thank you for the reply mate 🙂
No PK in this table . Why do you ask this question ?
Is there a clustered index on the table?
What proportion of rows...
^{“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 23, 2016 at 5:12 am

#1896853
RE: return minimum records

SELECT RowId, AssetCode, WorkOrder, RequiredByDate, TimeDown, ReportText
FROM (
SELECT RowId, AssetCode, WorkOrder, RequiredByDate, TimeDown, ReportText,
rn = ROW_NUMBER() OVER(PARTITION BY AssetCode ORDER BY RowID)
FROM #TempTable
) d
WHERE rn = 1
ORDER 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

August 23, 2016 at 4:22 am

#1896839
RE: Duplicate Record In Result

You have two rows in each table where Risk_01_Id_Num = '201600015'. Your result set with four rows is expected behaviour. If you wish to limit the result set to 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

August 23, 2016 at 4:15 am

#1896835
RE: Find out the shift Time

ashok.theagarajan (8/22/2016)
I have a main table where I need to map the Time part of Open Time (a date time field) with a shift table containing the shift timings.
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

August 22, 2016 at 4:33 am

#1896586
RE: Quick question on Full-Text Indexes

kramaswamy (8/17/2016)
...Basically the difference between the two is that, in the former case, I'm filtering out the deleted records from TableA on the join with TableB at the same time...
^{“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 22, 2016 at 1:58 am

#1896569
RE: Selective updates to NULL

drew.allen (8/19/2016)
ChrisM@Work (8/19/2016)
Why can't you use this, Phil (substituting "peanut" for an appropriate integer value):
UPDATE #test
SET Flag = @Flag
WHERE 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 19, 2016 at 7:52 am

#1896352
RE: Selective updates to NULL

Or even this:
UPDATE #test
SET Flag = CASE
WHEN ISNULL(Flag,peanut) <> ISNULL(@Flag,peanut) THEN @Flag
ELSE Flag -- <-- NON-UPDATING UPDATE
END
WHERE 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 19, 2016 at 7:47 am

#1896349
RE: Selective updates to NULL

Why can't you use this, Phil (substituting "peanut" for an appropriate integer value):
UPDATE #test
SET Flag = @Flag
WHERE Id = @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 19, 2016 at 7:41 am

#1896346
RE: Concurrency Woes.

Not uncommon. Here's how someone who really knows what he's doing fixed the very same problem.
^{“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 19, 2016 at 4:48 am

#1896314
RE: Are the posted questions getting worse?

Steve Jones - SSC Editor (8/18/2016)
Luis Cazares (8/18/2016)
We've got new laptops today at the office. Mine is missing SSMS. I guess I'll have to wait to be productive for some...
^{“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 19, 2016 at 1:51 am

#1896299

Viewing 15 posts - 1,246 through 1,260 (of 10,143 total)