ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,236 through 8,250 (of 10,143 total)

RE: Compare data to the same time last year?

Craig, you can check the values of the variables:
DECLARE @startDate datetime;
DECLARE @enddate datetime;
SET @startDate = dateadd(year, -8, dateadd(day, datediff(day, 0, getdate()), 0));
SET @endDate = dateadd(year, -1, dateadd(day, datediff(day, 0, getdate()),...
^{“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

January 14, 2010 at 5:30 am

#1103533
RE: 10 digit random unique alpha-numeric number

Abhijeet Dighe (1/14/2010)
I want it for storing transaction no.
I think it will be better to check in database for uniqueness.
Transaction no.? As in named transactions?
^{“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

January 14, 2010 at 4:24 am

#1103510
RE: 10 digit random unique alpha-numeric number

Abhijeet Dighe (1/14/2010)
Thanks.
But if I take only 10 characters of string generated using newid() like this:
select CONVERT(varchar(10), right(newid(),10))
still will it generate unique string?
It depends upon the scope of your requirement....
^{“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

January 14, 2010 at 3:49 am

#1103496
RE: 10 digit random unique alpha-numeric number

Abhijeet Dighe (1/13/2010)
Hi
So, newid() function only generates random no.
Will I have to check for its uniqueness?
According to BOL, NEWID() "Creates a unique value of type uniqueidentifier."
^{“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

January 14, 2010 at 2:40 am

#1103469
RE: Are the posted questions getting worse?

Greg Edwards-268690 (1/13/2010)
Grant Fritchey (1/13/2010)
Steve Jones - Editor (1/13/2010)
Grant,
I didn't think it was that bad, but when it doubt, doesn't hurt to apologize.
Yep. I agree. As soon as it was...
^{“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

January 14, 2010 at 2:05 am

#1103467
RE: How do you limit records using Distinct and Count - TOP and Rowcount dont work ?

ifila (1/13/2010)
Lynn Pettis (1/13/2010)
Could you please explain what you are trying to achieve? What is the business case for this query (now I'm starting to sound life 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

January 13, 2010 at 9:31 am

#1103160
RE: Update over linked servers slow

Using 2005, pulling relevant rows from the linked server into a #temp table can dramatically improve performance.
SELECT key, name
INTO #Remote
FROM serverA.dbname.dbo.tablename
UPDATE FROM with a JOIN can be unpredictable if...
^{“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

January 13, 2010 at 7:36 am

#1103048
RE: HELP NEEDED TO RETURN A SINGLE ROW FROM MULTIPLE RESULTS

glen.wass (1/13/2010)
Thanks Lutz,
i have worked out a method for doing this now,
Step 1 create table with all my results in it, 2 create update table
step 3 create cursor to insert...
^{“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

January 13, 2010 at 6:28 am

#1102994
RE: Comparing and Deleting repeating values in semicolon delimited string

Here are two queries which will do the trick.
Both use a tally table to transform the segments within the column into rows, then use a GROUP BY
to dedupe. To...
^{“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

January 13, 2010 at 6:18 am

#1102990
RE: How do you limit records using Distinct and Count - TOP and Rowcount dont work ?

Paul White (1/13/2010)
ifila (1/12/2010)
I did get a couple of suggestions.
I bet you did :laugh:
ROFL! <<unprintable>> 😀
Thanks Paul, that made the long slog into work through the snow very worthwhile!
^{“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

January 13, 2010 at 2:26 am

#1102934
RE: Is this possible? Select Statement difficulties

nialltuohy (1/12/2010)
Chris,
Thanks, i know it does but i have one small problem, i also need the savings to be split into 4 columns, for example a policy savings column, building_savings,...
^{“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

January 12, 2010 at 9:54 am

#1102558
RE: Is this possible? Select Statement difficulties

nialltuohy (1/12/2010)
Thanks for the reply, this very nearly works thanks very much appreciate it, the problem with the reports i have to use unfortunately is that they wont process multiple...
^{“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

January 12, 2010 at 9:03 am

#1102503
RE: Is this possible? Select Statement difficulties

Something like this?
/** Getting Total Savings per Policy*/
Select 'Policy' AS Sector,
subpolicy_name,
'' AS building_name,
'' AS floor_name,
'' AS room_name,
SUM(CO2_savings) As total_savings
From #CO2temp
group by subpolicy_name
UNION ALL
/** Getting Total Savings per...
^{“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

January 12, 2010 at 8:07 am

#1102452
RE: Using Subqueries and Joins - Retrieving columns from multiple tables without using table variables/temp tables

José.Cruz (1/12/2010)
Hi,
The problem is that the description field is diferente, for instance for machine topdubctx02 you get 2 rows in the result:
TestDate description machname
---------------------------------------------------------------------------------------------
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29...
^{“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

January 12, 2010 at 7:09 am

#1102396
RE: Grouping in stored procedure and using a case statement

Spikemarks, is it possible to determine what you want to do from these two posts? I don't think so! How about showing two output lists, one containing what you currently...
^{“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

January 12, 2010 at 6:55 am

#1102377

Viewing 15 posts - 8,236 through 8,250 (of 10,143 total)