ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,946 through 3,960 (of 10,143 total)

RE: Replace

Is this PostgreSQL?
^{“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

October 3, 2013 at 3:37 am

#1655469
RE: I need help Please :(

Hi Sharon
Can you confirm which SQL Server version you are using?
^{“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

October 3, 2013 at 12:57 am

#1655412
RE: SQL Help in grouping

gksharmaajmer (10/2/2013)
Thank you Chris for your continue support. Probably I am not able to find a bug in the query but when I am saying :
SELECT *
FROM #Results...
^{“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

October 3, 2013 at 12:55 am

#1655411
RE: wish to add rows having NULL values as placeholders for "missing" dates

polkadot (10/2/2013)
YIKES, I see the error. The where should be d.d>=
and not dbj.starttime >=
This works:
select * from cannedbackupjobs dbj
full outer join _dates d on d.d = CONVERT(VARCHAR(10), dbj.starttime, 120)
where...
^{“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

October 2, 2013 at 9:13 am

#1655244
RE: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Server-side paging.[/url]
^{“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

October 2, 2013 at 8:49 am

#1655236
RE: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

ORDER BY operates at the outermost level of a query and on the output rows. Where you've placed it in your sproc looks correct.
It doesn't make logical sense to...
^{“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

October 2, 2013 at 8:20 am

#1655227
RE: performance issue

As Gail states.
You may get some mileage from this:
-- Extract this chunk of your query separately
--------------------------------------------------------------------
JOIN OSS_BENCHMARK_PRICE_REFERENCE PR
ON L.DAILY_BENCHMARK_LOCAL_SEQ=PR.DAILY_BENCHMARK_LOCAL_SEQ
JOIN RD_MARKET_PRICE MP
ON MP.MARKET_PRICE_SEQ=PR.MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.FUEL_MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.GAS_MARKET_PRICE_SEQ...
^{“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

October 2, 2013 at 6:21 am

#1655191
RE: SQL Help in grouping

-- like this
SELECT
s.course_id, s.course_name,
roll_no = MIN(s.roll_no),
student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END,
s.geog_marks, s.math_marks
FROM #students s
INNER JOIN (
SELECT course_id, geog_marks, math_marks,...
^{“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

October 2, 2013 at 5:52 am

#1655183
RE: SQL Help in grouping

The results of my query match your desired output table. What do you want me to do?
^{“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

October 2, 2013 at 4:33 am

#1655169
RE: SQL Help in grouping

#students is a local temporary table I used instead of the permanent sample table students. Just remove the #.
g.SetSize is the calculated row count of the set in the partition...
^{“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

October 2, 2013 at 4:01 am

#1655162
RE: SQL Help in grouping

SELECT
s.course_id, s.course_name,
roll_no = MIN(s.roll_no),
student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END,
s.geog_marks, s.math_marks
FROM #students s
INNER JOIN (
SELECT course_id, geog_marks, math_marks, SetSize = 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

October 2, 2013 at 2:21 am

#1655150
RE: update from multiple tables

parthmeister (10/2/2013)
Hi Folks,
i am using 4 tables company, users, ARSAP, ARCUS to update a column in company table.
the tsql i am using is below.
UPDATE c
SET c.comp_primaryuserid = u.user_userid
FROM Company...
^{“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

October 2, 2013 at 1:46 am

#1655138
RE: Slow Script

This I reckon is the correct way to write your query:
select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3
from T1
join T2
on T1.COL4 = T2.COL3
where T2.COL4 != 'data1'
and T1.COL1 is not NULL
and T1.COL5...
^{“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

October 1, 2013 at 8:56 am

#1654949
RE: Are the posted questions getting worse?

jasona.work (10/1/2013)
Well, should be a rather interesting next couple days / weeks / (REALLY hope not) months with the US Government going into shutdown.
As some of you know, I'm now...
^{“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

October 1, 2013 at 6:36 am

#1654867
RE: I need help Please :(

sharon.ferrer (9/30/2013)
I have a table name Employee Logs
table consist of EmpID, Empname,Logdatetime.
I can generate the first in and last out by using max and min. Unfortunately there is an employee...
^{“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

October 1, 2013 at 6:10 am

#1654852

Viewing 15 posts - 3,946 through 3,960 (of 10,143 total)