ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,536 through 5,550 (of 10,143 total)

RE: Are the posted questions getting worse?

Koen Verbeeck (9/28/2012)
Grant Fritchey (9/27/2012)
THREADIZENS!
Who is going to make it to any of the SQL in the City events coming up in the next week+?
If you do one in Brussels...
^{“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 28, 2012 at 2:35 am

#1543247
RE: Group by column A but compare column B

Here's a fourth method:
-- solution
;WITH ParentsOnly AS (
SELECT ComputerName, ChildRows = COUNT(*)
FROM #MyTable
GROUP BY ComputerName
)
SELECT a.*, b.*, x.*
FROM ParentsOnly a
INNER JOIN ParentsOnly b ON b.ChildRows = a.ChildRows AND b.ComputerName...
^{“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 28, 2012 at 2:31 am

#1543245
RE: CAST (AS DECIMAL(30,5)) Behaving Differently

Shadab Shah (9/28/2012)
Hi,
I had written the lines of code as
Cast(SUM(A*B) / SUM(C) AS DECIMAL(30,5)) and
Cast(SUM(A*B) AS DECIMAL(30,5))/Cast(SUM(C) AS DECIMAL(30,5))
Conceptually both should return the same result , which 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

September 28, 2012 at 2:05 am

#1543236
RE: insert rows

harri.reddy (9/27/2012)
hi
i want to insert 1000 rows in a table without any loop.
how can i do it?
In what format are the 1000 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

September 28, 2012 at 2:03 am

#1543234
RE: Sorting multiple columns in various sort directions in a case statement

Barry Couch (9/27/2012)
CASE @SortColumn WHEN 'StreetName' THEN Maintainer + ', ' + StreetName END ASC,
but how could I do something like sorting by Maintainer ASC and StreetName DESC?
You will need...
^{“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 28, 2012 at 1:56 am

#1543227
RE: i want to write a stored procedure to display today and last week sale by tank

vinay.varaala (9/27/2012)
thanks a lot ..
can I subtract todaysales- weeksale as other column
many thanks
Yes of course. To keep the query nice, either call it as a CTE or use it as...
^{“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 27, 2012 at 8:39 am

#1542941
RE: Remove Decimals Without Rounding

vivekkumar341 (9/27/2012)
Try this.
DECLARE
@ta VARCHAR(MAX) = '107.3'
SELECT SUBSTRING(@ta,0,CHARINDEX('.',@ta,0)) + SUBSTRING(@ta,CHARINDEX('.',@ta,0) + 1,LEN(@ta))
Why? How is it relevant to this thread? The OP isn't even around any more - he's a tuna fisherman...
^{“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 27, 2012 at 8:00 am

#1542914
RE: Remove Decimals Without Rounding

Eugene Elutin (9/27/2012)
ChrisM@Work (9/27/2012)
Eugene Elutin (9/27/2012)
I have read a spec... 🙂
...
Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted...
^{“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 27, 2012 at 7:38 am

#1542897
RE: row_number replacement

komal145 (9/26/2012)
...
UPDATE TableC
SET step_desc= Tableb.StepDescription
FROM #MAX_STEPMAX_STEP WITH (NOLOCK)
JOIN TablebB WITH (NOLOCK) ON MAX_STEP.LoanNumber = b.LoanNumber
and MAx_step.Step=b.step
...
^{“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 27, 2012 at 7:29 am

#1542883
RE: i want to write a stored procedure to display today and last week sale by tank

This is a little more tricky but reads the tables only once.
SELECT
SumTodaySales = SUM(CASE WHEN CAST(tr.Reading_Date AS DATE) = dates.Today THEN tr.Sales ELSE 0 END),
SumLastweekSales = SUM(CASE...
^{“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 27, 2012 at 6:31 am

#1542835
RE: Remove Decimals Without Rounding

Eugene Elutin (9/27/2012)
I have read a spec... 🙂
...
Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted numbers 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

September 27, 2012 at 6:27 am

#1542829
RE: i want to write a stored procedure to display today and last week sale by tank

Here's the simplest way. Note that performance will suck because you're doing all of your table reads twice:
;WITH SalesToday AS (
select
SumSales = SUM(tr.Sales),
t1.Tank
from Transactions as tr...
^{“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 27, 2012 at 6:15 am

#1542822
RE: Query to find out latest data of each student

diptidjadhav (9/27/2012)
Hey thanks Jinxsee and ChrisM@Work. Your solution works perfect. Thanks a lot.
One more question for Jinxsee, Suppose if we are using row_number() for a huge records it will minimize...
^{“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 27, 2012 at 6:11 am

#1542819
RE: Query to find out latest data of each student

Jinxsee (9/27/2012)
My bad! Mental note added. ^^.
No worries - now how about fixing your code so that it runs against the OP's sample data? You don't need the GROUP BY.
^{“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 27, 2012 at 5:47 am

#1542808
RE: Query to find out latest data of each student

Jinxsee (9/27/2012)
select
VALUE, YEAR, MONTH, MARK from
(select VALUE, YEAR, MONTH, MARK, ROW_NUMBER() OVER(PARTITION BY VALUE ORDER by...
^{“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 27, 2012 at 5:43 am

#1542806

Viewing 15 posts - 5,536 through 5,550 (of 10,143 total)