ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,516 through 7,530 (of 10,143 total)

RE: Today's Random Word!

crookj (8/12/2010)
WOTD - Octopus
(The sour gummie type - my granddaughter's favorite candy)
Joe
Yum!
^{“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

August 12, 2010 at 10:04 am

#1206154
RE: Where are you?

Dewes van Lohuizen (8/12/2010)
@Sushant
Brandie is right: Holland is a province in the Netherlands, we have North Holland and South Holland.
People often says Holland and mean The Netherlands.
And football: Spain was...
^{“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

August 12, 2010 at 7:52 am

#1206023
RE: delete duplicated data

;WITH OrderedRows AS (
SELECT RowNo = ROW_NUMBER() OVER (PARTITION BY acolumnlist ORDER BY acolumnlist)
FROM MyTableWithDupes
)
DELETE FROM OrderedRows WHERE RowNo > 1
Edited to show that the script includes DELETE.
^{“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

August 12, 2010 at 7:06 am

#1205977
RE: Today's Random Word!

dbowlin (8/12/2010)
Vacation......
A lot of people must be on vacation because the post frequency to this forum has dropped dramatically.
I wish I was on vacation too.
🙂
Me too.
Getting tired now after...
^{“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

August 12, 2010 at 6:56 am

#1205967
RE: Query Help

khushbu (8/12/2010)
Thanks a TON!!!!
The query works very well.
the enddate for rowid = 3 comes form startdate-1 from row id = 4.....
and for last row id there is default value.
You're...
^{“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

August 12, 2010 at 5:32 am

#1205923
RE: Query Help

Where does the end-date come from, for rowid 3?
^{“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

August 12, 2010 at 5:20 am

#1205919
RE: Query Help

khushbu (8/12/2010)
posting a sample output required.
Rowid deccode tarief Startdate Enddate
1 010127 4300 2005-01-01 2005-12-31
2 010127 4300 2006-01-01 2007-12-31
3 010128 4300 2008-01-01 2004-12-31
4 010229 4300 2005-01-01 2005-12-31
5 010230 4300 2006-01-01 2099-01-01...
^{“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

August 12, 2010 at 5:19 am

#1205918
RE: Query Help

khushbu (8/12/2010)
Thanks for all the help!
But may be I did not get the partition part .
No, a startdate from one deccode cannot be used as the enddate for another.
The end...
^{“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

August 12, 2010 at 5:04 am

#1205912
RE: Query Help

khushbu (8/12/2010)
the data is partitioned on deccode, tarief and startdate
That's a description of a row, not a partition, which is a group of rows with a common value in one...
^{“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

August 12, 2010 at 4:43 am

#1205907
RE: Query Help

khushbu (8/12/2010)
e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.
I know NULL...
^{“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

August 12, 2010 at 4:31 am

#1205902
RE: Query Help

khushbu (8/12/2010)
The query does not give result as desired.
The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.
The query seems to be substracting 1 from 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

August 12, 2010 at 3:27 am

#1205878
RE: I need a better way to do this

Dennis, I'm not sure if this will work because there's nothing to test against, but give it a try. Even if it doesn't, it should give you a few ideas:
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

August 12, 2010 at 3:21 am

#1205872
RE: Query Help

If RowID has no gaps and can be reliably used to identify "the other row", then something like this should work for you:
CREATE TABLE #MyTable (
Rowid INT,
deccode CHAR(6),
tarief...
^{“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

August 12, 2010 at 2:56 am

#1205858
RE: Query Help

How do you know which row the enddate should be derived from?
Are there gaps in the sequence suggested by column RowID?
^{“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

August 12, 2010 at 2:46 am

#1205854
RE: I need a better way to do this

It's not rocket science, converting a query into a stored procedure or vice versa. So why not let us assume that you are writing a stored procedure? Then if you...
^{“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

August 11, 2010 at 9:16 am

#1205437

Viewing 15 posts - 7,516 through 7,530 (of 10,143 total)