ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,961 through 3,975 (of 10,143 total)

RE: Trigger to insert from one (new row) in table to another table

ngustafson-1056093 (10/1/2013)
The stored procedure is a standard proc used for different circumstances. I prefer to have a trigger execute the insert
Can you post the code for the sproc?
^{“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 1, 2013 at 6:01 am

#1654848
RE: How to improve a aquery

Are the NVARCHAR(MAX) column types necessary?
DECLARE @PropertyRepairsHistory Table
(
Jobnumber INT
, PropertyReference NVARCHAR (max) --
, JobTitle nvarchar (60)
, Priority nvarchar (20)
, JobType nvarchar (20)
, Contractorcode nvarchar (4)
, Contractor nvarchar...
^{“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 1, 2013 at 4:29 am

#1654834
RE: How to improve a aquery

How long do the two remote queries take to run and how many rows are returned?
SELECT *
FROM OPENQUERY (ingres2, '
SELECT repheader.repairno
, repheader.propref
, repheader.repairdesc
, priority.decode
, reptype.code
, workforce.wforcecode
, workforce.wforcename
,...
^{“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 1, 2013 at 3:37 am

#1654815
RE: how to update column city value from 'A' to 'B' and 'B' to 'A' in single query

dastagiri16 (9/25/2013)
hi,
I have a table like
id city
1 A
so i want to update city column from A to B...
^{“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 1, 2013 at 2:31 am

#1654807
RE: Need help with Charindex function..

-- solution
;WITH SampleData AS (
SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL
SELECT 'No email address in this string'
)
SELECT test, x3.p3
FROM...
^{“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 30, 2013 at 6:56 am

#1654530
RE: using case when condition in WHERE in SP

parulprabu (9/30/2013)
Hi,
Try with this code
where id=@id and month=@month and Eid=
case when @Eid=0 then case when Eid>0 then Eid else 0 end else @Eid end =Eid
There's a syntax error and a...
^{“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 30, 2013 at 6:32 am

#1654515
RE: Find Top 5 expensive Queries from a Read IO perspective

Here's your query
SELECT TOP 5
qt.text AS 'SQL',
qstats.total_physical_reads AS 'Total Physical Reads',
qstats.total_physical_reads/qstats.execution_count AS 'Average Physical Reads',
qstats.execution_count AS 'Execution Count',
qstats.total_worker_time/qstats.execution_count AS 'Average...
^{“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 30, 2013 at 3:17 am

#1654467
RE: Select record from group

paulcauchon (9/12/2013)
ChrisM@home (9/12/2013)
Not that complicated;
...
If I'm understanding this correctly, we're lining up all of the ID and Color groups in order, and then ascribing row numbers to them. From...
^{“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 13, 2013 at 4:01 am

#1650004
RE: get previous value

;WITH SampleData (Date, value) AS (
SELECT '01-01-2013', 10 UNION ALL
SELECT '02-01-2013', 20 UNION ALL
SELECT '03-01-2013', 0 UNION ALL
SELECT '04-01-2013', 0 UNION ALL
SELECT '05-01-2013', 30 UNION ALL...
^{“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 13, 2013 at 2:00 am

#1649979
RE: Count all months with "0's" starting today

Check if this works. It should generate a count of events per month, including months with no events. If it works then it's a trivial matter to use it as...
^{“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 13, 2013 at 1:38 am

#1649976
RE: Are the posted questions getting worse?

dwain.c (9/12/2013)
Jack Corbett (9/12/2013)
Brandie Tarvin (9/12/2013)
Ed,
Go to http://www.sqlsaturday.com and see if there is a way to register for event notifications. That way if one pops up near you...
^{“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 13, 2013 at 1:12 am

#1649970
RE: Select several subqueries with more than 1 value

L' Eomot Inversé (9/12/2013)
ChrisM@Work (9/12/2013)
Your initialisation code fails with an error. Your expectations are incorrect.
I think this is what you are looking for:
CREATE TABLE #table1(c nvarchar(1));
CREATE TABLE #table2(c nvarchar(1));
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

September 13, 2013 at 1:07 am

#1649967
RE: problem with subtracting one millisecond

Koen Verbeeck (9/13/2013)
dwain.c (9/12/2013)
Which of these is closer to the end of the week?
SELECT DATEADD(millisecond, -1, CAST('2013-09-13' AS DATETIME2))
,DATEADD(microsecond, -1, CAST('2013-09-13' AS DATETIME2))
Too bad SQL Server...
^{“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 13, 2013 at 1:05 am

#1649965
RE: How do I convert column data into row data?

Erland Sommarskog (9/12/2013)
Blimey? Must be some lingo local to Port Moresby.
Any way, since I don't think that there was a post with CROSS JOIN for unpivotting, here is an...
^{“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 13, 2013 at 1:01 am

#1649960
RE: How to add this condition into where clause? Thanks.

Or this?
SELECT *
FROM document
WHERE (@dt = 1 AND doctype IN (1, 2))
OR (@dt = 0 AND doctype = 1)
^{“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 12, 2013 at 9:28 am

#1649778

Viewing 15 posts - 3,961 through 3,975 (of 10,143 total)