Forum Replies Created
Viewing 15 posts - 961 through 975 (of 1,439 total)
-
RE: Looking for a set-based solution
WITH CTE AS (
SELECT Customer, MAX(Trx) AS Trx
FROM MyTable
WHERE Trx IS NOT NULL
GROUP BY Customer
HAVING COUNT(DISTINCT Trx)=1)
UPDATE m
SET Trx=c.Trx
FROM MyTable m
INNER JOIN CTE c ON c.Customer=m.Customer
WHERE m.Trx...
____________________________________________________
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
-
RE: Mutually Exclusive Query
lbrigham (1/5/2010)
Mark-101232 (1/5/2010)
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY1)
Still have table scans on TABLE1 and TABLE2. Processing time unchanged.
Apologies, should have...
____________________________________________________
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 -
RE: Mutually Exclusive Query
SELECT KEY1,KEY2,OTHERCOL1,OTHERCOL2,OTHERCOL3 FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.KEY1=TABLE1.KEY1 AND TABLE2.KEY2=TABLE1.KEY1)
____________________________________________________
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 -
RE: if else clause in sql select
Any of these
Select col1,col2, COALESCE(col3,'def value') as col3 from tbl1
Select col1,col2, ISNULL(col3,'def value') as col3 from tbl1
Select col1,col2, CASE WHEN col3 IS NULL THEN 'def value' ELSE col3 END 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 -
RE: Stuck on "continuous date spans"
Mark-101232 (1/4/2010)
Try this, not particularly efficient though.WITH Start_Dates AS(SELECT s1.Group_ID,s1.Start_Date
FROM Source_Data s1
WHERE NOT EXISTS(SELECT * FROM Source_Data s2
...
____________________________________________________
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 -
RE: Stuck on "continuous date spans"
Try this, not particularly efficient though.
WITH Start_Dates AS(SELECT s1.Group_ID,s1.Start_Date
FROM Source_Data s1
WHERE NOT EXISTS(SELECT * FROM Source_Data s2
...
____________________________________________________
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 -
RE: Help with a datediff calc
Use ISNULL or COALESCE
SELECT case_sk, COALESCE(pick_date,'112009') AS pick_date, ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row
____________________________________________________
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 -
RE: Help with a datediff calc
Try changing your subquery from
SELECT pickdate
FROM #DaysinStatus
WHERE #DaysinStatus.rownum = #DaysinStatus.rownum + 1
to
SELECT a.pickdate
FROM #DaysinStatus a
WHERE #DaysinStatus.rownum = a.rownum + 1
____________________________________________________
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 -
RE: Better Query
I think from your description, you're after candidates with all skills from one or more skills groups. So in your example
the candidate would have to have both "Java and Perl"...
____________________________________________________
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 -
RE: What's in your CLR?
Just some string manipulation functions, such as Levenshtein and InitCap. All of which could be coded in TSQL but are easy to do in C# (InitCap is a one-liner).
____________________________________________________
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 -
RE: Xquery Question
SELECT r.value('@ProductID', 'int') AS PID,r.value('@ProductName', 'varchar(40)') AS PName
FROM T
CROSS APPLY CatalogDescription.nodes('/ProductDescription') AS x(r)
____________________________________________________
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 -
RE: Aggregation with every N number of records grouped
Maybe this?
SELECT MIN(Col1) AS StartingNumber,
AVG(Col2*1.0) AS AvgValue
FROM dbo.Table_1
GROUP BY (Col1-1) / 5
ORDER BY MIN(Col1)
____________________________________________________
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 -
RE: Ages and Age Ranges
Change
Age Range
to
[Age Range]
____________________________________________________
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 -
RE: Date peak category
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate='20100901'
SET @EndDate='20100921';
WITH CTE1 AS (
SELECT CASE WHEN FromDate>=@StartDate THEN FromDate ELSE @StartDate END AS StartDate,
CASE WHEN ToDate<=@EndDate THEN...
____________________________________________________
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 -
RE: Flat File Source Unicode
Have a look here
http://unicode.org/faq/utf_bom.html
The Byte Order Mark at the start of the file indicates the unicode encoding. You'll need to examine the first few bytes of the file.
____________________________________________________
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
Viewing 15 posts - 961 through 975 (of 1,439 total)