Mark Cowne

Forum Replies Created

Viewing 15 posts - 646 through 660 (of 1,439 total)

RE: TSQL - Find maximum running date gap in a date range and also date gap ranking

Lots of options. For your temporary table solution you can do this
WITH CTE AS (
SELECT date_year,
date_and_time,
...
____________________________________________________
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

February 27, 2012 at 8:55 am

#1452126
RE: TSQL - Find maximum running date gap in a date range and also date gap ranking

Per year as below. As for not using CTEs, you could change the CTE to a derived table, but that's pretty much just a cosmetic difference.
WITH CTE AS (
SELECT...
____________________________________________________
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

February 27, 2012 at 7:56 am

#1452081
RE: TSQL - Find maximum running date gap in a date range and also date gap ranking

You can use this to give you output 2. Output 1 is where Rank is 1
WITH CTE AS (
SELECT date_and_time,
ROW_NUMBER() OVER(ORDER...
____________________________________________________
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

February 27, 2012 at 6:49 am

#1452036
RE: Count delimiters in field containing CSV

Here's another way. For any comma, count the number of double quotes preceeding it. If there is an even number count it, otherwise ignore.
WITH CTE AS (
SELECT t.id,
...
____________________________________________________
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

February 23, 2012 at 6:47 am

#1450809
RE: Extracting XML information

See if this helps
DECLARE @t TABLE(xmlCol NTEXT)
INSERT INTO @t(xmlCol)
VALUES('<NewDataSet>
<XmlLink>
<ObjectId>123456</ObjectId>
<ObjectName>Jo Bloggs</ObjectName> ...
____________________________________________________
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

February 23, 2012 at 4:54 am

#1450762
RE: What's triggering the error?

Have a look here
http://www.sqlservercentral.com/articles/IsNumeric/71512/
____________________________________________________
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

February 22, 2012 at 10:02 am

#1450422
RE: Reusing a "just declared" alias as the next item in a Select statement

Have a look here
http://www.sqlservercentral.com/Forums/Topic1248957-392-1.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

February 22, 2012 at 3:33 am

#1450160
RE: Find all "events" that happen between dates!?

I think it simplifies to this
SELECT
startdate
,enddate
,descript
FROM testdates
WHERE @End >= startdate AND @Start<=enddate
ORDER BY startdate, enddate
____________________________________________________
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

February 22, 2012 at 2:42 am

#1450140
RE: I am sure this is so simple but I am stumped

WITH CTE AS (
SELECT ID, peter, mike, steve,
ROW_NUMBER() OVER(PARTITION BY Peter ORDER BY Mike DESC) AS rn
FROM dbo.ATable)
SELECT peter, mike,...
____________________________________________________
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

February 22, 2012 at 2:20 am

#1450129
RE: Best approach for record comparison

Using my query from here
http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx
WITH StartsAndEnds(StartEnd,StartDate,EndDate,CustomerID,ServiceType) AS (
SELECT 'S' AS StartEnd,
StartDate,
DATEADD(day,-1,StartDate),
...
____________________________________________________
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

February 21, 2012 at 5:37 am

#1449627
RE: Group ID in each other

DECLARE @t TABLE(X CHAR(1), Y CHAR(1))
INSERT INTO @t(X,Y)
SELECT 'A' ,'B' UNION ALL
SELECT 'A' ,'B' UNION ALL
SELECT 'A' ,'C' UNION ALL
SELECT 'A' ,'C' UNION ALL
SELECT 'A' ,'C' UNION ALL
SELECT 'A' ,'D'...
____________________________________________________
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

February 20, 2012 at 9:15 am

#1449301
RE: Select the most recent record closest to primary record date,tricky

dwain.c (2/20/2012)
Jeff - Thanks for pointing that out. I was a bit rushed (had to run off to a meeting) so I didn't carefully check the result set.
However the...
____________________________________________________
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

February 20, 2012 at 3:59 am

#1449166
RE: String from Multiple rows into one row

Have a look here
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
____________________________________________________
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

February 13, 2012 at 7:02 am

#1446215
RE: query to get first 2 records

Gianluca Sartori (2/13/2012)
ekknaveen (2/13/2012)
even if you can suggest me the general query which will work in all databases it would be great
I don't think such a syntax exists. I'm afraid...
____________________________________________________
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

February 13, 2012 at 6:15 am

#1446192
RE: query to get first 2 records

You're better off asking here
http://www.sqlservercentral.com/Forums/Forum131-1.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

February 13, 2012 at 5:15 am

#1446173

Viewing 15 posts - 646 through 660 (of 1,439 total)