ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,401 through 8,415 (of 10,143 total)

RE: DateAdd together with DateDiff

What should happen to a deadline date of 28th February 2010 on 29th January 2010?
^{“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 7, 2009 at 3:59 am

#1088429
RE: complex query

Ankur Bhardwaj (12/7/2009)
Find all the employees who have joined at last day of month in last 3 years (2007,2008,2009)
Can you please supply some sample data in a readily-consumable format? 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

December 7, 2009 at 3:51 am

#1088427
RE: performing a loop in the SELECT clause

davidandrews13 (12/4/2009)
Lynn Pettis (12/4/2009)
Please, when you get a chance, go back and eliminate the cursor. Since there is a set-based solution, you really should use that as it should...
^{“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 4, 2009 at 9:14 am

#1087827
RE: performing a loop in the SELECT clause

davidandrews13 (12/4/2009)
OK. its a bit difficult for me to give some test data for this because the declaring of the temp tables and filling them would have to go into...
^{“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 4, 2009 at 7:32 am

#1087715
RE: Are the posted questions getting worse?

GSquared (12/4/2009)
I found out late yesterday that the CEO, the President, the Chairman, and the CFO, of the company I work for, all resigned late Wednesday, without prior notice, 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

December 4, 2009 at 7:21 am

#1087699
RE: Are the posted questions getting worse?

Grant Fritchey (12/4/2009)
In general, I'd say that's a hint to blow the dust off the resume.
Hey Grant, sorry I missed your recent trip over here - would have been 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

December 4, 2009 at 7:10 am

#1087688
RE: performing a loop in the SELECT clause

davidandrews13 (12/4/2009)
thanks everyone. got it to work. 😎
Top work, David. How about posting your solution for the curious?
^{“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 4, 2009 at 6:32 am

#1087641
RE: How to optimize this query

mabud_ncc (12/4/2009)
table p [prod_id (pk) , name, status]
table o [oid (pk), prod_id(fk), status]
table p2p [p2pid (pk),path_id(fk), prod_id(fk), status]
table pv [path_id(fk), cat_id (fk), step]
table c [cat_id (pk), status,...
^{“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 4, 2009 at 5:32 am

#1087596
RE: How to optimize this query

Some sample data showing the relationships between the tables would be great. There are a number of ways of structuring this type of query which has different aggregates hanging off...
^{“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 4, 2009 at 3:29 am

#1087562
RE: performing a loop in the SELECT clause

Replacing the cursor code with this will simplify things for you.
DECLARE @cats varchar(1000)
SELECT @cats = ISNULL(@cats, '') + '[' + cat.catName + '], '
FROM #Categories cat
-- Display ...
^{“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 3, 2009 at 8:55 am

#1087176
RE: performing a loop in the SELECT clause

Lynn Pettis (12/3/2009)
Sounds like a dynamic pivot may be required here. Please chack out the article references in my signature block below regarding Cross Tabs and Pivots.
Lynn beat me...
^{“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 3, 2009 at 8:27 am

#1087137
RE: convert left(datetime ,11) and order by

BaddaBing (12/3/2009)
The problem here is that if you want SQL Server to display that date in this format DD MMM YYYY (20 Dec 2009) then you need to convert it...
^{“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 3, 2009 at 6:25 am

#1087029
RE: convert left(datetime ,11) and order by

DROP TABLE #Guests
CREATE TABLE #Guests(
Firstname varchar(30),
Lastname varchar(30),
Birthdate datetime
)
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Jim','Jones','1961-12-03 01:00:00.000')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Joe','Bloggs','1961-12-03 02:00:00.000')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES...
^{“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 3, 2009 at 5:16 am

#1086994
RE: How to write a query for birthdays in the next two weeks?

DROP TABLE #Guests
CREATE TABLE #Guests(
Firstname varchar(30),
Lastname varchar(30),
Birthdate datetime
)
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Jim','Jones','19611203')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Joe','Bloggs','19621210')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Dave','Thomas','19631212')--Birthday in...
^{“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 3, 2009 at 5:10 am

#1086991
RE: Are the posted questions getting worse?

Lynn Pettis (5/28/2009)
Okay! Any one in Germany near Frankfurt? Maybe has a teen age (around 18 year old) son or daughter (preferably)?
And yes I am serious. I...
^{“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

June 4, 2009 at 2:53 am

#1004475

Viewing 15 posts - 8,401 through 8,415 (of 10,143 total)