ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,236 through 2,250 (of 10,143 total)

RE: Multiple records on one row..

-- expand this repeating sequence to cover the maximum number of sales by customer
SELECT
CustomerID,
SaleDate1 = MAX(CASE WHEN rn = 1 THEN SaleDate ELSE NULL END),
Location1 = MAX(CASE WHEN rn...
^{“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 20, 2015 at 2:23 am

#1821279
RE: Can't use >= with setInt

serviceaellis (8/19/2015)
Currently working with
DECLARE @yr AS int;
SET @yr = 2015;
which is used in
WHERE (YEAR(EndDate) = @yr)
...
^{“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 20, 2015 at 2:11 am

#1821278
RE: Find working time of each employee

dudekula.kareemulla (8/19/2015)
Hi Thanks for your reply ,,,,
I got your point but here situation may be swipe-in and swipe-out machines are separate and
new joiners may be confused and done instead of...
^{“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 20, 2015 at 2:00 am

#1821276
RE: Find working time of each employee

Jeff Moden (8/20/2015)
Really, really looks like and sounds like homework. Even if it's not, you should at least try. And rules 4, 5, and 6 create an oxymoron...
^{“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 20, 2015 at 1:57 am

#1821275
RE: Long running Select Query

coolchaitu (8/20/2015)
Dear Chris sir,
There is remote join in the query you posted. I came across an article that says Remote join doesn’t work well on SQL server 2005. Ours is...
^{“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 20, 2015 at 1:46 am

#1821274
RE: how to overcome from the tempdb spill over ?

In case this spills past UK time, this is what I have so far:
DECLARE @lobid INT = 3,@LineOfBusiness VARCHAR(50)='ECN'
DECLARE @CmsContractID VARCHAR(MAX)='H9285'
SELECT DISTINCT D.DeterminantID AS BEN_CAT_ID
,DENSE_RANK() OVER...
^{“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, 2015 at 9:02 am

#1821091
RE: how to overcome from the tempdb spill over ?

Eric M Russell (8/19/2015)
squvi.87 (8/19/2015)
...
Yes it produces correct result only, it took 30 mins. If I remove the [SplitString] part from the query it took only 5-10 seconds.
OUTER APPLY (
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 19, 2015 at 8:59 am

#1821088
RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

serviceaellis (8/19/2015)
ChrisM@Works,
The columns except the last four columns which are:
MembershipTypeIdEndDateInvoiceNumberPersonMembership_PersonId
Thanks. What about the content? What do the results tell 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

August 19, 2015 at 8:38 am

#1821075
RE: how to overcome from the tempdb spill over ?

Luis Cazares (8/19/2015)
Have you considered changing your splitter function to a faster one?
Here's probably the fastest available: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for your help Luis - there's plenty to go round in this...
^{“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, 2015 at 8:13 am

#1821060
RE: how to overcome from the tempdb spill over ?

Here's the first part. Run it, report back with your findings. Are there loads of dupes in the output?
IF OBJECT_ID('tempdb..#DeterminantAttributeValue') IS NOT NULL DROP TABLE #DeterminantAttributeValue
SELECT
DAV.DeterminantAttributeID, DAV.TypeID,
Value1Part =...
^{“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, 2015 at 7:35 am

#1821042
RE: how to overcome from the tempdb spill over ?

squvi.87 (8/19/2015)
ChrisM@Work (8/19/2015)
Fantastic, thanks.
1. Spilling sorts occur when the number of rows received by the sort operator is more than the estimate. If you examine the property sheet of 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

August 19, 2015 at 7:24 am

#1821037
RE: how to overcome from the tempdb spill over ?

Fantastic, thanks.
1. Spilling sorts occur when the number of rows received by the sort operator is more than the estimate. If you examine the property sheet of the sort operator,...
^{“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, 2015 at 7:11 am

#1821028
RE: how to overcome from the tempdb spill over ?

Can you post the entire query please, and the definition of the tvf "splitstring".
Cheers.
^{“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, 2015 at 6:17 am

#1820996
RE: Find working time of each employee

;WITH ProcessedData AS (
SELECT EmpName, SwipeDate, MIN_SwipeTime = MIN(SwipeTime), MAX_SwipeTime = MAX(SwipeTime), SwipeType, grp
FROM (
SELECT EmpName, SwipeDate, SwipeTime, SwipeType, grp = rn1-rn2
FROM (
SELECT
EmpName, SwipeDate, SwipeTime, SwipeType,
rn1 = 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

August 19, 2015 at 3:14 am

#1820964
RE: Display the output of table in given format

dineshhp (8/19/2015)
Hi Expert,
I have a table Test123 having three column EmpID,AttribName,AttribValue.
Please run the below query to generate table structure and data.
Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))
insert into Test123 values(1,'Name','X')
insert...
^{“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, 2015 at 1:53 am

#1820956

Viewing 15 posts - 2,236 through 2,250 (of 10,143 total)