Mark Cowne

Forum Replies Created

Viewing 15 posts - 826 through 840 (of 1,439 total)

RE: string search in objects

Try this
SELECT name
FROM sys.views
WHERe OBJECT_DEFINITION(object_id) LIKE '%where empid=%'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

April 6, 2011 at 8:01 am

#1308028
RE: The SSIS Data Pump - Step 2 of the Stairway to Integration Services

Great articles and timely (for me!). One question though, for the OLE DB Destination fast load, are there any recommended values for "Rows per batch" and "Maximum insert commit size"...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

April 6, 2011 at 4:02 am

#1307902
RE: Is any built in function availble in SQL Server 2008 which handle comma seperated values and return as table?

There's nothing built in, but you can use this function
http://www.sqlservercentral.com/Forums/FindPost944589.aspx
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

April 2, 2011 at 1:30 am

#1306762
RE: Are the posted questions getting worse?

Lynn Pettis (3/26/2011)
Okay, poll time. How many of you are getting tired of Celko and his high horse?
Yep, have been for a number of years now.
I may be wrong,...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 28, 2011 at 6:11 am

#1304302
RE: Is there a way to combine/merge multiple continuous rows by date into a single row without using a loop/cursor?

Try this
SELECT s1.Member_ID,
s1.Eff_Date,
MIN(t1.Term_Date) AS Term_Date,
ROW_NUMBER() OVER(ORDER BY s1.Eff_Date)...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 25, 2011 at 2:03 pm

#1303871
RE: Remove duplicate items form table

Dan-Ketil Jakobsen (3/25/2011)
Thanks Mark.
That was just brilliant.
Short end efective.
Dan
Thanks for the feedback!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 25, 2011 at 5:41 am

#1303493
RE: Remove duplicate items form table

DECLARE @T TABLE(item INT,pricecode INT,price INT)
INSERT INTO @T(item,pricecode,price)
SELECT 1018,1,100 UNION ALL
SELECT 1020,2,120 UNION ALL
SELECT 1031,1,90 UNION ALL
SELECT 1045,5,45 UNION ALL
SELECT 1018,2,120 UNION ALL
SELECT 1430,7,35 UNION ALL
SELECT 1020,3,95 UNION ALL
SELECT 1430,5,30;
WITH...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 25, 2011 at 4:12 am

#1303455
RE: Help with and update statement for duplication records (I dont want to deleted duplicates)

Not sure you understood myquery, but this should work
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS rn
FROM mytablename)
UPDATE CTE
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 22, 2011 at 10:06 am

#1301967
RE: Help with and update statement for duplication records (I dont want to deleted duplicates)

DECLARE @T TABLE(ID INT, Name VARCHAR(10), deleted INT)
INSERT INTO @T(ID,Name)
SELECT 1, 'Orange' UNION ALL
SELECT 2, 'Orange' UNION ALL
SELECT 3, 'RED' UNION ALL
SELECT 4, 'RED' UNION ALL
SELECT 5, 'RED';
WITH CTE AS...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 22, 2011 at 6:23 am

#1301828
RE: Trying to Group Sets of Hours

SELECT Dept,EmpNum,
SUM(CASE WHEN IndCode='APP' THEN IndHours ELSE 0 END) AS APP,
SUM(CASE WHEN...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 18, 2011 at 9:58 am

#1300622
RE: Counting records only when the status of a record has changed

Also a minor change will give you the NULL for the latest end month
WITH CTE AS (
SELECT ProcessID, Month, Compliant,
ROW_NUMBER() OVER(PARTITION BY ProcessID...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 16, 2011 at 7:24 am

#1299348
RE: Counting records only when the status of a record has changed

It's quite a well known technique, just google for sql "gaps and islands".
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 15, 2011 at 2:19 pm

#1299107
RE: Counting records only when the status of a record has changed

Something like this?
WITH CTE AS (
SELECT ProcessID, Month, Compliant,
ROW_NUMBER() OVER(PARTITION BY ProcessID ORDER BY Month) -
ROW_NUMBER()...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 15, 2011 at 12:10 pm

#1299042
RE: Counting records only when the status of a record has changed

Try this
DECLARE @t TABLE(ProcessID INT, Month INT, Compliant INT)
INSERT INTO @t(ProcessID, Month, Compliant)
SELECT 1,1,0 UNION ALL
SELECT 1,2,0 UNION ALL
SELECT 1,3,1 UNION ALL
SELECT 1,4,1 UNION ALL
SELECT 1,5,0 UNION ALL
SELECT 1,6,1;
WITH CTE...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 15, 2011 at 9:49 am

#1298943
RE: A single query to sum an aggregate - can this be done?

The SELECT inside the brackets is known as a derived table, the 'x' is simply to give it a name (which is required by SQL Server)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 9, 2011 at 9:32 am

#1296602

Viewing 15 posts - 826 through 840 (of 1,439 total)