ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,731 through 8,745 (of 10,143 total)

RE: How to find Week start date and Week end date

Michael Valentine Jones (3/4/2009)
I know what a number table is.
Yeah I know Michael, just joshing mate, I've learned tons of stuff from you over the years. Thanks for...
^{“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

March 5, 2009 at 1:26 am

#953832
RE: Are the posted questions getting worse?

Bob Hovious (3/4/2009)
Some other priceless pearls from the "Mangled" SQL Blog....
21. In case using GROUP BY without an aggregate function try using DISTINCT instead
What you have to love...
^{“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

March 4, 2009 at 10:37 am

#953414
RE: Are the posted questions getting worse?

Derek Dongray (3/4/2009)
Intersting.
It seems Mangal Pardeshi is an MVP in SQL Server
http://www.microsoft.com/india/mvp/indiamvp.aspx#MangalPardeshi
Mangal is an E&TC Engineer from Pune University. He started his career as an ERP Technical Consultant in 2007....
^{“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

March 4, 2009 at 9:13 am

#953317
RE: Are the posted questions getting worse?

GilaMonster (3/4/2009)
Chris Morris (3/4/2009)
..you're not going to ask the business reason then? 😉
Free beer means no questions asked.
Aw Gail, beer for you too!
Blimey, one day I'll get around to saying...
^{“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

March 4, 2009 at 8:56 am

#953297
RE: Removing cursor from TSQL - 1

joao.rolo.sa (3/4/2009)
Basically using this technique,
of Using an extra column with ID from our Data from 1 to NROWS and processing the row with ID equal to
current row number,...
^{“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

March 4, 2009 at 8:51 am

#953293
RE: Count on multiple columns excluding ones containing zero

JJSJ (3/4/2009)
So from this I would expect
Totals
5
5
4
5
5
4
As these are the totals of columns populated with figures other than zero.
SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL
SELECT 10.1, 11.1, 12.1,...
^{“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

March 4, 2009 at 7:48 am

#953206
RE: Count on multiple columns excluding ones containing zero

JJSJ (3/4/2009)
Hi,
I don't think I have been clear in what I require. I will use your example.
.
.
.
Sorry I hope this makes sense???
Not yet. Can I suggest you use the following...
^{“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

March 4, 2009 at 7:02 am

#953136
RE: Are the posted questions getting worse?

Grant Fritchey (3/4/2009)
Chris Morris (3/4/2009)
Wipe yer eyes mate, here's a Fuller's ESB, probably the best beer in the world.
Ooohhh... Beer! And an ESB. I'm happy again. Cheers!
Welcome! ...you're not going...
^{“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

March 4, 2009 at 6:57 am

#953127
RE: Are the posted questions getting worse?

GilaMonster (3/4/2009)
19 threads in total (including the Answered posts one), mostly a single post per thread. Ok solutions, little explanation and once or twice a link to the person's blog.
......
^{“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

March 4, 2009 at 6:53 am

#953120
RE: Are the posted questions getting worse?

Grant Fritchey (3/4/2009)
jcrawf02 (3/4/2009)
Grant Fritchey (3/4/2009)
Well that one just yacked me off but good. He may as well be quoting from my posts because I'm "guilty" of all the "crimes"...
^{“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

March 4, 2009 at 6:23 am

#953077
RE: Count on multiple columns excluding ones containing zero

Mangal Pardeshi (3/4/2009)
One more way, assuming that all columns you are considering are of numeric data type.
DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)
INSERT INTO @T SELECT
1,2,3,10 UNION...
^{“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

March 4, 2009 at 5:06 am

#953012
RE: Count on multiple columns excluding ones containing zero

Try this. The DISTINCT is replaced by a GROUP BY which for this example does the same thing but is more deliberate. Run the inner select to see what 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

March 4, 2009 at 4:33 am

#952993
RE: How to find Week start date and Week end date

Michael Valentine Jones (3/3/2009)
The code you posted doesn't work, unless you happen to have a table named Numbers in the current database.
Read about the tally (numbers) table here[/url]. In this...
^{“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

March 4, 2009 at 3:05 am

#952971
RE: How to find Week start date and Week end date

Yep that works, Lynn:
DROP TABLE #Dates
CREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, DATENAME(dw,aDate) AS [Day],
datediff(dd, aDate, SergiyStart) AS DaysToStartweek,
SergiyStart,...
^{“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

March 3, 2009 at 10:55 am

#952596
RE: How to find Week start date and Week end date

Michael, you might want to check your code, too...
DROP TABLE #Dates
CREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, SergiyStart, SergiyEnd, CJMStart,...
^{“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

March 3, 2009 at 10:20 am

#952571

Viewing 15 posts - 8,731 through 8,745 (of 10,143 total)