ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,556 through 9,570 (of 10,143 total)

RE: Group by Date....

Madhivanan (9/19/2008)
The proper way would be
group by dateadd(day,datediff(Day,0,date_col),0)
For group by, it doesn't necessarily need to be cast back to a datetime:
datediff(Day,0,date_col)
^{“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 19, 2008 at 8:13 am

#874247
RE: Using Joins and Derived tables

Hi Beruken
Instead of counting how many rows each of the temp tables contains and using the result to determine which is the parent table, it would make sense to use...
^{“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 19, 2008 at 7:53 am

#874223
RE: Count the number of spaces in a field

steveb (9/19/2008)
Terri (9/19/2008)
You do want to count them, but you want to cancel them out. Otherwise the replace function will catch them and it will ruin the count.
Why...
^{“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 19, 2008 at 7:38 am

#874204
RE: nvarchar -int

For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Thanks Jeff)
You could CAST the [ID] as a varchar.
^{“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 19, 2008 at 5:40 am

#874129
RE: getting week number of the current year

ALZDBA (9/19/2008)
There is this very nice and eye-opening article concering ranges ...
Called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
http://www.sqlservercentral.com/articles/TSQL/62867/
Nobody listens on a friday...
^{“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 19, 2008 at 5:02 am

#874115
RE: No. of sundays betn 2 dates

Or use a Tally table:
[font="Courier New"]SET DATEFORMAT YMD
SET DATEFIRST 1 -- monday, so sunday is 7
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2008-09-01' -- implicit conversion
SET @EndDate = '2008-09-22' --...
^{“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 19, 2008 at 5:00 am

#874112
RE: 1yr,3yr and 5yr + Date calculation

[font="Courier New"]DECLARE @MyDate DATETIME
SET @MyDate = CAST('2008-09-19' AS DATETIME) -- makes a date without a time component
SET @MyDate = DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) -- chop the time component off today
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

September 19, 2008 at 3:27 am

#874075
RE: getting week number of the current year

Is there any reason why you aren't using a start date (the first day of week 36) and an end date (the last day of week 39) for this?
WHERE YourDateColumn...
^{“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 19, 2008 at 3:09 am

#874068
RE: getting week number of the current year

Nomvula (9/18/2008)
hi chris i looked at that and it fine but you giving me the start and the end date but i want the list of all the dates 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

September 18, 2008 at 2:00 am

#873427
RE: getting week number of the current year

Nomvula (9/17/2008)
for instance '01/09/2008' - '07/09/2008' by looking in my calendar those dates fall in week 36 so i need to get all the dates which falls on wk 36,37,38,39
Here's...
^{“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 17, 2008 at 7:57 am

#873012
RE: getting week number of the current year

Nomvula (9/17/2008)
hi Chris
thanks for the response, actualy i'm trying to get dates within week 36,37,38,39.
apologies for my mistake, i didn't elaborate that thorough
Which dates, Nomvula? The start and end dates...
^{“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 17, 2008 at 5:45 am

#872933
RE: getting week number of the current year

Nomvula (9/17/2008)
hi guys
i need to create a script to get week 36,37,38 and 39 of the current year.
No problem:
SELECT DATEPART(YY, GETDATE()) AS CurrentYear, CAST(36 AS SMALLINT) AS [Week] 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

September 17, 2008 at 5:29 am

#872925
RE: T-Sql Top 3rd Position

Hi Sanjay
Please have a read of this thread: http://www.sqlservercentral.com/Forums/Topic565091-145-1.aspx
it will help us to help you.
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 17, 2008 at 5:20 am

#872923
RE: Sum values from rows with overlapped dates

So, some of the time you take the maximum value of the overlaps, and other times you take the sum of the overlaps. What's the logic for the decision?
^{“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 16, 2008 at 12:53 am

#872157
RE: Happy Birthday

Steve Jones - Editor (9/15/2008)
Make sure you don't target any developers with that thing!
Thanks again for all the well wishes.
Only property developers Steve, they nick all the best shooting land...
^{“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:45 pm

#871991

Viewing 15 posts - 9,556 through 9,570 (of 10,143 total)