ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,646 through 3,660 (of 10,143 total)

RE: SQL Query

balu.arunkumar (2/10/2014)
Hi This part shows error.
/*------------------------
SELECT*, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM#sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which...
^{“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 10, 2014 at 6:37 am

#1687510
RE: Please help to sort this

This is the easy way out!
DROP TABLE #COLORS
CREATE TABLE #COLORS (COLORS VARCHAR(50))
INSERT INTO #COLORS
SELECT '1 - Green = FHG' UNION ALL
SELECT ' 2 - 3< Amber <10' UNION ALL
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

February 10, 2014 at 6:31 am

#1687507
RE: INSERT INTO, EXCLUDING ONE COLUMN

Can you provide us with a brief description of the end to end process?
^{“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 10, 2014 at 2:04 am

#1687451
RE: Are the posted questions getting worse?

GilaMonster (2/7/2014)
jasona.work (2/7/2014)
ChrisM@Work (2/7/2014)
Anybody else seeing a kinky image of Koen, Gail and Grant wondering around without their tshirts?
/Using my best George Takei imitation
Ooh My...
😉
At least he's not imagining us...
^{“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 7, 2014 at 8:22 am

#1687090
RE: Count NULL columns

karthik M (2/7/2014)
VALUES (t1), (t2), (t3), (t4))
I have 119 columns in the actual table. Do I need to use all those names in VALUES clause?
You could use dynamic SQL,...
^{“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 7, 2014 at 7:45 am

#1687059
RE: Are the posted questions getting worse?

Anybody else seeing a kinky image of Koen, Gail and Grant wondering around without their tshirts?
^{“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 7, 2014 at 7:28 am

#1687043
RE: I need to write a query using a join

SELECT t1.* --, t2.*
FROM #TEMP t1
LEFT JOIN #TEMP t2 ON t2.LNAME = t1.LNAME AND t2.DriverID <> t1.DriverID
WHERE t1.Disposition = 'Guilty'
AND t2.DriverID IS NULL
^{“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 7, 2014 at 7:25 am

#1687042
RE: INSERT INTO, EXCLUDING ONE COLUMN

hoseam (2/7/2014)
here's the idea behind Dynamic SQL:
The user will be passing table name to make changes to it...
Data changes, or table structure changes?
^{“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 7, 2014 at 7:18 am

#1687037
RE: Count NULL columns

SELECT t.eno, d.NullCount
FROM test t
CROSS APPLY (
SELECT NullCount = 4 - COUNT(col) FROM (VALUES (t1), (t2), (t3), (t4)) d (col)
) d
WHERE d.NullCount > 2
^{“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 7, 2014 at 7:08 am

#1687027
RE: INSERT INTO, EXCLUDING ONE COLUMN

hoseam (2/7/2014)
Without listing the columns??
No - as Lowell said, you will need a column list on the SELECT side.
Once you've been bitten a few times, you will realise it's...
^{“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 7, 2014 at 6:56 am

#1687020
RE: UNABLE TO RETRIEVE data while using between operator

batesview (2/7/2014)
This can really reach out to bite you when dates are involved, again sort of obvious but so easy to forget/overlook
It certainly can - but Jeff Moden has most...
^{“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 7, 2014 at 6:49 am

#1687019
RE: UNABLE TO RETRIEVE data while using between operator

Thanks. There are tons of these little bitty helpful tricks and you will find a good many are covered in Spackle articles by Jeff Moden.
^{“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 7, 2014 at 1:52 am

#1686930
RE: CASE Statement multiple aurgument

It is possible for a CASE block to change the number of rows returned by SELECT with DISTINCT.
Find a filter (a WHERE clause) which returns only rows which 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

February 6, 2014 at 7:04 am

#1686585
RE: Multiples address lines on a single row. Pivot?

Danster (2/5/2014)
...
What I want is this:
RowID, 1stAddr1, 1stAddr2, 1stAddr3, 1stAddr4, 1stPostcode, 2ndAddr1, 2ndAddr2, 2ndAddr3 (etc)
...
What's the maximum number of addresses per RowID?
^{“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 6, 2014 at 2:30 am

#1686490
RE: Populate Dynamic Table Name in T-SQL Cursor

It's not often that such a great example of when not to use cursors comes up. This appears to be a very straightforward query:
DECLARE @start DATETIME
SELECT @start = MAX(SessionEndDate) 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 6, 2014 at 2:07 am

#1686483

Viewing 15 posts - 3,646 through 3,660 (of 10,143 total)