ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,451 through 9,465 (of 10,143 total)

RE: Query Proble (From Pravin)

Like this...
SELECT
SUM(Amount) AS Amount_1 ,
SUM(CASE WHEN Column_1 = 2 AND Column_2 = 4 THEN Amount ELSE 0 END) AS Amount_2
FROM xyz
WHERE Column_1 = 2
AND Column_2 = 4
AND Column_3 =...
^{“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

October 17, 2008 at 7:05 am

#886328
RE: Finding NULL values

Karthick,
1. what are you planning to do once you've found a null and stopped "the process"?
2. what is the process for? Is it for finding nulls?
^{“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

October 17, 2008 at 6:18 am

#886300
RE: CASE Statements

This is almost certainly easier to test, and quite possibly quicker, if you expand it out into an onion select:
[font="Courier New"]SELECT LagDays, CASE
WHEN LagDays BETWEEN 0 AND 3...
^{“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

October 17, 2008 at 6:14 am

#886295
RE: CASE Statement within a CASE

Using ISNULL?
[font="Courier New"]SELECT m.MonitoredEntityCode,
   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_1' THEN m.NumericResult END AS 'EMISSION_POINT_1',
   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_2' THEN m.NumericResult END AS 'EMISSION_POINT_2',
   CASE WHEN m.MonitoredLocationCode = 'EMISSION_POINT_3'...
^{“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

October 17, 2008 at 3:37 am

#886240
RE: Removing partially duplicate records in SQL

Did some work for a mailing shop some years ago, we used a method like this for the same thing:
[font="Courier New"]DROP TABLE #Test
CREATE TABLE #Test (RECID INT, COL1 CHAR(1), COL2...
^{“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

October 15, 2008 at 8:54 am

#885289
RE: Temporary table with dynamic sql

Madhivanan (10/15/2008)
Chris Morris (10/15/2008)
That's interesting, I also assumed it took a new connection.
If you create the temp table before executing the string, then (as Barry's code shows) the 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

October 15, 2008 at 5:23 am

#885122
RE: Insert Row Problem

You're welcome, barcode, many 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

October 15, 2008 at 4:24 am

#885109
RE: Insert Row Problem

Try this, barcode:
[font="Courier New"]SELECT c.CONTAINERID, '*** FULL CONTENT ***', 7, c.CONTAINERORIGINID, 1, c.COMPANYID, c.SUBCOID, 1, c.DEPT, c.LEVELID, 1
FROM CONTAINERS c
LEFT JOIN INDEXDATA i ON i.CONTAINERID = c.CONTAINERID AND i.INDEXFIELD1...
^{“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

October 15, 2008 at 3:54 am

#885095
RE: Temporary table with dynamic sql

That's interesting, I also assumed it took a new connection.
If you create the temp table before executing the string, then (as Barry's code shows) the table is visible 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

October 15, 2008 at 2:59 am

#885082
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Richard Fryar (10/14/2008)
Taking the basic idea posted by Chris Morris (page 2 of this thread) and modifying the string build part gets the following. If there are duplicate properties...
^{“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

October 15, 2008 at 2:11 am

#885066
RE: what does it mean "SELECT ... FROM .... WHERE X NOT IN (8)"

I use this handy gadget:
http://extras.sqlservercentral.com/prettifier/prettifier.aspx
Cheers
ChrisM
^{“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

October 15, 2008 at 2:08 am

#885065
RE: Start and End Date Groupings

Hi Debbie, as Peso points out, your best bet is a clustered index on person + startdate.
This generates your new column. Personally, I'd normalise that into two columns, but 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

October 14, 2008 at 4:53 am

#884521
RE: Start and End Date Groupings

Debbie, how about posting a chunk of sample data?
^{“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

October 14, 2008 at 3:36 am

#884495
RE: Start and End Date Groupings

Debbie Edwards (10/14/2008)
I think Im doing something very wrong.... :crazy:
I got the first example to bring back data but I dont understand the results. The 2nd example worked also but...
^{“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

October 14, 2008 at 2:43 am

#884472
RE: Start and End Date Groupings

Hi Debbie
The "running total" update statement - the one with the variables in it - can be a bit quirky. It's best if the data is physically ordered in 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

October 14, 2008 at 2:41 am

#884470

Viewing 15 posts - 9,451 through 9,465 (of 10,143 total)