ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,376 through 9,390 (of 10,143 total)

RE: conditional if statements

You're welcome. Does it work now? If so, it would be beneficial to post your completed query here - you never know when someone else is going to come up...
^{“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

November 19, 2008 at 8:50 am

#900587
RE: Adding empty rows for items which do not exist

Your colleagues version is exactly what I would have done pre tally-tables.
Incidentally, this...
Substring(Cast(l.LogDate as nvarchar), 1, 11) = t.logdate
could be a performance killer, SQL Server won't know if 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

November 19, 2008 at 8:48 am

#900586
RE: conditional if statements

Have you considered writing it like this...
[font="Courier New"]UPDATE [AppUsers]
   SET [Username] = @NewUsername
WHERE [Username] = @OldUsername
   AND @OldClassification = ''
   AND @OldMerchant = ''
UPDATE [AppUsers]
  ...
^{“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

November 19, 2008 at 8:35 am

#900573
RE: Adding empty rows for items which do not exist

Hi Chris
It's because there's a time component in LogDate. Using a range takes care of this:
[font="Courier New"]SELECT DATEADD(DAY, n.number-1, @StartDate) AS CalcLogDate,
COUNT(DISTINCT(ProcessID)) AS ProcessCount
FROM Numbers n
LEFT JOIN #LogEntry l...
^{“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

November 19, 2008 at 8:12 am

#900548
RE: CTE returns NULL value.

What happens when you run this simplified version of your query? If you supply some sample data then forum members will be able to test themselves.
[font="Courier New"]
DECLARE @HieID -- assign...
^{“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

November 19, 2008 at 7:30 am

#900515
RE: How to convert bit to character

DECLARE @bit BIT
SET @bit = 0 -- or 1, or comment out
SELECT @bit AS BitValue, CASE @bit WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE NULL END AS YesNo...
^{“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

November 19, 2008 at 7:16 am

#900503
RE: CTE returns NULL value.

sqluser (11/19/2008)
i am getting the following error while executing the procedure.
The statement terminated. The maximum recursion %d has been exhausted before statement completion.
Please help to solve the problem.
There is insufficient...
^{“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

November 19, 2008 at 7:04 am

#900491
RE: Find Closest Match Data

Token matching works quite well...
[font="Courier New"]DROP TABLE #Table
CREATE TABLE #Table (TheValues VARCHAR (20))
INSERT INTO #Table (TheValues)
SELECT '0010' UNION ALL
SELECT '11242' UNION ALL
SELECT '0011246' UNION ALL
SELECT '0011264' UNION ALL
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

November 19, 2008 at 5:43 am

#900456
RE: CTE returns NULL value.

Can you post the procedure?
^{“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

November 19, 2008 at 5:14 am

#900452
RE: Adding empty rows for items which do not exist

Hi Chris
Do you have a tally (numbers) table? If so, try this:
[font="Courier New"]DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = 'Nov 01 2008'
SET @EndDate = 'Dec 01 2008'
SELECT DATEADD(DAY, n.number-1, @StartDate)...
^{“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

November 19, 2008 at 4:51 am

#900438
RE: use "sum" aggregate function as criteria

Can you UPDATE tempTable? If so, you could use the running totals solution:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
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

November 19, 2008 at 4:13 am

#900417
RE: Retrieving latest date from 2 fields in table

Try this:
ORDER BY CASE WHEN ISNULL(DateAmended, DateCreated) > DateCreated THEN DateAmended ELSE DateCreated END DESC
^{“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

November 19, 2008 at 4:10 am

#900416
RE: Using MAX in a SELECT Statement

Hi Michael
I don't think this could be converted into a view in the exact way that you've suggested because the parameters are required for the derived table, i.e. before 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

November 19, 2008 at 2:48 am

#900398
RE: The flow of an UPDATE STATEMENT with a FROM CLAUSE

Hi John
Compare the SELECT component of each statement.
Row count
Values if you feel it is necessary
Time.
This is what I mean by the SELECT component:
[font="Courier New"]SELECT [P].[LAST_PAYMENT_DATE]
   ,.[NEXT_PAYMENT_DATE]
   ,[P].[CUR_PAYMENT]
   ,.[AVG_PMT_FREQ]
FROM [dbo].[Amortization]...
^{“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

November 19, 2008 at 2:14 am

#900380
RE: The flow of an UPDATE STATEMENT with a FROM CLAUSE

John
I reckon this...
[font="Courier New"]                UPDATE [A] SET
                                [LAST_PAYMENT_DATE] =        [P].[LAST_PAYMENT_DATE]
                                ,[NEXT_PAYMENT_DATE] =        .[NEXT_PAYMENT_DATE]
                                ,[CUR_PAYMENT]                =        [P].[CUR_PAYMENT]
                                ,[PMT_FREQ]                        =        .[AVG_PMT_FREQ]
                FROM [dbo].[Amortization] [A]
                INNER JOIN [##PS]        [P]
           ON [A].[ID_NUMBER] = [P].[ID_NUMBER]
                INNER JOIN [##PS]
           ON [A].[ID_NUMBER] = .[ID_NUMBER]
                WHERE [A].[TBL_SOURCEID] <> '1'
           AND [P].[RECCNT] =...
^{“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

November 18, 2008 at 10:57 am

#900087

Viewing 15 posts - 9,376 through 9,390 (of 10,143 total)