ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,706 through 9,720 (of 10,143 total)

RE: Not like '%%'

Vaseem, try it with a little extra data...
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows...
^{“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

August 1, 2008 at 5:49 am

#851794
RE: Not like '%%'

vaseem (8/1/2008)
Hi
Use this SQL
select * from temp222 where ','+skilltype+',' not like '%,c,%'
Thanks
Vaseem
This looks interesting Vaseem, would you mind elaborating with some sample data?
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

August 1, 2008 at 5:38 am

#851784
RE: how to update a rows of data using a function in a Stored Procedure

Atif Sheikh (8/1/2008)
As far as I underrstood ur problem, you are already doing the job.
I dont know why are you going for TOP 1/
Just run the two queries...
^{“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

August 1, 2008 at 5:31 am

#851777
RE: Matching recoreds

Not really, you've lost the derived table d.
If you're comparing values from different rows of a table, then you have to join the table in more than once. You're...
^{“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

August 1, 2008 at 5:15 am

#851763
RE: Comparing Data and getting results

Hi John
It's going to look something like this:
SELECT m.ProductID, m.ProductName, m.Price, t.Price
FROM MyPricingTable m
LEFT JOIN TheirPricingTable t ON t.ProductID = m.ProductID
WHERE t.Price - m.Price > 20
There's very little information...
^{“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

August 1, 2008 at 4:36 am

#851740
RE: Not like '%%'

You need to match the pattern at the beginning and end of the string, and in the middle, in different ways:
SET NOCOUNT ON
DROP table #Temp
CREATE TABLE #Temp (ID Int identity(1,1),Skilltype...
^{“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

August 1, 2008 at 4:29 am

#851736
RE: Matching recoreds

Untested, of course...
SELECT h.OrderID, h.OrderDate, h.address, c.address, p.cardno, c.cardno
FROM T_OrderHeader h
INNER JOIN T_Customers c ON c.CustomerID = h.CustomerID
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
INNER JOIN (SELECT h.OrderID, h.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

August 1, 2008 at 3:52 am

#851724
RE: Matching recoreds

As a separate query to the one above, or built into it? Also, card number is in the customer table, are you sure you want to use card number 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

August 1, 2008 at 3:34 am

#851714
RE: Cannot check vales returned from a function in a CASE statement

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

August 1, 2008 at 3:02 am

#851702
RE: Matching recoreds

Yes of course! Can you name the date column (and table) which you would like to use for this?
^{“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

August 1, 2008 at 2:59 am

#851701
RE: How to get Sequential ordering of data based on Ids Passed to IN Keyword

Hi Meeraj, apologies for the delay in replying to you.
Here's a script for creating a Numbers or Tally table, which you will see often on this forum.
IF EXISTS (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

August 1, 2008 at 2:54 am

#851697
RE: How to get Sequential ordering of data based on Ids Passed to IN Keyword

There's an answer in this, Meeraj:
SELECT * FROM [dbo].[uftSplitString] ('232,33,546,2,4,5,7,8,1091,223,3434', ',')
CREATE FUNCTION [dbo].[uftSplitString]
(
@String VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1),
Item VARCHAR(8000)
)...
^{“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 31, 2008 at 4:50 am

#851151
RE: Matching recoreds

Something like this?
SELECT h.OrderID, h.address, c.address, p.cardno, c.cardno
FROM T_OrderHeader h
INNER JOIN T_Customers c ON c.CustomerID = h.CustomerID
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
WHERE h.address <> c.address
^{“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 31, 2008 at 4:42 am

#851147
RE: Matching recoreds

and...
^{“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 31, 2008 at 4:24 am

#851135
RE: Usage of "Tally Table"

What's your point, Karthik? Chris knows exactly what the output will be for any pair of numbers entered into his query...
^{“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 31, 2008 at 4:17 am

#851130

Viewing 15 posts - 9,706 through 9,720 (of 10,143 total)