ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,141 through 4,155 (of 10,143 total)

RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

Jeff Moden (8/15/2013)
ChrisM@Work (8/15/2013)
Dwain's quite capable of providing an American English description of how his code works. Here's an English description of mine 😀
BWAAAA-HAAAA!!!! I'm not sure that's so true...
^{“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

August 15, 2013 at 8:26 am

#1641825
RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

As a little aside, I had a quick look at the rCTE method vs other methods of generating rows. Here's the code:
-- Q1
DROP TABLE #Temp1;
WITH RowGenerator AS (
SELECT...
^{“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

August 15, 2013 at 8:24 am

#1641824
RE: Help on triggers

CREATE TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger
ON dbo.My_Table
INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM deleted WHERE tag <> 1)
DELETE FROM MyTable
WHERE <key> IN (SELECT <key> FROM deleted)
END
^{“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

August 15, 2013 at 5:08 am

#1641730
RE: Help on triggers

vignesh.ms (8/15/2013)
yes of course I want to restrict delete query based on some condition.
for example ,
assume a table named sample, and one of the column is tag which holds value...
^{“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

August 15, 2013 at 3:58 am

#1641705
RE: increment id based on column value

SELECT
l.legacyID,
l.field1,
[Newid] = DENSE_RANK() OVER(ORDER BY x.legacyID)
FROM #LoadTest l
OUTER APPLY (
SELECT TOP 1 legacyID
FROM #LoadTest i
WHERE i.field1 = 'H'
AND i.legacyID <= l.legacyID
ORDER BY i.legacyID DESC)...
^{“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

August 15, 2013 at 2:25 am

#1641679
RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

GPO (8/15/2013)
Chris how would you change yours to work on SQL 2005 (no cross apply and table value constructors)?
SQL2k5 introduced APPLY so you're ok with it in your query. Here's...
^{“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

August 15, 2013 at 2:06 am

#1641674
RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

GPO (8/15/2013)
Hi Dwain
I'm enormously grateful for the code you've put up. I'll test yours and Chris's and see what I can learn from them. I'll post back my observations after...
^{“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

August 15, 2013 at 1:23 am

#1641665
RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

There's a little date arithmetic left for you in this one:
SELECT
location_id,
unoccupied_start_dt = CASE WHEN seq = 1 THEN NULL ELSE unoccupied_start_dt END,
unoccupied_end_dt = CASE WHEN seq =...
^{“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

August 14, 2013 at 9:42 am

#1641469
RE: Are the posted questions getting worse?

Koen Verbeeck (8/14/2013)
And this post marks my 10,000th one on SSC! 😎 😎 :w00t: (and some of them were actually useful!)
Now that I'm one of the big boyz and Grant...
^{“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

August 14, 2013 at 6:47 am

#1641362
RE: Is overpunch amenable to cross apply?

dwain.c (8/13/2013)
ChrisM@Work (8/13/2013)
Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into 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

August 14, 2013 at 6:36 am

#1641357
RE: recursive CTE in a view

If the query doesn't work, why try to construct a view out of it? Drop the CREATE VIEW part until you've got it working. It's just noise.
Start again, your query...
^{“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

August 14, 2013 at 5:52 am

#1641334
RE: Update takes long time

Thanks for posting those. It looks to me that the indexing strategy for the two tables is a bit hit or miss, meaning there's plenty of scope for improvement.
With...
^{“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

August 14, 2013 at 1:33 am

#1641233
RE: How to Group by the Past Seven Days

Have a play with this date arithmetic code:
SELECT DATEDIFF(DD,'19010101',GETDATE())
SELECT DATEDIFF(DD,0,GETDATE())
SELECT DATENAME(DW,CAST('19010101' AS DATETIME))
SELECT
MyDate,
DATENAME(dw,MyDate),
DaysSince19000101 = DATEDIFF(DD,0,MyDate),
DateNoTime = DATEADD(DD,DATEDIFF(DD,0,MyDate),0),
mon_sun = DATEADD(DD,0+DATEDIFF(DD,0,MyDate)/7*7,0),
tue_mon = DATEADD(DD,1+DATEDIFF(DD,1,MyDate)/7*7,0),
wed_tue = DATEADD(DD,2+DATEDIFF(DD,2,MyDate)/7*7,0),
thu_wed = DATEADD(DD,3+DATEDIFF(DD,3,MyDate)/7*7,0),
fri_thu = DATEADD(DD,4+DATEDIFF(DD,4,MyDate)/7*7,0),
sat_fri = DATEADD(DD,5+DATEDIFF(DD,5,MyDate)/7*7,0),
sun_sat...
^{“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

August 13, 2013 at 9:25 am

#1640999
RE: Is overpunch amenable to cross apply?

Stefan_G (8/13/2013)
If you want maximum performance you could use something like this:
create function fixop2(@a varchar(20)) returns table
as
return select
case
when right(@a,1) between 'A' and 'I' then +cast(left(@a, len(@a)-1) as int)*10 + (ascii(right(@a,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

August 13, 2013 at 9:02 am

#1640982
RE: Is overpunch amenable to cross apply?

Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a iTVF.
SELECT TOP...
^{“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

August 13, 2013 at 7:41 am

#1640935

Viewing 15 posts - 4,141 through 4,155 (of 10,143 total)