ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,191 through 5,205 (of 10,143 total)

RE: Creating a Ref Number usning letters and numbers.

wafw1971 (2/20/2013)
I have a column on my database called Booking Number this is to be used on correspondence etc however I don't want it to be Booking Number to be...
^{“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 20, 2013 at 6:18 am

#1589528
RE: Creating a BookingDate using a random function

wafw1971 (2/20/2013)
Its alright I have figured it out:
...
DwainC already figured it out for you:
SELECT
ArrivalDate,
BookingDate = DATEADD(day,
-(1 + ABS(checksum(NEWID())) % 90),
ArrivalDate)
FROM Bookings
ORDER BY ArrivalDate
^{“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 20, 2013 at 5:46 am

#1589516
RE: Deleting duplicates rows

DROP table #empl
CREATE TABLE #empl
(
row_id int,
ename varchar(120),
job varchar(120),
sal varchar(100)
)
INSERT #empl VALUES
('4','ram','IT','60000'),
('4','ram','IT','60000'),
('4','ram','IT','60000'),
('4','ram','IT','60000');
WITH OrderedData AS (
SELECT row_id, ename, job, sal,
rn = ROW_NUMBER() OVER(PARTITION BY row_id, ename, job, sal...
^{“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 20, 2013 at 12:54 am

#1589457
RE: All Possible Combinations Loop

akberali67 (2/18/2013)
Chris, thanks again for all the help. Truly thanks doesnt do it but from the bottom of my heart I can not thank you for the help.
Gosh - thanks!...
^{“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 19, 2013 at 1:00 am

#1589056
RE: SQL COMPARE HELP!!!

-- Collect the matches
SELECT l.ID ;
FROM LARGE l ;
INNER JOIN small s ;
ON s.ID <> l.ID ;
AND LARGE.Z5 = small.z5 and Large.ln = small.ln and Large.fn = small.fn...
^{“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 19, 2013 at 12:57 am

#1589054
RE: All Possible Combinations Loop

Paul White (2/18/2013)
ChrisM@Work (2/11/2013)
I don't know if this error (Error 8623) is dependant upon system resources, in which case you might get lucky using a server with more resources, or...
^{“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 18, 2013 at 2:59 am

#1588764
RE: Any thoughts on this process?

sqldba_newbie (2/16/2013)
This is more of generic programming question. We have a stored procedure which is used on many of our applications built using asp.net. The sp basically first gives total...
^{“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 18, 2013 at 1:21 am

#1588729
RE: Need urgent help in a tricky t-SQL

Sachin Vaidya (2/14/2013)
Hi Cadavre,
Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people 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 15, 2013 at 1:09 am

#1588215
RE: A query question for the SQL Gurus

Another way:
SELECT abc.*
FROM dbo.abc abc
WHERE NOT EXISTS (SELECT 1 FROM dbo.xyz)
OR abc.id IN (SELECT ID FROM dbo.xyz)
^{“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 13, 2013 at 6:56 am

#1587431
RE: Calculating a Moving Average

mpdillon (2/12/2013)
...since it didn't use the lookup table, I was not able to fully implement it....
Which lookup table, Pat? The article (and the discussion) should provide you with all the...
^{“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 12, 2013 at 5:55 am

#1586963
RE: Comparing figures based on relative date

ScottPletcher (2/11/2013)
For best performance,
Cluster the table by
LAST_DAY_OF_MONTH
and not the dopey identity column. If you still need the identity (??), you can leave it as the nonclustered pk.
+1
^{“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 12, 2013 at 12:57 am

#1586858
RE: IF or CASE on 2 different columns, THEN values x, y or z

The option you have commented out will never be selected because there's an option before it in the CASE construct which will:
CASE
WHEN mb.name LIKE...
^{“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 11, 2013 at 9:54 am

#1586659
RE: Count Rows within a Query

You're welcome. Thank you 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

February 11, 2013 at 9:52 am

#1586658
RE: Change rows into column without using pivot method.

gparamasivamit (2/11/2013)
i need answer
42 of course. Doesn't everybody know this now?
^{“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 11, 2013 at 9:49 am

#1586655
RE: Count Rows within a Query

CassieF (2/11/2013)
ho hum..
SQL Server 2000 - 8.00.2039 (intel x86)
Heh no worries. Try this;
SELECT
MarketName,
COUNT(*)
FROM (
SELECT --top 100 percent
f.FormName,
m.MarketName
FROM Form f
INNER JOIN FormMarkets_Link l
ON f.FormID =...
^{“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 11, 2013 at 9:45 am

#1586652

Viewing 15 posts - 5,191 through 5,205 (of 10,143 total)