ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,511 through 3,525 (of 10,143 total)

RE: Need help with runaway query using multiple APPLY joins

Check your table aliases:
SELECT s.state_name
, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits
, SUM(DistinctPermits) AS DistinctPermits
FROM States S
CROSS APPLY (
SELECT w.StateID, COUNT(*) as DistinctPermits
FROM Permit P
INNER JOIN Well W1 ON P.WellID...
^{“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 21, 2014 at 3:28 am

#1698987
RE: add a filter criteria to SQL statement

rlsublime (3/20/2014)
I am trying to add a '<19' filter to the following sql code. And ideas would be appreciated as I am a sql newbie. thanks
(Select sum(NVL(sh_productivehours,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

March 21, 2014 at 2:51 am

#1698978
RE: Bitwise OR function

Here's a sandpit version very similar to what you currently have. The only downside is the ugly aggregate - but it's a very simple ugly aggregate. I guess it depends...
^{“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 20, 2014 at 9:56 am

#1698763
RE: Checking varchar data to see if it can be cast as decimal

Can you augment the sample table in my last post to account for your observations? Thanks.
^{“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 20, 2014 at 9:04 am

#1698728
RE: Checking varchar data to see if it can be cast as decimal

SQL Server will apply the filter and the conversion in whichever order results in the lowest cost - and in this case, the order chosen is breaking your query. Here's...
^{“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 20, 2014 at 7:50 am

#1698690
RE: How to find a missing sequence in a column?

You're welcome, thanks for the feedback.
^{“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 20, 2014 at 7:22 am

#1698676
RE: How to find a missing sequence in a column?

There are loads of ways of doing this. Here are two.
drop Table #Sample
Create Table #Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) ...
^{“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 20, 2014 at 4:46 am

#1698627
RE: Adding Where Condition on Select query on a View : Error converting data type varchar to bigint.

The numeric test and the cast to bigint have to occur together and in the correct order.
CREATE VIEW ChildEmployees
AS
SELECT x.EmpID
FROM MasterEmployees
CROSS APPLY (
SELECT EmpiD CASE
WHEN ISNUMERIC(EmpID) =...
^{“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 18, 2014 at 10:47 am

#1698121
RE: Bitwise OR function

Here's a solution which will work with many values. First the UDF:
ALTER FUNCTION fn_BITWISE_OR
(
@col1 VARCHAR(3)
)
RETURNS VARCHAR(3)
AS
BEGIN
-- try row-wise
DECLARE @LastValue INT
SELECT @LastValue = 000
SELECT @LastValue = @LastValue | d.bit_flag
-- substitute...
^{“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 18, 2014 at 10:10 am

#1698111
RE: Bitwise OR function

joakim.fenno (3/18/2014)
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and...
^{“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 18, 2014 at 9:46 am

#1698092
RE: Bitwise OR function

Although just about every reference on the first page returned by Google specifies two operands, the TSQL BITWISE OR actually works with a chain like this:
-- on a single...
^{“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 18, 2014 at 9:36 am

#1698082
RE: Bitwise OR function

joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it...
^{“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 18, 2014 at 9:25 am

#1698070
RE: Bitwise OR function

joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far...
^{“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 18, 2014 at 9:12 am

#1698060
RE: Bitwise OR function

joakim.fenno (3/18/2014)
I be happy to use the built in T-SQL functions if that is possible.
But the function needs to work as an aggregate function.
I have to be able to execute...
^{“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 18, 2014 at 8:53 am

#1698053
RE: intersect

karthik babu (3/18/2014)
as early as possible.. since its EOD today we have 12+ hrs 🙂
How about pasting the assignment here so we don't have to guess what you're trying 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

March 18, 2014 at 8:06 am

#1698027

Viewing 15 posts - 3,511 through 3,525 (of 10,143 total)