ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,676 through 6,690 (of 10,143 total)

RE: Calculate component percents without a cursor

gcresse (9/6/2011)
ChrisM,
This works fantastic with the test data! Thanks for your efforts. Now I will see if I can make it work with my actual tables.
I had actually...
^{“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

September 7, 2011 at 1:46 am

#1377814
RE: joining 3 tables not getting valid resultset

maruthipuligandla (9/6/2011)
...Any update on the query? ...
Nope, still waiting for usable sample data scripts.
^{“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

September 6, 2011 at 6:56 am

#1377366
RE: Strange SQL Server Evaluation of 0 (bit) = '' (Empty string) or even non empty string

steveb. (9/6/2011)
... an empty string does not equal NULL, to do this with your code you will need.
SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 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

September 6, 2011 at 5:44 am

#1377312
RE: joining 3 tables not getting valid resultset

maruthipuligandla (9/6/2011)
Hi,
I've generated scripts with data for 4 tables, please find the attached sql script files..
The image columns aren't required for this exercise, take up an inordinate amount of space,...
^{“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

September 6, 2011 at 4:20 am

#1377267
RE: joining 3 tables not getting valid resultset

maruthipuligandla (9/6/2011)
Hi,
I didnt get what exactly you asking me for? Should i send scripts for 3 tables with data or what?...
Four tables. If you take the time to read 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

September 6, 2011 at 4:03 am

#1377259
RE: joining 3 tables not getting valid resultset

maruthipuligandla (9/6/2011)
Please find the sample data screen attached herewith from CandidateInterview table, where we can see the no:of candidates applied for a job for particular status..
Please supply DDL and DML...
^{“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

September 6, 2011 at 3:50 am

#1377254
RE: Calculate component percents without a cursor

Here's a mod which works across different products with different numbers of ingredients:
USE tempdb
GO
CREATE TABLE Transactions(
trnKey ...
^{“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

September 5, 2011 at 10:06 am

#1377088
RE: Select history of records

kramaswamy (9/2/2011)
just out of curiousity Chris, I assume there's no real difference between writing the query like that, or like "with ___ AS (blah) SELECT FROM cte WHERE count =...
^{“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

September 5, 2011 at 4:34 am

#1376996
RE: Calculate component percents without a cursor

gcresse (9/1/2011)
Oops. You are correct, I have a mistake in the spreadsheet calculations.
I forgot to mention in my original post that the product is liquid and stored 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

September 5, 2011 at 4:19 am

#1376993
RE: Issue with Join ...

mattech06 (9/1/2011)
Thanks guys for the speedy response. I actually used something similar using WHERE instead and it's returned the same amount of rows. However both WHERE and INNER JOIN seems...
^{“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

September 1, 2011 at 8:58 am

#1376014
RE: Issue with Join ...

Here's the simplest most basic script which makes a lot of assumptions:
SELECT
p.dbPatCnt,
p.dbPatFirstName,
p.dbPatLastName,
f.dbPhoneNumber,
f.dbPhoneTypeID
FROM Patients p
INNER JOIN LnkPhone l ON l.dbKeyCnt = p.dbPatCnt
INNER JOIN Phone f 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

September 1, 2011 at 8:27 am

#1375990
RE: How to join a table-valued Function

lalit.madan (7/19/2011)
/*this behaves like inner join*/
select * from freight_manager
cross apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)
/*this behaves like cross join*/
select * from freight_manager
outer apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)
🙂
I think you will find...
^{“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

September 1, 2011 at 7:25 am

#1375940
RE: Select history of records

Here's another way:
SELECT
question,
Answer,
answereddate,
accredappid
FROM (
SELECT
question,
Answer,
answereddate,
accredappid,
AnswerCount = COUNT(*) OVER (PARTITION BY accredappid, question)
FROM answers
) d
WHERE AnswerCount > 1
ORDER BY accredappid, question
^{“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

September 1, 2011 at 4:08 am

#1375870
RE: Calculate component percents without a cursor

The calculations in your spreadsheet appear to be incorrect, this is what I think it should look like:
[font="Courier New"]
Ingr Pct Ingr...
^{“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

September 1, 2011 at 3:33 am

#1375852
RE: SUM relative to row date value

Cadavre (8/31/2011)
Nice one Chris 😉
Told you someone would simplify it Greg
Wonder if it works on the real data though?
My money's on CC's script, it's more flexible 😎
^{“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 31, 2011 at 9:44 am

#1375567

Viewing 15 posts - 6,676 through 6,690 (of 10,143 total)