ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,796 through 6,810 (of 10,143 total)

RE: is this something wrong with this query

Try these two variants of your WHERE clause:
WHERE ([State] <> N'Closed'
AND Title LIKE N'%Bahamas%')
OR History LIKE N'%TheGreatCharlie%'
OR [Repro Steps] LIKE N'%Anything%'
WHERE [State] <> N'Closed' ...
^{“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 4, 2011 at 5:08 am

#1363538
RE: is this something wrong with this query

Vishal Singh (8/4/2011)
Hi Pals,
Not sure wwhatswrong with this query but whenever I am running this, I am getting all those rows with State='closed' which it should ignoring.
SELECT [ID],Priority,Title,[Created By],[Assigned 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 4, 2011 at 4:55 am

#1363533
RE: LEFT JOIN WITH COUNT Question

SELECT A.MSFTID,
SUM(COUNT(*)) OVER(PARTITION BY A.MSFTID) AS 'COUNT',
b.ULTIMATE_SUPER,
b.servicepackname,
A.name
FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B
ON A.MSFTID = B.bulletinnumber
WHERE a.MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, b.ULTIMATE_SUPER, b.servicepackname, A.name
ORDER BY...
^{“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 4, 2011 at 4:37 am

#1363527
RE: MERGE statement to include new column that is an identity column. Don't know how to populate.

hxkresl (8/3/2011)
...How do i finish the merge statement so that it contains auto incremented number values in the 'schemamapping' column?..
Since 'schemamapping' is set to autoincrement, it shouldn't appear in 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

August 4, 2011 at 2:10 am

#1363480
RE: SQL code issue

pwalter83 (8/3/2011)
... display data where the ARRIVAL_SCHEDULE_DT is three months preceding current date when the ship first arrived at PORT_CD = 'BEZEE' (I work for a shipping company). I know...
^{“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 3, 2011 at 10:27 am

#1363213
RE: How will you write a query for this scenario

GSquared (8/3/2011)
ChrisM@Work (8/3/2011)
Recursive CTE's don't allow aggregate functions but they do allow ROW_NUMBER() which provides you with a means of obtaining MIN() and MAX():
http://www.sqlservercentral.com/Forums/FindPost1137945.aspx
It's probably going to be slower than...
^{“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 3, 2011 at 7:40 am

#1363041
RE: How will you write a query for this scenario

Recursive CTE's don't allow aggregate functions but they do allow ROW_NUMBER() which provides you with a means of obtaining MIN() and MAX():
http://www.sqlservercentral.com/Forums/FindPost1137945.aspx
^{“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 3, 2011 at 2:30 am

#1362859
RE: Compare dates

-- make a sample table with some dates in it
DROP TABLE #Dates
CREATE TABLE #Dates (RandomDate DATETIME)
INSERT INTO #Dates (RandomDate)
SELECT Dateval = GETDATE() + n.RN
FROM (SELECT TOP 100 RN =...
^{“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, 2011 at 10:22 am

#1362321
RE: Problem Query

In full agreement with everybody who's posted so far - this is a lot of work, and you have a lot to learn. Here's a start - you need 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 2, 2011 at 9:51 am

#1362285
RE: Logic

Nice work John, cheers 🙂
^{“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, 2011 at 4:04 am

#1362020
RE: Logic

-- make some sample data
DROP TABLE #Test
CREATE TABLE #Test (MyString VARCHAR(30))
INSERT INTO #Test (MyString) SELECT 'IncomeTax'
INSERT INTO #Test (MyString) SELECT 'InheritanceTax'
INSERT INTO #Test (MyString) SELECT 'PurchaseTax'
INSERT INTO...
^{“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, 2011 at 3:33 am

#1362006
RE: Conversion failed when converting from a character string to uniqueidentifier.

Can you post the whole query? It should work:
drop table #test
create table #test (id int identity(1,1), guidcol uniqueidentifier)
insert into #test (guidcol) select NEWID()
insert into #test (guidcol) select NEWID()
insert into #test...
^{“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, 2011 at 6:51 am

#1361511
RE: Comparinng integer like string values.

Joy Smith San (7/29/2011)
...
Problem is we can't create function or any other objects in client's DB. Have to manage with select query itself...
No problem.
SELECT d.MyKey, d.AppVersion, x1.*
FROM (
SELECT MyKey =...
^{“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 29, 2011 at 7:05 am

#1360746
RE: Comparinng integer like string values.

This shows some promise:
SELECT d.MyKey, d.AppVersion, x1.OrderSeq
FROM (
SELECT MyKey = 1, AppVersion = '10.1.55.3366'
UNION
SELECT 1, '10.1.99.64'
UNION
SELECT 1, '10.1.1.1'
UNION
SELECT 1, '10.1.3.9'
UNION
SELECT 3, '9999.9999.9999.9999'
UNION
SELECT 2, '7.0.4'
UNION
SELECT 2, '7.1'
UNION
SELECT 2, '8'
UNION
SELECT...
^{“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 29, 2011 at 6:13 am

#1360707
RE: Been wracking my brains with this

SELECT TimesheetKey, SiteKey, [Date],
SUMDuration = SUM(Duration) OVER(PARTITION BY SiteKey, [Date], GroupRule), GroupRule
FROM #TIMESHEET
^{“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 29, 2011 at 2:16 am

#1360582

Viewing 15 posts - 6,796 through 6,810 (of 10,143 total)