ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,261 through 4,275 (of 10,143 total)

RE: Salesman Running Totals by Date problem...

Hi Charlotte
Some sample data in the same format as you provided in this thread would help. Thanks.
^{“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 30, 2013 at 4:41 am

#1636907
RE: Select variable based upon sub query

Do you have to read the same table three times or would something like this work?
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
HAVING Value1 BETWEEN
(CASE WHEN Value1 <= @trig
THEN TRUNCATE(Value1-@tol1,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

July 30, 2013 at 4:37 am

#1636905
RE: Using non-deterministic T-SQL functions inside a UDF

There is a cheaty way to use a side-effecting function in a function, and that's to encapsulate it into a view, like this:
CREATE VIEW vw_srn AS SELECT srn = ABS(CHECKSUM(NEWID()))
GO
CREATE...
^{“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 30, 2013 at 3:55 am

#1636890
RE: Using non-deterministic T-SQL functions inside a UDF

Use APPLY with a simple expression like this. Also, RAND operates as a run-time constant. NEWID doesn't, so you can generate a new value for each output row of your...
^{“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 30, 2013 at 3:49 am

#1636889
RE: When NULL IS NOT NULL

Peter Brinkhaus (7/29/2013)
mister.magoo (7/29/2013)
Of course, I could be wrong and will skulk away with my tail between my legs when Paul tells me I am wrong 😛
I guess Paul will...
^{“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 30, 2013 at 1:20 am

#1636852
RE: create table valued function

SQL_Surfer (7/29/2013)
Where are you doing SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a ? I need to get max on that column from Mytable.
You can't get...
^{“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 30, 2013 at 1:04 am

#1636847
RE: Find the first record for each month

N.D (7/29/2013)
Ok, I popped the results into a temporary table. It's only giving the results (rn = 1) for either the first or the last year (depending on whether 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 30, 2013 at 12:52 am

#1636843
RE: Find the first record for each month

There's an extra column in your output. You can filter on it, provided you set up the query as an inner query or CTE of an outer query wielding 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

July 29, 2013 at 9:38 am

#1636693
RE: When NULL IS NOT NULL

Blimey MM if I was a snake oil brewer I'd want you leading my sales force! Whether you're on the right track or not, it's a great story!
Have a look...
^{“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, 2013 at 9:37 am

#1636692
RE: Find the first record for each month

select
backup_start_date as [Date],
((f.backup_size/1024)/1024) as [Database Size],
((f.file_size/1024)/1024) as [File Size],
RANK() OVER (PARTITION BY backup_start_date ORDER BY backup_start_date) AS Rank,
rn = ROW_NUMBER() OVER (PARTITION BY MONTH(backup_start_date) ORDER BY backup_start_date DESC)
from...
^{“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, 2013 at 8:55 am

#1636678
RE: Handle error record from User Defined Function

ssskumar4u (7/29/2013)
Hi Chris,
Thanks for your response.
It is the case that based on pattern matching, we have to retrieve the index number.
If pattern matching fails,then identify the error record...
^{“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, 2013 at 8:47 am

#1636673
RE: CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

margarett.hance 40946 (7/29/2013)
select * from data where Desc like '%CA%'
The OP is now selling real estate in Argentina, 3 1/2 years later.
Margarett, if the leftmost part of the Desc column...
^{“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, 2013 at 8:32 am

#1636663
RE: Update all but last two rows of each category

-- Stare & Compare: check it's likely to work
;WITH Updater AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY category ORDER BY creation_date DESC)
FROM MyTable
)
SELECT *
FROM Updater
WHERE rn > 2
--...
^{“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, 2013 at 8:16 am

#1636656
RE: How to update data in a table having primary key - foreign key link with another table

Sean, there are more details here.
^{“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, 2013 at 7:43 am

#1636640
RE: Query really slow with eager spool

This thread is six years old.
^{“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, 2013 at 7:41 am

#1636639

Viewing 15 posts - 4,261 through 4,275 (of 10,143 total)