ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,231 through 4,245 (of 10,143 total)

RE: View index not being used

-- force usage of the view
SELECT Column1, Column2, ... FROM Table1, View1
WITH (NOEXPAND) WHERE ...
-- force usage of the base tables
SELECT Column1, Column2, ... FROM Table1, View1
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

August 2, 2013 at 4:28 am

#1638110
RE: Sorting Months By Number (SQL Spackle)

David, you might want to read the article again. The MONTH() function is covered...
^{“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

August 2, 2013 at 4:17 am

#1638107
RE: How To Use IS NOT NULL in a Case Statement

Teee (8/2/2013)
Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.
Thanks
That's because the subquery in Kapil'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

August 2, 2013 at 1:32 am

#1638073
RE: How To Use IS NOT NULL in a Case Statement

kapil_kk (8/2/2013)
...
Try this now-
Select
jc.intJobCardId
,b.vchBarcode as [Barcode]
,i.dteIncidentDate as [IncidentDate]
,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '') as [ServiceProvider]
,dteAssignedDate as [AssignedDate]
,dteCompletedDate as [CompletedDate]
,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]
,ISNULL(lb.TotalHours, 0) as [TotalHours]
,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [Efficiency]
,'' as [Productivity]
,l.vchLocation
,typ.vchType
From dtlIncident...
^{“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

August 2, 2013 at 1:14 am

#1638067
RE: attempting to recreate complex "multi-layer" Access queries in SSMS

R. Brush (8/1/2013)
ChrisM@Work,
I know how to do it. I didn't say it could not be accomplished some other way.
My original post said that an alias cannot be referred 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

August 1, 2013 at 7:46 am

#1637794
RE: How to convert my cursor tsql to set based

This works but it's quite expensive to run:
WITH Pass1 AS (
SELECT
PoolId = DENSE_RANK() OVER(ORDER BY ResidentId),
HouseId, ResidentId
FROM #HouseResident
),
Pass2 AS (
SELECT
PoolId = MIN(PoolId) OVER (PARTITION BY HouseId),
HouseId,...
^{“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

August 1, 2013 at 7:36 am

#1637789
RE: attempting to recreate complex "multi-layer" Access queries in SSMS

R. Brush (7/31/2013)
Just a clarificaton:
An alias for a calcuated column can be used in GROUP BY or ORDER BY clauses, but not in the SELECT column list as vega805 was...
^{“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

August 1, 2013 at 1:01 am

#1637664
RE: attempting to recreate complex "multi-layer" Access queries in SSMS

Like this:
SELECT
c.companyName,
r.hoops,
r.ranchNumber,
tbNHC.[00002],
tbNHC.[30],
tbNHC.[40],
tbNHC.[60],
p.SumOfqtyReceived,
([30]+[40]+(0.6*[60]))/(p.[SumOfqtyReceived]/x.[TotalAc]) AS crateHC,
x.TotalAC,
tbNHC.Date
FROM tlkpCompany c
INNER JOIN tblCycleRanch r ON c.[companyNum] = r.[companyNum]
CROSS APPLY ( -- perform aggregate...
^{“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

July 31, 2013 at 9:54 am

#1637450
RE: attempting to recreate complex "multi-layer" Access queries in SSMS

R. Brush (7/31/2013)
Access lets you define a calculated column in a query and then use the calculated column name in other calculated columns. SQL Server will not allow this....
^{“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

July 31, 2013 at 9:45 am

#1637443
RE: Why doesn't the substring work?

Stefan_G (7/31/2013)
Since you are on SQL2008, you can forget about TRY_CAST since this is a SQL2012 feature only.
On SQL2008 you could use a CASE expression to guard the expression evaluation...
^{“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

July 31, 2013 at 6:20 am

#1637334
RE: Why doesn't the substring work?

Ok - try these:
SELECT
TTT_Factureren = 0,
TTT_FactuurStatus = 0,
TTT_FactuurNummer = 0
FROM tblInvoicesProject
WHERE TTT_ProjectID = 26
AND TTT_FactuurStatus = 1
AND TTT_ID NOT IN (
SELECT SUBSTRING(CAST(X.TextDescription AS VARCHAR(100)),13,10)
FROM [500].dbo.frstx X
WHERE (LEFT(CAST(X.TextDescription AS VARCHAR(100)),...
^{“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

July 31, 2013 at 5:29 am

#1637317
RE: Why doesn't the substring work?

How many rows are returned?
^{“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

July 31, 2013 at 5:21 am

#1637311
RE: Why doesn't the substring work?

Have you tried both subqueries against your actual data, Marc?
^{“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

July 31, 2013 at 5:13 am

#1637308
RE: How to loop throught prameters to use the same sqlquerys

Are you processing mailing (DM) extracts?
^{“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

July 31, 2013 at 4:59 am

#1637301
RE: How to loop throught prameters to use the same sqlquerys

clas (7/31/2013)
...
Sql code must be easily transferred to MS Access...
Why?
^{“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

July 31, 2013 at 4:21 am

#1637292

Viewing 15 posts - 4,231 through 4,245 (of 10,143 total)