ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,921 through 7,935 (of 10,143 total)

RE: Today's Random Word!

Continuously-variable transmission
^{“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

May 14, 2010 at 2:12 am

#1167349
RE: Are the posted questions getting worse?

There's a rumour circulating about a pole-dancing chipmunk
^{“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

May 14, 2010 at 2:10 am

#1167347
RE: Today's Random Word!

mbricker-600840 (5/13/2010)
tosscrosby (5/13/2010)
tyromancy
I wonder how that works?
My wife is quite the lover of cheese. I wonder if that was involved in her choice of me as a mate.
We...
^{“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

May 13, 2010 at 10:02 am

#1167022
RE: calculate dates between rows

There might be some mileage in this too:
SET DATEFORMAT DMY
DROP TABLE #Temp
CREATE TABLE #Temp (Art CHAR(1), [Status] VARCHAR(3), [Date] DATE)
INSERT INTO #Temp (Art, [Status], [Date])
SELECT 'A', 'In', '10/10/2009' UNION...
^{“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

May 11, 2010 at 6:07 am

#1165478
RE: Divide by zero error when using CASE statement

turkey-500673 (5/10/2010)
Thanks for the great solutions, but none of them worked. I'm going to take the numerator and make 1 field and the divisor another field in a temp 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

May 11, 2010 at 2:00 am

#1165386
RE: PIVOT UNPIVOT IN SQL2005

mukherjee.subhro (5/10/2010)
...the orderyear field the O/P is coming like cnt2007,cnt2008.... like that;
I want to omit this cnt portion, if you have any suggestion then please sent me by tomorrow.
I think...
^{“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

May 10, 2010 at 7:46 am

#1164983
RE: PIVOT UNPIVOT IN SQL2005

mukherjee.subhro (5/10/2010)
Yea !
I mean what you have understood i.e. stripping the first 3 alphabet and taking only the numeric portion (i.e. year) . The output is in...
^{“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

May 10, 2010 at 7:15 am

#1164961
RE: Today's Random Word!

Grimouse
^{“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

May 10, 2010 at 7:01 am

#1164950
RE: PIVOT UNPIVOT IN SQL2005

mukherjee.subhro (5/10/2010)
Hello Sir
Please look at the image file that I have send with this reply,I have run as per your suggestion removing the typecast and it...
^{“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

May 10, 2010 at 6:57 am

#1164947
RE: PIVOT UNPIVOT IN SQL2005

Try it without the CAST, change
SELECT empid,CAST(orderyear AS INT)AS orderyear,numorders
to
SELECT empid, orderyear, numorders
^{“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

May 10, 2010 at 5:46 am

#1164914
RE: Today's Random Word!

Tom.Thomson (5/10/2010)
RBarryYoung (5/9/2010)
tosscrosby (5/6/2010)
I apologize for my morbid-ness, but you have to admit that you probably learned a new word (I sure did!).
For something totally different:
Callipygian
(how Jeff talks...
^{“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

May 10, 2010 at 4:41 am

#1164906
RE: Divide by zero error when using CASE statement

The simplest way is to avoid the calculation altogether;
c_new_PP = CASE WHEN oe_line.unit_price = 0 OR inventory_supplier.cost = 0 THEN 0 ELSE
(
(
(100 * inventory_supplier.cost * 1.1)
/ (
100.00000001 - (oe_line.unit_price...
^{“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

May 10, 2010 at 4:40 am

#1164904
RE: CTE and Self Join with NULL Values

There may be some mileage in this:
;WITH EnrollmentRollup AS (
SELECT
StudentID,
Summary = SUM(CASE ActionClass WHEN 'E' THEN 1 WHEN 'W' THEN -1 ELSE 0 END),
Actions = COUNT(*)
FROM #Enrollment
WHERE...
^{“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

May 7, 2010 at 10:15 am

#1164238
RE: Today's Random Word!

Paul White NZ (5/7/2010)
Tom Brown (5/7/2010)
It's the first time I've ever had to queue to vote.
I'm quite surprised no-one has challenged some of the very close results yet because...
^{“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

May 7, 2010 at 9:24 am

#1164191
RE: Convert nvarchar to datetime

Abrar Ahmad_ (5/7/2010)
[font="Verdana"]Yep
I have a nvarchar field with a value '20100407132212' every database practitioner knows this format very well, but un-fortunately implicit conversion from this nvarchar data column to DATETIME...
^{“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

May 7, 2010 at 7:32 am

#1164089

Viewing 15 posts - 7,921 through 7,935 (of 10,143 total)