ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,836 through 8,850 (of 10,143 total)

RE: Calculate Month Diffrence

CrazyMan (2/3/2009)
Hi Chris
It works, it returns all rows with date (2nd) despite of what ever month...
^{“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

February 3, 2009 at 8:52 am

#937200
RE: Calculate Month Diffrence

Test with some extra data, CrazyMan. It doesn't work.
Insert into #Month Values('2008-12-30 23:48:56.024')
Insert into #Month Values('2008-12-31 23:48:56.024')
Insert into #Month Values('2009-01-01 23:48:56.024')
Insert into #Month Values('2009-01-02 23:48:56.024')
Insert into #Month Values('2009-01-03 23:58:53.024')
Insert 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

February 3, 2009 at 8:30 am

#937170
RE: Calculate Month Diffrence

CrazyMan (2/3/2009)
Hi Visal
Thanks, i need all records that more than a months old despite of year,...
^{“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

February 3, 2009 at 7:56 am

#937143
RE: Huge Query - PageIOLatch_SH among other things..

There's another possible angle here. Where is the code running from? Is it a query placed in a command object in Crystal or from a stored procedure? How many rows...
^{“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

February 3, 2009 at 6:01 am

#937049
RE: how to access whole row in a cursor

You can use min on varchar. Hopwever, it might not give the same result as you were originally getting. This is closer, using correlated subqueries:
SELECT d.*
INTO #feedrecord
FROM (
SELECT f.*,
VENDORNUM =...
^{“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

February 3, 2009 at 4:31 am

#937010
RE: Selection from Parent Table as well as child table

sar_kan25 (2/2/2009)
Hello guy's
I think you havent got my problem.......
I m providing an example
...snip...
please help
Aha!
Exactly as you would find on an order or an invoice?
Can you please set up some...
^{“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

February 3, 2009 at 2:41 am

#936965
RE: how to access whole row in a cursor

vikas.saxena (2/3/2009)
one more thing chris.....
..snip..
the code above will fetch all the records into feedtable or i need to apply something else for it.
The purpose of this statement is to pull...
^{“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

February 3, 2009 at 2:36 am

#936962
RE: POOR QUERY PERFORMANCE

nikhil.verma (2/2/2009)
Thanks a lot for ur help Chris !!!1
One error i am getting with your code :
Invalid column name 'error_code'.
I'm sorry I can't go any further with 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

February 3, 2009 at 2:33 am

#936959
RE: CASE ISNULL

Philip Horan (1/31/2009)
Hi Chris, yes the same values. The query is for a report. Currently the report returns a blank for NULL's, I thought it would look better if 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

February 2, 2009 at 8:09 am

#936452
RE: how to access whole row in a cursor

GilaMonster (2/2/2009)
Chris Morris (2/2/2009)
Gail - nice weapon! Horrible haircut:P
I'll change the avatar back in a few days. That's the second comment about the haircut.
Funny thing is, it's not far different...
^{“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

February 2, 2009 at 7:55 am

#936440
RE: POOR QUERY PERFORMANCE

Resolving the tables out of the views results in this:
SELECT it.error_code, COUNT(*) as occurances,
SUM(d.qty_tested) AS qty_tested,
SUM(d.qty_failed) AS qty_failed
FROM [assembly] as a
INNER JOIN tester_configuration as tc ON...
^{“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

February 2, 2009 at 7:45 am

#936427
RE: how to access whole row in a cursor

GilaMonster (2/2/2009)
From a quick glance, this doesn't need a cursor at all.
One question - if all 5 of the carrier names are blank, do you want 5 records in 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

February 2, 2009 at 6:51 am

#936389
RE: how to access whole row in a cursor

You're welcome.
This isn't a complicated process and there's certainly no need for a cursor. Processing the feed table row by row,
with up to six individual inserts per row, is...
^{“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

February 2, 2009 at 6:50 am

#936388
RE: Selection from Parent Table as well as child table

Exactly as you would find on an order or an invoice?
^{“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

February 2, 2009 at 5:44 am

#936353
RE: POOR QUERY PERFORMANCE

Thanks...also the view limit_set_view please.
Views of views can be performance hogs. Others have pointed out the correlated subqueries - they may not be much of a problem however combining 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

February 2, 2009 at 5:39 am

#936351

Viewing 15 posts - 8,836 through 8,850 (of 10,143 total)