ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,781 through 3,795 (of 10,143 total)

RE: Stored Procedure to remove all records that don't have max FileID

Truncating the table before loading new data would be more efficient - is there some reason why you chose the CTE 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

January 7, 2014 at 7:34 am

#1678077
RE: Stored Procedure to remove all records that don't have max FileID

petr.caslavka (1/7/2014)
This will work great. thank you very much for your help,
Petr
Which option?
^{“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 7, 2014 at 7:10 am

#1678064
RE: SubProcedure (Subroutines) within Stored Procedure

Yes you can - stored procedures with input and output parameters can be nested within a calling stored procedure. Do you really need the code blocks configured as "subroutines"?
^{“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 7, 2014 at 7:08 am

#1678063
RE: Get Next Id with a MERGE

Something like this:
DECLARE @NextSeqId INT
SET @NextSeqId = dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()
;WITH CTEseriesnacionales AS (
SELECT NextSeqId = @NextSeqId + (ROW_NUMBER() OVER(ORDER BY RangoIni)-1),
*
FROM dbo.seriesnacionales
)
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T
USING CTEseriesnacionales AS S
ON (T.RANGE1 = S.RANGOINI )
...
^{“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 7, 2014 at 7:04 am

#1678060
RE: Complex Query. Please help

Working on a completely different principle, this will perform differently to Luiz' fine solution. Test them and assuming they return correct values, choose the fastest - on the assumption that...
^{“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 7, 2014 at 6:46 am

#1678053
RE: Begginer in SQL Procedures

A slightly different approach from the previous two, triggered by a gruelling telephone survey I was foolish enough to agree to participate in just a couple of weeks ago. There...
^{“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 7, 2014 at 2:09 am

#1677984
RE: DATEDIFF Statement help!

joey6401c (1/6/2014)
Thanks for the reply
I modified my query but it returns an error
Delcare @dt date
Set @dt = GETDATE()
select distinct v1.name 'Machine Name', v1. 'Username', t1.displayname 'Unlicensed Application', t1.installdate 'Install Date'...
^{“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 6, 2014 at 9:03 am

#1677788
RE: Max Function Output Mapping to get Description

waheed71 (1/6/2014)
...Let me explore this in more details...
That's the ticket!
Thanks for the feedback.
^{“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 6, 2014 at 8:34 am

#1677774
RE: Max Function Output Mapping to get Description

waheed71 (1/6/2014)
Dear Chris,
Thank you. Can you please give me example of below text.
Change the join column on my query to whichever MAX column fits your requirement.
Sure.
Change this:
INNER JOIN ST_SUPPLIER su...
^{“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 6, 2014 at 8:19 am

#1677768
RE: Max Function Output Mapping to get Description

waheed71 (1/6/2014)
Dear ChrisM / Dear Lowell,
Thanks for the prompt reply. I am really sorry about not able to communicate properly. So far I am unable to get required result.
I just...
^{“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 6, 2014 at 8:04 am

#1677762
RE: DATEDIFF Statement help!

If you haven't got a ~~timestamp~~ date/time column in your table, how can you tell if something is more than 7 days old?
Does your table have an assocoation with any...
^{“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 6, 2014 at 7:02 am

#1677740
RE: Max Function Output Mapping to get Description

Slightly different to Lowell's code and I'm not sure which is correct:
SELECT
d.TR_STOCKNO,
d.CT_STOCKSUMMARY,
d.PH_SUPPLIERCODE,
su.SU_SUPPLIERNAME,
d.PH_RAISEDDATE,
d.TR_DATE,
d.LASTPO,
d.LASTVENDOR,
d.LASTGRN,
d.Age
FROM (
SELECT
tr.TR_STOCKNO,
ct.CT_STOCKSUMMARY,
ph.PH_SUPPLIERCODE,
--SU_SUPPLIERNAME,
ph.PH_RAISEDDATE,
tr.TR_DATE,
MAX(ph.PH_RAISEDDATE) OVER (PARTITION BY tr.TR_STOCKNO) AS LASTPO,
MAX(ph.PH_SUPPLIERCODE) OVER (PARTITION BY tr.TR_STOCKNO) AS LASTVENDOR,
MAX(TR_DATE) OVER (PARTITION...
^{“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 6, 2014 at 6:53 am

#1677739
RE: please help me

ROW_NUMBER() on an outer SELECT will often return the results in the ROW_NUMBER() order, which could make your query equivalent to this:
SELECT rownumber, linknumber, savabegh
FROM (
SELECT
rownumber = ROW_NUMBER()...
^{“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 6, 2014 at 6:30 am

#1677731
RE: please help me

The splitter discussed here [/url]is shown to be far superior in performance terms to either of the two already listed oon this thread. Here's how you would use it to...
^{“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 6, 2014 at 3:31 am

#1677686
RE: Arthimatic overflowerror converting expression to data type datetime

If you know that DATEADD(s, nDateTime, '1970-01-01 00:00:00') converts seconds since 1970-01-01 to a datetime,
why not simply swap the faulty expression in your WHERE clause for the the expression...
^{“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 6, 2014 at 3:09 am

#1677674

Viewing 15 posts - 3,781 through 3,795 (of 10,143 total)