Mark Cowne

Forum Replies Created

Viewing 15 posts - 526 through 540 (of 1,439 total)

RE: calculate trend of a column

RBarryYoung (8/23/2012)
Mark has it. That's what a Difference Query is... 🙂
Have a look a Jeff Modens excellent article here[/url] for an explanation
____________________________________________________
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

August 23, 2012 at 2:05 pm

#1529449
RE: calculate trend of a column

;with cte (temp_id, temp_test_id, temp_status)
as
(
select 1, 2, 'failed' union all
select 2, 2, 'failed' union all
select 3, 2, 'passed' union all
select 4, 17, 'failed' union all
select 5, 17, 'passed' union...
____________________________________________________
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

August 23, 2012 at 10:16 am

#1529361
RE: INSERT into Target table from Source table, redirecting duplicate key rows into ERROR table

dcwilson2009 (8/23/2012)
Thank you for your response. While you were coming up with that, I came up with this as an entire solution finally. If you can, please take a look...
____________________________________________________
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

August 23, 2012 at 9:50 am

#1529346
RE: INSERT into Target table from Source table, redirecting duplicate key rows into ERROR table

Try this
with cte as (
select DayKey, holi_id, holi_desc, holi_dt,
count(*) over(partition by DayKey, holi_id) as cn
from Source)
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in...
____________________________________________________
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

August 23, 2012 at 9:13 am

#1529324
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

ChrisM@Work (8/23/2012)
Mark-101232 (8/23/2012)
ChrisM@Work (8/23/2012)
mburbea (8/22/2012)
While this article is a bit old I did want to comment on a trick that I've discovered to build an itvf for a xml splitter....
____________________________________________________
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

August 23, 2012 at 5:20 am

#1529178
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

ChrisM@Work (8/23/2012)
mburbea (8/22/2012)
While this article is a bit old I did want to comment on a trick that I've discovered to build an itvf for a xml splitter. This will...
____________________________________________________
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

August 23, 2012 at 4:48 am

#1529165
RE: Urgent request please

Assumes ID is contiguous
DECLARE @t TABLE(ID INT,Status INT)
INSERT INTO @t(ID,Status)
SELECT 1,0 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,0 UNION ALL
SELECT 4,1 UNION ALL
SELECT 5,1 UNION ALL
SELECT 6,1 UNION ALL
SELECT 7,1 UNION...
____________________________________________________
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

August 21, 2012 at 2:32 pm

#1528370
RE: Consolidate rows based on criteria

with cte1 as (
select acc_no,number,c_name,
sum(R_Value) over(partition by acc_no) as R_Value,
sum(time_spent) over(partition by acc_no) as time_spent,
...
____________________________________________________
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

August 20, 2012 at 7:57 am

#1527606
RE: Intresting Query

Hand coding the SQLCLR doesn't help much either, nowhere near Dwains 'REVERSE'
================================================================================
DWAIN
================================================================================
SQL Server Execution Times:
CPU time = 998 ms, elapsed time = 998 ms.
================================================================================
MARK'S SQLCLR
================================================================================
...
____________________________________________________
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

August 10, 2012 at 5:16 am

#1524340
RE: Intresting Query

Eugene Elutin (8/10/2012)
try to return
(SqlString) s.Value.TrimStart('0').TrimEnd('0');
instead of
new SqlString(s.Value.TrimStart('0').TrimEnd('0'));
Marginal improvement
From this
CPU time = 2262 ms, elapsed time = 2277 ms. ...
____________________________________________________
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

August 10, 2012 at 4:50 am

#1524323
RE: Intresting Query

dwain.c (8/10/2012)
Gosh! If you're gonna use the VARCHAR(20) version of @Holder, you should at least use this version of DWAIN's:
REVERSE(1*REVERSE([Column] * 1))
Oops, my bad. Original post updated.
____________________________________________________
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

August 10, 2012 at 4:44 am

#1524316
RE: Intresting Query

SQLCLR doesn't perform that well
SET NOCOUNT ON;
IF object_id('tempdb..#sampleData') IS NOT NULL
BEGIN
DROP TABLE #sampleData;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],
'0000' +...
____________________________________________________
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

August 10, 2012 at 4:29 am

#1524308
RE: Listing Continus Numbers

Using a numbers/tally table
http://www.sqlservercentral.com/articles/T-SQL/62867/
SELECT t.N
FROM dbo.Tally t
WHERE EXISTS(SELECT * FROM @TT1 t1 WHERE t.N BETWEEN t1.StartNo AND t1.EndNo)
EXCEPT
SELECT DDNo
FROM @TT2
____________________________________________________
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

August 9, 2012 at 8:22 am

#1523816
RE: WITH XMLNAMESPACES y subconsultas

Known issue. I don't believe there's a simple workaround.
http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements
____________________________________________________
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

August 8, 2012 at 5:36 am

#1523058
RE: pull out all values within an XML column

Try changing
OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'),
to
OriginalSchema.value('Form[1]/Codes[1]/code[sql:column("y.myNum")][1]','varchar(max)')
____________________________________________________
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

August 7, 2012 at 8:41 am

#1522643

Viewing 15 posts - 526 through 540 (of 1,439 total)