ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,981 through 1,995 (of 10,143 total)

RE: CASE WHEN SELECT Help.

Is this what you mean?
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value > M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') THEN 'Red'
WHEN A.[Measure] IN ('N2F-03') AND A.Value < M.Threshold...
^{“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 29, 2015 at 5:09 am

#1836301
RE: Are the posted questions getting worse?

Koen Verbeeck (10/27/2015)
...
Happy birthday geezer. It's beer O'clock 🙂
^{“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 28, 2015 at 10:54 am

#1836149
RE: In Query - All rows with same status

chindilog (10/28/2015)
I get it. All rows shows marked but column name shows "constant", how do I change "constant" to "status"?
SELECT 'Packed', so.num AS ordernum, so.customercontact,
so.dateissued, so.datecreated, so.datecompleted
FROM ship
LEFT 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

October 28, 2015 at 10:49 am

#1836147
RE: In Query - All rows with same status

chindilog (10/28/2015)
Hello Everyone. This might be very simple, but need some help. I get records from a query whose status is "Marked". I need another column to be added 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 28, 2015 at 10:33 am

#1836135
RE: CASE WHEN

dramaqueen (10/28/2015)
Hello,
Me again really sorry.
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be...
^{“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 28, 2015 at 10:20 am

#1836129
RE: where clause help

Sean Lange (10/28/2015)
kat35601 (10/28/2015)
...
....
Then you have inequality conditions in you where clause. This is nonSARGable and prevents index seeks.
...
Not always:
CREATE TABLE #MyTable (ID INT IDENTITY(1,1) NOT NULL, Inequality CHAR(1))
INSERT...
^{“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 28, 2015 at 10:17 am

#1836127
RE: where clause help

-- is wm.lastupdate a datetime?
SELECT *
FROM WIP_master wm
LEFT JOIN m1_DC.dbo.SalesOrders so
ON so.ompSalesOrderID = wm.ompSalesOrderID
LEFT JOIN m1_DC.dbo.UTRUCK t
ON t.UTRTRUCKNUMBER = so.UOMPTRUCKNUMBER
WHERE (redtag='Y' OR so.uompschedulenumber LIKE 'S%')
AND so.ompclosed <>...
^{“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 28, 2015 at 9:52 am

#1836119
RE: Optimizer Building different Execution plans

jody.a.morgan (10/28/2015)
If I select the "Select" in the Execution plans and click properties the "Reason for Early Termination of Statement Optimization" is "Time Out"
The query is too complex for SQL...
^{“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 28, 2015 at 9:18 am

#1836105
RE: Optimizer Building different Execution plans

The queries are the same to you but they are different queries to SQL Server, hence different execution plans.
Right-click on the leftmost operator - the SELECT - and examine 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

October 28, 2015 at 7:01 am

#1836017
RE: Building a tree view using t-sql

/*
DROP TABLE #TREE
create table #tree (Position VARCHAR(10), level int, seq int, value varchar(200))
insert into #tree
select '1', 2,2,'Earth' union all
select '1.1', 3,3,'Europe' union all
select '1.1.1', 4,4,'France' union all
select '1.1.2', 4,5,'Germany' union...
^{“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 28, 2015 at 4:14 am

#1835969
RE: Date Function (Possibility)

SQLPain (10/27/2015)
Thanks Scott, I am using the GroupBy and having clause but using them is not making any difference as results are coming out to be exact same. Funny thing...
^{“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 27, 2015 at 10:38 am

#1835854
RE: SUM Case When (decimal's!)

dramaqueen (10/27/2015)
Here is the design view of the audit table. I am putting the data into the value field.
Can you post the code?
^{“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 27, 2015 at 10:29 am

#1835849
RE: SUM Case When (decimal's!)

dramaqueen (10/27/2015)
Hi,
Can someone please explain why this is happening.
I am using a select query to do the following;
SUM(CASE WHEN [Measure] = 'XXX' THEN 1 ELSE 0 END) AS 'SEN-04'
The numbers...
^{“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 27, 2015 at 10:21 am

#1835843
RE: Scripting a query that needs all values.

This type of problem has a name - Relational Division - and a number of solutions. This is the simplest solution:
CREATE TABLE #ItemsMap (TagID INT, ItemID INT)
INSERT INTO #ItemsMap (TagID,ItemID)...
^{“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 27, 2015 at 9:39 am

#1835828
RE: test

And121 (10/27/2015)
test
This is the production environment. Please contact your DBA.
^{“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 27, 2015 at 7:25 am

#1835777

Viewing 15 posts - 1,981 through 1,995 (of 10,143 total)