ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,666 through 7,680 (of 10,143 total)

RE: Character type number convert

Paul White NZ (7/14/2010)
Eugene Elutin (7/14/2010)
Paul, have you seen my post #951618 (using UDF), bottom of page 3?
Do you think this approach is viable for most of scenarious?...
^{“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

July 14, 2010 at 7:24 am

#1192790
RE: Optimizing query

Comment out this section, it isn't referenced in the SELECT:
LEFT OUTER JOIN
( SELECT p.EntityCode,
SUM(t.[On-Sale Rate]) AS OnSale,
SUM(t.[Off-Sale Rate]) AS OffSale
FROM aztec.dbo.ProductTaxRules p, aztec.dbo.TaxRules t
WHERE (p.TaxRule1 = t.[Index No]...
^{“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

July 14, 2010 at 6:59 am

#1192766
RE: Name,Name1 --Name1,Name2

/*
i need result like
------------------------------
sriram Thiru
Thiru Hari
Hari KSS
----------------
*/
DROP table #emp
create table #emp
(
eno int,
ename varchar(20),
designation varchar(20),
report_to int
)
insert into #emp values (1000,'Sriram','STE',100)
insert into #emp values (100,'Thiru','PL',10)
insert into #emp values (10,'Hari','PM',5)
insert into #emp values...
^{“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

July 14, 2010 at 5:00 am

#1192668
RE: Between Statment only for current day

jon pill (7/14/2010)
SELECT min(upvolume),min(downvolume),Max(upvolume),Max(downvolume)
FROM
(SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/13/2010') as XDtable
WHERE TotalTrades
BETWEEN
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000
AND
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH...
^{“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

July 14, 2010 at 4:44 am

#1192660
RE: Return distinct results where multiple rows have different values

/*
I need to return distinct OrderIDs where the following conditions are met:
• The order is just for Bananas (ie just the one row exists for an OrderID)
• The order is...
^{“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

July 14, 2010 at 4:22 am

#1192656
RE: Today's Random Word!

To have and to hold your cake and eat 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

July 13, 2010 at 9:49 am

#1192189
RE: Character type number convert

Eugene Elutin (7/13/2010)
Looks like there is no simple way (set-based) to do it in SQL2005.
DBCC GetGapsInSpec
Depends on the fillfactor of the spec - if it's less than 50%, you have...
^{“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

July 13, 2010 at 9:46 am

#1192186
RE: Today's Random Word!

The Mamas and the Papas
^{“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

July 13, 2010 at 9:41 am

#1192178
RE: Character type number convert

Paul White NZ (7/13/2010)
Chris Morris-439714 (7/13/2010)
It's great ... but...the order of the output doesn't match the order specified by the OP 😛
Yeah, but the OP got it wrong!
TWICE! 😀
^{“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

July 13, 2010 at 9:38 am

#1192173
RE: Character type number convert

Paul White NZ (7/13/2010)
Eugene Elutin (7/13/2010)
I knew it wouldn't work, however it does look nice (as result of creative thinking
It does indeed look very pretty 😎
I'm going with the upgrade...
^{“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

July 13, 2010 at 9:32 am

#1192161
RE: not in operator with multiple columns

thava (7/13/2010)
wow that's great simple and easy chris
i got it thanks for your reply
Gosh :blush: you're welcome, 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

July 13, 2010 at 9:17 am

#1192132
RE: Character type number convert

Thanks Paul, that was interesting - if challenging - reading. I've not yet used rCTEs to resolve hierarchies in anger. This would be a good place to start, given 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

July 13, 2010 at 9:10 am

#1192117
RE: not in operator with multiple columns

Have you tried a left join?
FROM BatchStock b
LEFT JOIN #Tmp t ON t.slno = b.slno AND t.itemid = b.itemid
WHERE b.PurId = @PurID AND b.DocRef = @DocRef
AND t.slno IS...
^{“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

July 13, 2010 at 9:05 am

#1192105
RE: Character type number convert

Paul White NZ (7/13/2010)
skcadavre (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Chris Morris-439714 (7/13/2010)
Or 1.1.3, even...
I think the OP specified the location of that.
He did.
I took Chris' post 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

July 13, 2010 at 7:16 am

#1192012
RE: Character type number convert

Paul White NZ (7/13/2010)
skcadavre (7/13/2010)
You killed both solutions 😛
It's a knack 😉
I'm thinking CHARINDEX, split on the period and sort that way?
That would work (and Chris would provide a recursive...
^{“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

July 13, 2010 at 6:25 am

#1191957

Viewing 15 posts - 7,666 through 7,680 (of 10,143 total)