ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,571 through 9,585 (of 10,143 total)

RE: Sum values from rows with overlapped dates

jwpquinn (9/15/2008)
Chris, Thank you for taking the time to review this issue.
Your SQL is correct for the EmpID 2 with only Alogs 4 through 8.
But when I uncomment the code...
^{“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

September 15, 2008 at 12:44 pm

#871989
RE: Sum values from rows with overlapped dates

Is this what you're looking for?
[font="Courier New"]SET DATEFORMAT mdy
DROP TABLE #OVERLAP
CREATE TABLE #OVERLAP
(
ALog INT,
ABegin DATETIME,
AEnd DATETIME,
EmpID CHAR(11),
APercent FLOAT,
PRIMARY KEY (ALog, ABegin, AEnd)
)
INSERT INTO #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)
SELECT 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

September 15, 2008 at 9:46 am

#871846
RE: Happy Birthday

Happy Birthday Mr Jones for when the day comes.
Late November this year I'll be 50. About ten months ago I finally got around to taking up the English longbow after...
^{“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

September 15, 2008 at 8:18 am

#871771
RE: amount difference between 2 dats

mathewspsimon (9/11/2008)
no PK and Indexes are there.
That pretty much identifies this as a homework question. Rather than solve this for you, here's an article which discusses more than one method...
^{“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

September 11, 2008 at 4:38 am

#870200
RE: amount difference between 2 dats

Hi Mathew
What do you have in the way of pk / indexes in this table?
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

September 11, 2008 at 4:29 am

#870192
RE: Traversing Rows

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

September 11, 2008 at 3:11 am

#870173
RE: String function

Jeff Moden (9/10/2008)
Chris Morris (9/10/2008)
Hi Khushbu
You're welcome, thanks for the feedback.
The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (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

September 11, 2008 at 1:08 am

#870134
RE: Report # of characters for an integer value

It works on SQL Server 2k, and I'm pretty sure it will work in SQL Server 2k5 too. This is a SQL Server 2k5 forum section.
^{“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

September 10, 2008 at 1:37 pm

#869946
RE: Report # of characters for an integer value

gpeters (9/10/2008)
Chris Morris (9/10/2008)
I've just run this again, exactly as it appears here...
Strange. What type of db are you using?
You first 😉
^{“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

September 10, 2008 at 10:31 am

#869802
RE: Traversing Rows

If two tally tables makes it too confusing, then one will do at a push. The output of this is the position of the first @, the first and second...
^{“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

September 10, 2008 at 10:28 am

#869799
RE: Report # of characters for an integer value

gpeters (9/10/2008)
Chris Morris (9/10/2008)
There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?
Hi. Yes I executed the exact query you supplied.
I've just run this again, exactly...
^{“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

September 10, 2008 at 10:08 am

#869789
RE: Report # of characters for an integer value

There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?
^{“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

September 10, 2008 at 9:51 am

#869774
RE: Traversing Rows

Or even better - one tally table for each delimiter 😛
[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)
SET @holdingAndWeightAndComments  = '@MAGFX~51~Comment1@MALOX~52~Comment2@MAGFX~53~Comment3@'
SELECT n1.number,
   n2.number,
   CHARINDEX('~', @holdingAndWeightAndComments, n2.number+1),
   CHARINDEX('@', @holdingAndWeightAndComments, n1.number+1),
   SUBSTRING(@holdingAndWeightAndComments, n1.number+1, n2.number...
^{“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

September 10, 2008 at 9:49 am

#869773
RE: Traversing Rows

Like this?
[font="Courier New"]DECLARE @holdingAndWeightAndComments VARCHAR(60)
SELECT @holdingAndWeightAndComments  = '@MAGFX~50~Comment1@MALOX~50~Comment2@MAGFX~50~Comment3@'
DROP TABLE #hold
SELECT number, '9' AS [ID], --[ID] = IDENTITY(5),
   SUBSTRING(@holdingAndWeightAndComments,number+1,
          CHARINDEX('@',SUBSTRING(@holdingAndWeightAndComments,number+1,DATALENGTH(@holdingAndWeightAndComments)))-1) AS hold
INTO #hold
FROM Numbers -- Tally Table is a direct replacement of...
^{“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

September 10, 2008 at 9:02 am

#869730
RE: Report # of characters for an integer value

gpeters (9/10/2008)
Awesome. Thank you Mr or Mrs. 500.
You're very welcome 'Forum Newbie', 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

September 10, 2008 at 8:27 am

#869695

Viewing 15 posts - 9,571 through 9,585 (of 10,143 total)