ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,446 through 2,460 (of 10,143 total)

RE: Data comes correct with Select but when convert it to update, it does not work

drew.allen (6/10/2015)
ChrisM@Work (6/10/2015)
Your syntax is a little out:
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE ~~#TEMP2~~ CTE
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum...
^{“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

June 11, 2015 at 1:56 am

#1804603
RE: How to select records only when their status has been changed?

jay-125866 (6/10/2015)
Thank you, Chris. Your solution works about right.
The issue remaining is that the Product Pencil is also shown in the result. It's not supposed to be there since...
^{“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

June 11, 2015 at 1:54 am

#1804602
RE: Data comes correct with Select but when convert it to update, it does not work

Your syntax is a little out:
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE ~~#TEMP2~~ CTE
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 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

June 10, 2015 at 10:09 am

#1804408
RE: How to select records only when their status has been changed?

Try this:
;WITH PaidProducts AS (
SELECT *,
rn1 = ROW_NUMBER() OVER (PARTITION BY productId ORDER BY logDate DESC),
rn2 = CASE ProductStatus WHEN 'paid' THEN ROW_NUMBER() OVER (PARTITION BY productId, ProductStatus ORDER...
^{“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

June 10, 2015 at 9:40 am

#1804391
RE: How to select records only when their status has been changed?

jay-125866 (6/9/2015)
...select only the products that had been paid for, but later their status has been changed.
I need to report the original paid date, the most recent status, and most...
^{“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

June 10, 2015 at 9:16 am

#1804380
RE: Case statement with between not calculating correctly

gissah (6/10/2015)
Can someone please look at this code and tell me what I am doing wrong. the middle 2 conditions are not calculating correctly. I am getting zero as 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

June 10, 2015 at 8:35 am

#1804364
RE: Ranking duplicate contacts using multiple columns for ranking. Phone and emails that are blank can match to phones and emails that are not blank.

jeff.born (6/9/2015)
ChrisM@Work (6/9/2015)
@jeff, please can you amend your sample data set so that it contains each of the conditions you describe? Thanks. Then run this and have a look at...
^{“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

June 10, 2015 at 8:03 am

#1804350
RE: Need help in query

Zohaib Anwar (6/10/2015)
ChrisM@Work (6/10/2015)
You should be expecting a final query something like this:
;
WITH PreAgg AS (
SELECT
p.id,
HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,
paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount -...
^{“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

June 10, 2015 at 7:15 am

#1804325
RE: Need help in query

You should be expecting a final query something like this:
;
WITH PreAgg AS (
SELECT
p.id,
HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,
paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount - tdPaid.CreditAmount ELSE 0...
^{“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

June 10, 2015 at 6:40 am

#1804306
RE: Need help in query

Zohaib Anwar (6/10/2015)
ChrisM@Work (6/10/2015)
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no...
^{“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

June 10, 2015 at 6:15 am

#1804294
RE: Need help in query

Zohaib Anwar (6/10/2015)
If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:
...
^{“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

June 10, 2015 at 6:01 am

#1804288
RE: Need help in query

Zohaib Anwar (6/10/2015)
ChrisM@Work (6/10/2015)
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no...
^{“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

June 10, 2015 at 5:24 am

#1804273
RE: Need help in query

Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then 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

June 10, 2015 at 5:04 am

#1804264
RE: Are the posted questions getting worse?

Grant Fritchey (6/9/2015)
So tempted to whip out "DO YOU KNOW WHO I AM?"
Time to walk away from the forums for a few...
Where the **** is that tent?
Good lord Grant! 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

June 9, 2015 at 9:32 am

#1804005
RE: Are the posted questions getting worse?

Steve Jones - SSC Editor (6/9/2015)
Eirikur Eiriksson (6/4/2015)
Should be as simple as
..TOP(1)...
WHERE POST.VISIBLE = TRUE;
😎
Nothing is ever simple, and the queries that do this potentially slow way down with...
^{“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

June 9, 2015 at 9:26 am

#1804002

Viewing 15 posts - 2,446 through 2,460 (of 10,143 total)