ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,466 through 3,480 (of 10,143 total)

RE: Update FIELD from Select Aggregated Statement on second table

You're welcome. Thanks for the feedback. If you have any questions about the solution, post back.
^{“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

April 10, 2014 at 9:29 am

#1704951
RE: Update FIELD from Select Aggregated Statement on second table

Here's a guess:
-- ALWAYS check first using a SELECT
SELECT
l.rack, l.bay, l.height, l.qty, x.TotalStock, x.Warehouse, x.Bin
FROM [SLGWarehouseLayout] l
CROSS APPLY (
SELECT SUM(i.QtyOnHand1) AS TotalStock, i.Warehouse, i.Bin
FROM InvMultBin i
WHERE i.Warehouse 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

April 10, 2014 at 7:43 am

#1704901
RE: Update FIELD from Select Aggregated Statement on second table

The inner select is uncorrelated - it will update every row of the target.
Which table contains column QtyOnHand1?
^{“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

April 10, 2014 at 7:05 am

#1704877
RE: generate insert statement from table

djj (4/7/2014)
Why would you want to scan the table four times when you can do it just once?
Because I forget about CROSSAPPLY as I have not used it as often...
^{“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

April 7, 2014 at 8:31 am

#1703814
RE: generate insert statement from table

What do you want your output table to look like, using the sample data you've provided?
^{“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

April 7, 2014 at 6:46 am

#1703747
RE: Are the posted questions getting worse?

SQLRNNR (4/4/2014)
Sean Lange (4/4/2014)
TomThomson (4/3/2014)
Steve Jones - SSC Editor (4/3/2014)
Revenant (4/3/2014)
Sean Lange (4/3/2014)
Greg Edwards-268690 (4/3/2014)
it was clear they had no idea what they wanted or needed.
But finish it by Friday.
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

April 4, 2014 at 7:51 am

#1703310
RE: Need query to sum up the previous values

"Running totals": ssc has a brilliant article by Jeff Moden here[/url] which covers most of the methods currently used.
^{“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

April 2, 2014 at 6:41 am

#1702425
RE: Help need in Avoiding Loop

born2achieve (4/2/2014)
...Any suggestions or improvisations please, also how can i make the status=1 after the accomplishment....
In the users_transaction table or the users table? Assuming the former, because we...
^{“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

April 2, 2014 at 6:36 am

#1702421
RE: Need help with runaway query using multiple APPLY joins

bncaffey (3/31/2014)
So does anyone have experience writing queries with multiple APPLY joins?
Yes, but I've not observed this before. I can't see anything obvious in the plans either. If you have...
^{“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 31, 2014 at 9:32 am

#1701651
RE: Like operator - Query performance issue

As an aside, it seems odd that you are searching so many of those columns. Can you show us what the data looks like?
^{“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 31, 2014 at 7:00 am

#1701563
RE: Are the posted questions getting worse?

jasona.work (3/31/2014)
ChrisM@Work (3/31/2014)
Way back in the early 90's many of us FoxPro programmers waited with bated breath for MS' release of a product which included a crude implementation of SQL....
^{“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 31, 2014 at 6:56 am

#1701562
RE: Are the posted questions getting worse?

andrew gothard (3/29/2014)
ChrisM@Work (3/28/2014)
WayneS (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of these pieces of sh...
^{“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 31, 2014 at 1:56 am

#1701495
RE: Are the posted questions getting worse?

Revenant (3/28/2014)
TomThomson (3/28/2014)
Ville-Pekka Vahteala (3/28/2014)
TomThomson (3/28/2014)
Ville-Pekka Vahteala (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of these...
^{“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 31, 2014 at 1:51 am

#1701493
RE: Are the posted questions getting worse?

Sean Lange (3/28/2014)
ChrisM@Work (3/28/2014)
Sean Lange (3/28/2014)
ChrisM@Work (3/28/2014)
Sean Lange (3/28/2014)
dwain.c (3/28/2014)
Ville-Pekka Vahteala (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database...
^{“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 28, 2014 at 8:38 am

#1701141
RE: Are the posted questions getting worse?

Sean Lange (3/28/2014)
ChrisM@Work (3/28/2014)
Sean Lange (3/28/2014)
dwain.c (3/28/2014)
Ville-Pekka Vahteala (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of...
^{“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 28, 2014 at 8:25 am

#1701132

Viewing 15 posts - 3,466 through 3,480 (of 10,143 total)