ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,971 through 8,985 (of 10,143 total)

RE: Grouping on Date Range / Overlap

Using the "running totals" method to take care of gaps...
[font="Courier New"]SET DATEFORMAT DMY
GO
WITH testData(Start, [Stop] , v1, v2, ID )
AS
(
SELECT CAST('01/01/09' AS DATETIME), CAST('31/01/09' AS DATETIME), 1399, 2499, 729275 UNION
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

January 7, 2009 at 4:15 am

#922412
RE: Grouping on Date Range / Overlap

The first start datetime and the last stop datetime in one row, for each ID?
^{“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 7, 2009 at 2:55 am

#922379
RE: Grouping rows together

Anu, a good first step would be to tidy up the original query. Check this against your original, it's much simpler - and therefore easier to work with, and 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

January 7, 2009 at 2:30 am

#922364
RE: problem in query

pat (1/6/2009)
it is only doing to one worksheet then!!
Nah...IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname AND day = @day))
^{“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 6, 2009 at 2:23 pm

#922175
RE: problem in query

The day number would be ideal, why don't you use that instead of @Testname?
^{“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 6, 2009 at 1:50 pm

#922147
RE: problem in query

pat (1/6/2009)
then how will i write the insert and update please guide me!!
thanks
Sure! Remember that PRINT statement? Post what gets printed, say the first 10 lines for one worksheet.
^{“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 6, 2009 at 1:36 pm

#922133
RE: problem in query

pat (1/6/2009)
what?
@Testname is assigned only outside the WHILE loop (WHILE @currentRow <= @dataEndRow) so remains static for each iteration.
This conditional ...IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))
...
^{“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 6, 2009 at 1:09 pm

#922104
RE: problem in query

pat (1/6/2009)
yes
Top work Pat, you've cracked 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

January 6, 2009 at 12:54 pm

#922081
RE: problem in query

Yes, it does. Is it always the same?
IF (EXISTS (SELECT fname FROM testtable WHERE fname = @Testname))
BEGIN
...
^{“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 6, 2009 at 12:46 pm

#922069
RE: problem in query

pat (1/6/2009)
no i does on insert but rest of them update like if worksheet has data till 24 rows it will run update till 24 but insert is one only!!
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

January 6, 2009 at 12:20 pm

#922042
RE: problem in query

pat (1/6/2009)
Hi,
i did that already and that's why i said that it only does one insert per worksheet(total count of worksheets are 29) and it updates the same reocords.
thanks
Pat
So 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

January 6, 2009 at 12:01 pm

#922027
RE: problem in query

pat (1/6/2009)
here is the thing when i did that the you u suggest.
I only does one insert and rest of them update. and file has 29 worksheet so i get...
^{“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 6, 2009 at 11:51 am

#922019
RE: problem in query

pat (1/6/2009)
i am blank now what do i need to do and how?
Try this for starters IF (EXISTS...
^{“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 6, 2009 at 11:35 am

#922002
RE: problem in query

pat (1/6/2009)
i only have two condition right now to do insert and update others are related to excel formating thanks
also if i do only insert statement it work like charm....
^{“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 6, 2009 at 11:00 am

#921974
RE: problem in query

pat (1/6/2009)
still the same it is not inserting all records. the file has 695 records and i only get 29 with initial setup or 204 after having is null criteria!!
You've...
^{“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 6, 2009 at 10:39 am

#921962

Viewing 15 posts - 8,971 through 8,985 (of 10,143 total)