ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,976 through 9,990 (of 10,143 total)

RE: Get DATE part of the DATETIME

DECLARE @TheDate DATETIME, @HalfDayAndaBit DECIMAL (14,13)
SET @HalfDayAndaBit = 0.5000000385803
SET @TheDate = '2008-02-05 11:59:59.993'
SELECT @TheDate
SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)
SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)
SET @TheDate = '2008-02-05 11:59:59.997'
SELECT @TheDate
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

February 5, 2008 at 9:25 am

#775197
RE: Get DATE part of the DATETIME

DECLARE @TheDate DATETIME
SET @TheDate = '2008-02-05 23:59:59.997'
SELECT @TheDate
SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)
SET @TheDate = '2008-02-05 00:00:00.000'
SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)
😉
We're still on 2K, could be decades...
^{“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

February 5, 2008 at 7:11 am

#775114
RE: Get DATE part of the DATETIME

Running an update against a table with a little over 500,000 rows I get the following (average of three runs, in seconds)
1) CAST / FLOOR / CAST [2.32]
2) DATEADD /...
^{“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

February 5, 2008 at 4:29 am

#775044
RE: Get DATE part of the DATETIME

Not yet, Derek. Derek? Where'd he go? 😉
^{“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

February 5, 2008 at 3:54 am

#775033
RE: SQL statement

Have a play with the following, it should help you identify where the sp is failing:
DROP TABLE #Offboardv7
GO
CREATE TABLE #Offboardv7(
[ID] [int] NOT NULL,
[SID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_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

February 5, 2008 at 2:50 am

#775022
RE: Get DATE part of the DATETIME

Skinning a cat innit:
SELECT [DateOnly] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- CAST / FLOOR / CAST
SELECT [DateOnly] = dateadd(dd,datediff(dd, 0, getdate()), 0) -- DATEADD / DATEDIFF
SELECT [DateOnly] = CAST(CAST(GETDATE()...
^{“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

February 5, 2008 at 2:17 am

#775017
RE: Strange behaviour of CASE when run between servers

It's perhaps worth a look. The error message states something about nested cases, but case nesting isn't necessary for the error - just more than 10 options in one case...
^{“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

February 1, 2008 at 7:59 am

#774224
RE: Strange behaviour of CASE when run between servers

This is probably a long shot Paul, but what is the maximum number of options you have in a single CASE construct? There's a limit, when running a query against...
^{“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

February 1, 2008 at 7:37 am

#774197
RE: Need help with query

You're very welcome Brian, and many 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

January 31, 2008 at 11:35 pm

#774067
RE: Transaction log backups are huge

Ian Yates (1/31/2008)
It will definitely be some sort of index rebuild. Another thing it could be is if you run a command similar to (but much larger in scope)
update...
^{“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

January 31, 2008 at 10:19 am

#773856
RE: Transaction log backups are huge

Matt Miller (1/31/2008)
persnickety is a kind word to describe Lawson....:) But still - you may find it doesn't need that much maintenance.
Actually - I thought QWIRKY was the layout...
^{“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

January 31, 2008 at 10:11 am

#773848
RE: Transaction log backups are huge

Good point Matt, I don't know the answer but your suggestion is pretty easy to check out. The system is Lawson which I'm finding out is kinda "pernickity" and quirky...
^{“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

January 31, 2008 at 9:32 am

#773821
RE: Transaction log backups are huge

Recovery model is "Full"
^{“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

January 31, 2008 at 6:46 am

#773718
RE: Transaction log backups are huge

Hi Todd & Gail
I'm looking into this now. Index rebuilds are 3am. It's an odd system, most columns are covered, and one of our accounts tables has 30m rows. Will...
^{“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

January 31, 2008 at 6:44 am

#773717
RE: Need help with query

This calculates daily totals for the last whole week, monday through saturday:
DROP TABLE #ContactRoutingDetail
GO
CREATE TABLE #ContactRoutingDetail (queueTime INT, StartDateTime DATETIME)
GO
INSERT INTO #ContactRoutingDetail (queueTime, StartDateTime)
SELECT 10, '2008-01-31 11:23:32.937' UNION ALL --...
^{“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

January 31, 2008 at 6:42 am

#773714

Viewing 15 posts - 9,976 through 9,990 (of 10,143 total)