ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,071 through 5,085 (of 10,143 total)

RE: Query to get rows if value contains result of another query

winmansoft (3/7/2013)
ChrisM@Work (3/7/2013)
yes - do this instead:
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'seh,jyo,mhf'
SELECT *
INTO #Elements
FROM dbo.DelimitedSplit8K (@Parameter,',')
SELECT m.*
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

March 7, 2013 at 5:17 am

#1594389
RE: Grouping records by time interval

SELECT MAX(Ticket_Closed_DateTime)
FROM Tussen_Tickets
-- returns '2013-01-17 10:22:00.000'
^{“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

March 7, 2013 at 2:46 am

#1594352
RE: How to Convert Semi colon Separated Values into Column

DECLARE @ItemCod VARCHAR(8000) = 'T1;T2;T3;'
DECLARE @ItemName VARCHAR(8000) = 'Pencil Box;Eraser;Mouse Pad;'
DECLARE @Amount VARCHAR(8000) = '1900;2000;8900;'
SELECT
c.ItemNumber,
ItemCod = c.Item,
ItemName = n.Item,
Amount = a.Item
FROM dbo.DelimitedSplit8K(@ItemCod,';') c
INNER JOIN...
^{“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

March 7, 2013 at 1:56 am

#1594333
RE: IN Vs INNER JOIN

purushottam2 (3/7/2013)
Which one is more performance oriented query?
1. SELECT 1 FROM table1 WHERE Id IN (SELECT Id FROM table2)
2. SELECT 1 FROM table1 t1 INNER JOIN table2 t2 ON t1.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

March 7, 2013 at 1:41 am

#1594327
RE: Performing strange conditional COUNT

This is called "Islands and Gaps". An island is a set of rows where the dates are consecutive, bounded by rows with non-consecutive dates. Jeff Moden has a great Spackle...
^{“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

March 7, 2013 at 1:38 am

#1594325
RE: Query to get rows if value contains result of another query

winmansoft (3/6/2013)
Thank you
I found that i can do this
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'seh,jyo,mhf'
DECLARE @Elements TABLE
(
...
^{“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

March 7, 2013 at 1:08 am

#1594322
RE: The Beer Cooler Thread

Revenant (3/6/2013)
I am calling it a day. I will head to Black Raven http://blackravenbrewing.com/ to down a pint of their Totem Ale (or two).
BTW, the linked page gives you a...
^{“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

March 7, 2013 at 1:01 am

#1594320
RE: Grouping records by time interval

geert.de.vylder (3/6/2013)
ChrisM@Work (3/6/2013)
I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me...
^{“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

March 6, 2013 at 9:32 am

#1594043
RE: Grouping records by time interval

geert.de.vylder (3/6/2013)
ChrisM@Work (3/6/2013)
I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me...
^{“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

March 6, 2013 at 9:12 am

#1594030
RE: Splitting a string into fields

If there really are always four elements, then you could use PARSENAME (look it up in BOL):
;WITH SampleData AS (
SELECT MyString = 'Scrap : Part Assembly : Surface Defects :...
^{“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

March 6, 2013 at 9:10 am

#1594029
RE: Grouping records by time interval

geert.de.vylder (3/6/2013)
ChrisM@Work (3/6/2013)
I meant in terms of potentially reducing the rowcount.
Let's call a set from this table containing the same day, all of the intervals, and the same Event_Channel,...
^{“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

March 6, 2013 at 8:54 am

#1594021
RE: Grouping records by time interval

geert.de.vylder (3/6/2013)
ChrisM@Work (3/6/2013)
This data set obviously isn't what you were expecting to see, even though it exactly meets your requirements. What would you like to do with it?
I have 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

March 6, 2013 at 8:24 am

#1594007
RE: Data Issue

Ratheesh.K.Nair (3/6/2013)
Thanks Chris for the reply.
Problem i am facing is When selecting I am getting same data while using symbol F or F as search criteria both are giving 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

March 6, 2013 at 7:41 am

#1593965
RE: Grouping records by time interval

geert.de.vylder (3/6/2013)
...But the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk are all NULL or zero...
That sounds about right. The matrix table contains 1,180,224 rows (all dates and event channels...
^{“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

March 6, 2013 at 7:35 am

#1593958
RE: Cursor.. cmds don’t seem to update until everything has finished

Try using this instead of print, which is buffered:
raiserror ('My Print Statement', 10,1) with nowait
Your inserts may be uncommitted until the cursor loop has completed, in which case you could...
^{“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

March 6, 2013 at 6:41 am

#1593917

Viewing 15 posts - 5,071 through 5,085 (of 10,143 total)