ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,426 through 7,440 (of 10,143 total)

RE: SubSelect Statement not returning accurate results

You forgot to correlate your correlated subquery:
SELECT OwnerStaff_Branch,
COUNT(WorkOrderID) AS MonthTotal,
(SELECT COUNT(WorkOrderID) AS Expr1
FROM WorkOrderReportView
WHERE (dbo.DateOnly(CreatedOn) = dbo.DateOnly(GETDATE())
AND OwnerStaff_Branch = w.OwnerStaff_Branch)
) AS DailyTotal
FROM WorkOrderReportView w
WHERE (MONTH(CreatedOn) = MONTH(GETDATE())) AND (YEAR(CreatedOn)...
^{“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 9, 2010 at 9:12 am

#1218701
RE: If Exists UPDATE ELSE INSERT

The check for existence looks way too complicated - this should work:
INSERT INTO SERVERS_V
(
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
.
.
.
SERVER_TIMEZONE
)
SELECT
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
.
.
.
SERVER_TIMEZONE
FROM SERVERS_VT
WHERE NOT EXISTS (SELECT 1 FROM SERVERS_V WHERE SERVER_ID = SERVERS_VT.SERVER_ID)
^{“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 9, 2010 at 8:49 am

#1218676
RE: How to use IF in designing a view?

Vasudev Tantry (9/9/2010)
Thanks Crazy..
You're welcome, Forum Newbie 😛
^{“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 9, 2010 at 8:10 am

#1218613
RE: How to use IF in designing a view?

Use CASE.
IF enables you to distinguish between statements, CASE enables you to distinguish between values.
SELECT
c.c_fname AS [First Name],
c.c_lname AS [Last Name],
c.c_nick_name AS [Emp Id],
b.b_number_str AS [Card...
^{“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 9, 2010 at 7:10 am

#1218568
RE: Enjoy Your Job

Jack Corbett (9/9/2010)
...At the end of the day I want to see that my work is having an impact and if it isn't then that is when I am looking...
^{“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 9, 2010 at 6:22 am

#1218549
RE: Enjoy Your Job

Whether you're permy or contracting, it seems to me that the three KPIs affecting happiness at work are money, people, and the actual work you're doing.
All three and you're...
^{“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 9, 2010 at 5:59 am

#1218535
RE: Searching a part of String in string Column

chetanr.jain (9/8/2010)
Hi,
I require a little help on string query.
I have two table ( Table A and Table B) each having employee column with string values.
I want to search that does...
^{“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 9, 2010 at 5:44 am

#1218525
RE: Question: float display as character

Jeff Moden (9/9/2010)
As a side bar, the "2" option I used in the conversion is the 16 byte option for "E" notation. If I'd used "1", it would have...
^{“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 9, 2010 at 5:36 am

#1218521
RE: Question: float display as character

declare @returnvalue float
set @ReturnValue = ((6840844991.81 + 113664947.42) - (6947944500.34 + 2417617.57)) / (6947944500.34 + 2417617.57)
SELECT @ReturnValue --this is the correct calculated float number
-- truncate to 16dp
SELECT CONVERT(DECIMAL(26,16),@ReturnValue)
-- Use...
^{“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 9, 2010 at 5:08 am

#1218500
RE: T-SQL code performance - am I missing something obvious

Surely this would be tons easier and quicker if you were to normalise out the categories quite early on?
DROP TABLE #Temp
CREATE TABLE #Temp (ID INT IDENTITY(1,1), Name VARCHAR(30), [Address] VARCHAR(60),...
^{“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 9, 2010 at 2:46 am

#1218441
RE: Please help with a query

WayneS (9/8/2010)
Chris Morris-439714 (9/8/2010)
WayneS (9/8/2010)
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm...
^{“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 8, 2010 at 9:40 am

#1218059
RE: Please help with a query

WayneS (9/8/2010)
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm using a tally...
^{“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 8, 2010 at 9:26 am

#1218038
RE: Today's Random Word!

CirquedeSQLeil (9/8/2010)
Chris Morris-439714 (9/8/2010)
CirquedeSQLeil (9/8/2010)
eep opp ork ah ah
Like a Richmond sausage, you know there's pork in there somewhere.
p ork ahhhhhh
LOL
You need one of those dandy 360^o sofas.
^{“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 8, 2010 at 9:19 am

#1218030
RE: Today's Random Word!

CirquedeSQLeil (9/8/2010)
eep opp ork ah ah
Like a Richmond sausage, you know there's pork in there somewhere.
^{“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 8, 2010 at 9:02 am

#1218013
RE: Please help with a query

-- generate a row for every unit bought / sold.
-- I guess this could be adapted to use the least common denominator
-- Another possibility is to use an iTVF (inline...
^{“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 8, 2010 at 8:33 am

#1217984

Viewing 15 posts - 7,426 through 7,440 (of 10,143 total)