ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,436 through 6,450 (of 10,143 total)

RE: Aggregating monthly sales data by column

Usman Butt (12/1/2011)
ChrisM@Work (12/1/2011)
Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:
SELECT
ItemCode,
Column1 = MAX(CASE WHEN YearShipped = 2010...
^{“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

December 1, 2011 at 3:38 am

#1415112
RE: Aggregating monthly sales data by column

Use a crosstab, preaggregating the raw table with either a CTE or a derived table. Here's the derived table version:
SELECT
ItemCode,
Column1 = MAX(CASE WHEN YearShipped = 2010 AND MonthShipped =...
^{“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

December 1, 2011 at 2:43 am

#1415094
RE: need help with query to select with subquery

Denise McMillan (11/30/2011)
I got it to work by joining just on the left 4 charracters of the number in the subquery.
select number, avalue, (select x.nvalue from valuestbl as...
^{“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

November 30, 2011 at 10:26 am

#1414836
RE: Complex View using repeating groups

Hey Jon, thanks loads for posting up sample data - it took a moment to tweak the script but no worries. Try this:
;WITH CTE AS (
SELECT
ID,
ProjectID,
rowDescription,
[Year],...
^{“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

November 30, 2011 at 7:54 am

#1414694
RE: Complex View using repeating groups

Hi Jon, welcome to the forum.
Can you post up some sample data? There's a link in my sig which shows how to do this - well worth a read for...
^{“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

November 30, 2011 at 6:57 am

#1414642
RE: T-SQL , Stored Procedures & Performance Tuning

alaguganesha1983 (11/30/2011)
T-SQL , Stored Procedures & Performance Tuning:
I need clear idea and good example for the above topic i mentioned.
help me with this please.
Acquiring sufficient experience, knowledge and understanding of...
^{“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

November 30, 2011 at 2:31 am

#1414504
RE: getting multivalue from a function

ashkan siroos (11/29/2011)
I have Changed my query to avoid cross apply because of the efficiency ...
CROSS/OUTER APPLY can be surprisingly efficient - read the excellent articles written by Paul White,...
^{“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

November 30, 2011 at 2:09 am

#1414491
RE: getting multivalue from a function

FROM
.
.
.
CROSS APPLY (
SELECT poi.POIDescription, rn = ROW_NUMBER() OVER(ORDER BY r.RequestCode desc)
FROM DMSRequestDocListTbl rdl
INNER JOIN DMSRequestTbl r
ON rdl.RequestCode = r.RequestCode
AND r.RequestTypeID in ( 8,9)
AND r.DocRevID = @DocRevID...
^{“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

November 29, 2011 at 10:02 am

#1414225
RE: getting multivalue from a function

Try using Cross/Outer Apply.
You can easily convert the content into an inline TVF.
^{“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

November 29, 2011 at 6:45 am

#1414021
RE: DELETE !!! soft ? or Hard ?

Using a temp table containing the PK's of the target table looks like the best option to me:
SELECT sec_no
INTO #sec_no_to_delete
FROM sec_returns m
WHERE NOT EXISTS (
SELECT 1
FROM sec_returns
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

November 29, 2011 at 3:59 am

#1413899
RE: Today's Random Word!

Evil Kraig F (11/22/2011)
SQLRNNR (11/22/2011)
riddle
Question Mark
Mark is innocent!
^{“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

November 29, 2011 at 3:25 am

#1413891
RE: Order by, displaying results out of order.

Jorge Lopes (11/28/2011)
There is a slight problem in the third Select statement you have to put 2 spaces between Balanço and ANO
SELECT 'Balanço[space][space]ANO'
CREATE TABLE #desc
( NAME VARCHAR(100))
INSERT INTO #desc
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

November 28, 2011 at 10:30 am

#1413550
RE: Order by, displaying results out of order.

DROP TABLE #desc
CREATE TABLE #desc
( NAME VARCHAR(100))
INSERT INTO #desc
SELECT 'Balanço- Activo não corrente' UNION ALL -- note changed position of hyphen
SELECT 'Balanço - Autonomia Financeira' UNION ALL
SELECT 'Balanço ANO'
SELECT
NAME,...
^{“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

November 28, 2011 at 4:01 am

#1413333
RE: Order by, displaying results out of order.

Character position 9 of two of your strings is a hyphen. There are ways to get around this, for instance you could remove the hyphen and a white space using...
^{“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

November 28, 2011 at 3:53 am

#1413331
RE: kind Of a struck with this problem. What could be the problem

Run this query, describe the result set - better still, post the result set:
SELECT YEAR(OrderDate) AS OrderYear,
AVG(TotalDue) AS TotalDue
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
^{“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

November 25, 2011 at 7:42 am

#1412991

Viewing 15 posts - 6,436 through 6,450 (of 10,143 total)