ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,341 through 8,355 (of 10,143 total)

RE: Ages and Age Ranges

Nice work.
There are a number of different ways of achieving the same thing with TSQL, even in the version you are using. Here's one of them:
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

December 16, 2009 at 5:49 am

#1092940
RE: Ages and Age Ranges

Ryan Keast (12/16/2009)
Oh my lord thats worked.
THANK YOU!!!!
I can understand the logic from this now.
Out of intrest at the very end of the SELECT statement you have 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

December 16, 2009 at 4:30 am

#1092908
RE: Ages and Age Ranges

Try this, Ryan:
SELECT *,
[Age Range] = CASE
WHEN [Age] < 18 THEN 'Juvenile'
WHEN [Age] BETWEEN 18 AND 31 THEN '18 TO 31'
WHEN [Age] BETWEEN 32 AND 41 THEN '32 TO 41'
WHEN...
^{“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 16, 2009 at 4:00 am

#1092901
RE: Duplicate Entries

This should get you started, Dean:
DROP TABLE #MySampleTable
CREATE TABLE #MySampleTable (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT)
INSERT INTO #MySampleTable (col1, col2, col3, col4,...
^{“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 15, 2009 at 9:26 am

#1092593
RE: Ages and Age Ranges

Ryan Keast (12/9/2009)
How would I incorporate multiple age ranges?
So take it a bit further and say if between age 25 and 48 then "25-48" and add more if I need...
^{“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 15, 2009 at 6:53 am

#1092478
RE: Ages and Age Ranges

This expression for returning age range bears little resemblance to TSQL:
(SELECT CASE WHEN (SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B]) THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) 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

December 15, 2009 at 2:49 am

#1092400
RE: Using Case in a where clause

Something like this:
WHERE headcount = 'transfercluster'
OR headcount = CASE WHEN @Consolidate <> '(Retail Consolidate)' THEN 'TransferConsolidate' ELSE CAST(NEWID() AS CHAR(36)) END
^{“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 15, 2009 at 2:39 am

#1092397
RE: Using Case in a where clause

You can use CASE in a WHERE clause, but that's not the problem - you are trying to construct a list on-the-fly for the IN operator. The syntax of your...
^{“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 15, 2009 at 2:15 am

#1092389
RE: Inserting value for identity column in a temporary table

There's an alternative syntax which you may find more intuitive:
SELECT IDENTITY(int, 1, 1) AS RowID, ...
INTO #temp
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

December 14, 2009 at 9:46 am

#1092062
RE: Are the posted questions getting worse?

GSquared (12/14/2009)
CirquedeSQLeil (12/11/2009)
By the time the thread hits 10,000 y'all will be flatlined, passed out drunk from all the bear being passed around - bwahahaha 10,000 shall be mine (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

December 14, 2009 at 9:41 am

#1092058
RE: Stored Procedure

GSquared (12/14/2009)
That doesn't look like T-SQL. Not sure what it is, but I doubt it's from an MS SQL database.
<<from dual>> Oracle?
^{“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 14, 2009 at 8:32 am

#1091989
RE: Aggregation with every N number of records grouped

sibir1us (12/10/2009)
My question was to get the aggregation for every 5 rows of data, no matter how many rows there are in the table. If I have 503 records in...
^{“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 14, 2009 at 6:59 am

#1091914
RE: Are the posted questions getting worse?

GSquared (12/11/2009)
On the subject of beer, one of my favorite jokes is that the human body is the world's most efficient machine at turning European beer into American beer. ...
^{“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 14, 2009 at 2:04 am

#1091800
RE: update cursor

CirquedeSQLeil (12/11/2009)
Thanks Chris for following up.
As for the requirements and desired output, Chris was spot on. If this does not do exactly what you envisioned - we need 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

December 11, 2009 at 9:12 am

#1091246
RE: update cursor

charu.verma (12/11/2009)
Thanks-but this will not work
The cloumns in Tbl3 have to be deleted tnum of tbl2,formatted tnum returned from fucntion & sid of tbl1 & not oid of tbl2
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

December 11, 2009 at 8:17 am

#1091212

Viewing 15 posts - 8,341 through 8,355 (of 10,143 total)