ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,176 through 5,190 (of 10,143 total)

RE: Complex SQL QUERY with DateDIFF

SET DATEFORMAT DMY
CREATE TABLE #TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)
insert into #temp select 1, '01551 ', '20/02/2013 17:41:45.000' --
insert into #temp select 2, '01551 ', '20/02/2013 17:41:45.900'
insert into...
^{“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

February 25, 2013 at 9:47 am

#1590855
RE: procedure performance

Thanks. The plans for each of the MERGE statements are very similar. Here's what I suggest you do; pick one of the statements and work with it in a query...
^{“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

February 25, 2013 at 8:01 am

#1590818
RE: strip out unmatched text with in a group

;WITH CTEAgg AS (
SELECT
GroupingID,
GroupItemCount = COUNT(*) OVER (PARTITION BY GroupingID),
[Description]
FROM #TEMP
)
SELECT DISTINCT t.GroupingID, Item
FROM #TEMP t
OUTER APPLY (
SELECT Item = STUFF(
(
SELECT ','+Item
FROM (
SELECT Item, GroupItemCount, [Description]
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

February 25, 2013 at 7:57 am

#1590813
RE: procedure performance

sathiyan00 (2/25/2013)
hi ,
i attached actual execution plan of my proc..please find attachment(results.txt)...
Can you save it as a .sqlplan file and post, please?
^{“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

February 25, 2013 at 6:52 am

#1590791
RE: procedure performance

Can you please post the actual execution plans of all three merge statements.
^{“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

February 25, 2013 at 5:52 am

#1590775
RE: strip out unmatched text with in a group

Sure, it's in here[/url]. The followup discussion is well worth a read.
^{“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

February 25, 2013 at 5:13 am

#1590761
RE: strip out unmatched text with in a group

Can you finish it from here?
;WITH CTEAgg AS (
SELECT
GroupingID,
GroupItemCount = COUNT(*) OVER (PARTITION BY GroupingID),
[Description]
FROM #TEMP
)
SELECT GroupingID, Item
FROM (
SELECT
GroupingID, GroupItemCount, [Description], Item
FROM CTEAgg
CROSS APPLY...
^{“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

February 25, 2013 at 5:05 am

#1590756
RE: ntile or something similar

SELECT *
FROM (
SELECT *,
Chunk = 1+(ROW_NUMBER() OVER(ORDER BY stress)-1)%4
FROM #stress
) d
ORDER BY Chunk, Stress
^{“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

February 25, 2013 at 4:31 am

#1590738
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

L' Eomot Inversé (2/22/2013)
mister.magoo (2/22/2013)
And here is the code (with notes removed for brevity) and the changes underlined.
Sorry if I have made a mistake, but I think this is valid.
Looks...
^{“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

February 22, 2013 at 7:53 am

#1590370
RE: Update query for my new boss in my new job I am struggling with

dwain.c (2/22/2013)
Have you tried the solution I suggested?
I'm thinking it will do what you need.
I hope I didn't confuse you by putting Table2 into the CTE.
Looks good to me. Same...
^{“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

February 22, 2013 at 3:56 am

#1590274
RE: Update query for my new boss in my new job I am struggling with

I'm not so sure either - the tables might look like this:
DROP TABLE #Table1
SELECT
*
INTO #Table1
FROM (
SELECT Column1 = 'microsoft outlook 2007,microsoft 2007 outlook,microsoft out look 2007,microsoft 2007 office,microsoft office...
^{“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

February 22, 2013 at 1:13 am

#1590215
RE: Updating a % of cells in a Column

wafw1971 (2/21/2013)
I have 4 million lines of code....
Four million lines of code would keep you quiet for a while! I guess you mean four million rows in a table?
Read...
^{“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

February 21, 2013 at 8:34 am

#1589998
RE: T SQL statement issue

chandrakant_gaurav (2/21/2013)
Its embarassing :blush: . Thank you!!
Heh - don't be embarrassed, you almost got there. Thanks loads for posting a sample script.
^{“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

February 21, 2013 at 7:06 am

#1589942
RE: T SQL statement issue

So nearly there...
SELECT
calldate,
Overall= SUM(talktime),
A_Talktime= SUM(case when ID = 1 then talktime END),
B_Talktime= SUM(case when ID = 2 then talktime END),
C_Talktime= SUM(case when ID = 3 then talktime END)
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

February 21, 2013 at 6:25 am

#1589917
RE: Creating a Ref Number usning letters and numbers.

Try this, Wayne:
USE Occupancy
;WITH BookingsToUpdate AS (
SELECT
*,
Seq = ROW_NUMBER() OVER (ORDER BY Booking_Skey)
FROM Bookings
)
SELECT
Seq,
Booking_Skey,
BookingNumber = 'B' + RIGHT('0000000' + CAST(Seq AS VARCHAR(8)),8),
AnotherBookingNumber = 'B' + RIGHT('0000000'...
^{“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

February 21, 2013 at 3:21 am

#1589865

Viewing 15 posts - 5,176 through 5,190 (of 10,143 total)