ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,121 through 9,135 (of 10,143 total)

RE: Error: Conversion failed when converting datetime from character string.

Hi Sunil
Give this a try...SELECT DISTINCT UserName
FROM tbl_PBN_debugging
WHERE DATEDIFF(dd, Logdate, GETDATE()) < 91
...how many rows do you get?
This is "the number of users who have logged on within...
^{“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, 2008 at 4:29 am

#913037
RE: Curdate() ODBC Error

Hi David
Your question is answered here:
Because the ODBC-Driver makes "{fn CURDATE()}" beeing a char datatype the
conversion of "2006-08-01" to number results in an expression witch can be
convertet to "2006-01-08" as...
^{“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, 2008 at 1:56 am

#912989
RE: Join Query assitance

Hi Sunil
Please read the link at the bottom of my post, it will show you how to write table creation scripts and insert statements to provide forum users with sample...
^{“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, 2008 at 1:42 am

#912984
RE: Error: Conversion failed when converting datetime from character string.

Sunil, did you try my 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

December 16, 2008 at 1:21 am

#912981
RE: Are the posted questions getting worse?

i'm gonna stay carbon (dibaxide) neutral here
^{“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, 2008 at 1:59 pm

#912789
RE: error subtracting 2 time datatypes

This is a longshot Michael and I apologise if "sucking eggs" comes to mind...does 2008 have timediff / timeadd functions?
^{“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, 2008 at 9:55 am

#912600
RE: Arithmetic overflow error

Panks (12/15/2008)
Resolved...
Used a simple try catch block...
Thanks a lot anyways...
Which setting was it, Panks?
^{“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, 2008 at 9:52 am

#912598
RE: Filling in blanks

sognibene (12/15/2008)
At some point we're going to have to evaluate having a "permanent" tally table in the DB. For now, it's only used in reporting and since it can...
^{“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, 2008 at 9:47 am

#912593
RE: Arithmetic overflow error

Panks (12/15/2008)
I am working on SQL 2000 to 2005 migration project.
One of my proc uses the below script:
SET ARITHABORT OFF
UPDATE Prubond_MVR_Calc
SET MVRpct = (1- SFSurVal * (1 + Leeway) /...
^{“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, 2008 at 9:42 am

#912583
RE: Filling in blanks

You're welcome. I ran a quick test of both methods with a date range of nearly 40,000 days
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/1900'
SET @EndDate = '12/5/2009'
and both methods...
^{“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, 2008 at 9:28 am

#912560
RE: error-An expression of non-boolean type specified in a context where a condition is expected

viji (12/15/2008)
Thanks chris. nice explanation.
Could you please suggest some thing to get best performance on this??
thanks
regards
viji
It's impossible to tell without knowing more about your tables, viji. Here's an alternative...
^{“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, 2008 at 9:17 am

#912551
RE: Filling in blanks

If you have a tally table, then this works a treat:
[font="Courier New"]SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
      [TranDate] DATETIME,
      [TransactionAmount] money,
      [Balance] money)

INSERT INTO #myBalances VALUES ('12/1/2008',100,100)
INSERT INTO #myBalances VALUES ('12/3/2008',-60,40)
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

December 15, 2008 at 8:58 am

#912529
RE: error-An expression of non-boolean type specified in a context where a condition is expected

If you remove the unwanted conditions from the expression, then it's easier to see how it works...
WHERE locationkey IN (
SELECT locationkey
FROM dbo.ClientTbl_DimStores
WHERE...
^{“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, 2008 at 8:35 am

#912494
RE: error-An expression of non-boolean type specified in a context where a condition is expected

viji (12/15/2008)
sorry i didn't because, for us '%' means 'all' and if storeattribute is '%' the storeattributevalue will also be '%' and there is no column in the database 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 15, 2008 at 8:11 am

#912476
RE: Order by clause

'Welcome Seth 🙂
^{“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, 2008 at 6:56 am

#912432

Viewing 15 posts - 9,121 through 9,135 (of 10,143 total)