ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,466 through 9,480 (of 10,143 total)

RE: Start and End Date Groupings

Debbie Edwards (10/13/2008)
Thats exactly what I was thinking....
But I would want it to reset for each person every time it got to the next ECAFST
118007ECAFSTD5028582007-09-102007-09-10 1
118007ECAFMTG5033412007-12-11 2007-12-11 ...
^{“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

October 14, 2008 at 12:36 am

#884423
RE: Join problem

dana_turcanu1981 (10/13/2008)
Hi!
I have 2 tables:
CREATE TABLE [dbo].[Tab1](
[Cpt] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL)
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('2')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('235')
INSERT INTO [dbo].[Tab1] ([Cpt]) VALUES('234')
and
CREATE...
^{“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

October 13, 2008 at 8:05 am

#884147
RE: Start and End Date Groupings

Hi Debbie
If you are able to add columns to your data set, how about adding a column to identify ECAF groups? This would make reporting on your data much easier....
^{“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

October 13, 2008 at 6:51 am

#884119
RE: Query for Reporting Purpose

Hi Ravi
Use a GROUP BY to aggregate the results to SubjectID level:
SELECT SubjectID,
MAX(CASE WHEN ClassID= @FirstClassID THEN ISNULL(CostOfBook,0) ELSE 0.0 END) AS FirstClassCost
MAX(CASE WHEN ClassID= @SecondClassID THEN ISNULL(CostOfBook,0) ELSE 0.0...
^{“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

October 13, 2008 at 4:10 am

#884049
RE: REPLACE

Jackal (10/13/2008)
Sorry, I'm being a clown, I skimmed over the article to quickly.
I see now the usage of the replace, however this is done in a while loop.
I 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

October 13, 2008 at 4:05 am

#884047
RE: REPLACE

Yes it is - GSquared posted a tried-and-tested function using REPLACE in the same thread. Problem is, there are so many possible characters to look for.
^{“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

October 13, 2008 at 3:46 am

#884039
RE: REPLACE

Hello
GSquared demonstrates an excellent function for this here:
http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx
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

October 13, 2008 at 3:36 am

#884032
RE: what does it mean "SELECT ... FROM .... WHERE X NOT IN (8)"

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

October 13, 2008 at 2:35 am

#884014
RE: what does it mean "SELECT ... FROM .... WHERE X NOT IN (8)"

Ronald (10/13/2008)
Can someone explain to me what it means when adding this to the end of a SQL query 'NOT IN (8)'
Hi Ronald
Try the following statement.
[font="Courier New"]SELECT *
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

October 13, 2008 at 2:15 am

#884005
RE: 'N' number of usage - Tally Table

Hi Barry, it's a while since I worked with the product, but back then it was blindingly fast vs the competition when importing files or splitting files as strings into...
^{“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

October 10, 2008 at 7:16 am

#883389
RE: 'N' number of usage - Tally Table

Ahah. Thanks for the clarification, Jeff.
^{“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

October 10, 2008 at 5:33 am

#883355
RE: 'N' number of usage - Tally Table

Sergiy (10/9/2008)
One day some developer will read a conversation like this and decide it's good to use "thorn" character as a replacement for CR/LF.
He/she will agree it with other side...
^{“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

October 10, 2008 at 3:01 am

#883314
RE: Problems with dynamic string in Openquery

I'm sorry Joey but that simply isn't correct. Here's part of the WHERE clause from a very similar situation to yours, OPENROWSET with dynamic SQL:
AND CREATE_DTTM >= ''''' + @sDateRangeStart...
^{“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

October 9, 2008 at 8:37 am

#882938
RE: Tricky Query

DROP TABLE #Sample
CREATE TABLE #Sample (C1 VARCHAR(20), C2 VARCHAR(20), C3 VARCHAR(20), C4 VARCHAR(20))
INSERT INTO #Sample (C1, C2, C3, C4)
SELECT 'a@a.com', '1-Jan-08', 'AAA', 'BBB' UNION ALL
SELECT 'a@a.com', '1-Jan-08', 'CCC', 'DDD'...
^{“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

October 9, 2008 at 8:24 am

#882920
RE: Need Help--Derived Tables???

bharathi (10/9/2008)
I found it myself...Thankss a lot anywayz...
Hi bharathi
Please would you post your solution? It would finish this thread nicely and also, possibly, provide useful learning material for others.
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

October 9, 2008 at 6:29 am

#882838

Viewing 15 posts - 9,466 through 9,480 (of 10,143 total)