ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,636 through 7,650 (of 10,143 total)

RE: Select with 2 different conditions

This is pretty much a stab in the dark - which is all that can be done without more details as per Joe's post.
SELECT
[no],
name,
Jan = SUM(CASE Condition1 THEN...
^{“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

July 20, 2010 at 10:01 am

#1195652
RE: De-duplicating Customer Detail and assigning parent child structure to multiple customer records

Have a play with this, Matt. Hopefully it will give you some ides.
IF OBJECT_ID( 'tempdb..#rawdata') IS NOT NULL DROP TABLE #rawdata
IF OBJECT_ID( 'tempdb..#WorkingSet') IS NOT NULL DROP TABLE #WorkingSet
CREATE TABLE...
^{“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

July 20, 2010 at 8:36 am

#1195570
RE: Cursor within DTS

The UPDATE of the table is a special kind called a Running Totals Update. Jeff Moden has written some excellent and easily consumable articles about them for this site, well...
^{“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

July 20, 2010 at 7:51 am

#1195531
RE: SQL 2005 - SELECT query takes long time to retrieve

balasach82 (7/20/2010)
SQL 2005 - SELECT query takes long time to retrieve records from a table which has about 250000 records. Even a top 10 takes 4 to 5 mins. Any...
^{“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

July 20, 2010 at 7:19 am

#1195494
RE: Help in Aggregating columns

You're welcome, thanks for the generous feedback.
^{“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

July 20, 2010 at 6:28 am

#1195449
RE: De-duplicating Customer Detail and assigning parent child structure to multiple customer records

Hi Matt
There's a lot of work involved in getting it just right and how you do it depends on what it's for - if you're doing a one-off process then...
^{“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

July 20, 2010 at 5:19 am

#1195415
RE: Help in Aggregating columns

Untested:
SELECT
d.Category,
d.Station,
d.Brand,
'Sun' = SUM(CASE d.[Day] WHEN 'Sun' THEN d.[Number Of Occurence] ELSE 0 END),
'Mon' = SUM(CASE d.[Day] WHEN 'Mon' THEN d.[Number Of Occurence] ELSE 0 END),
'Tue' =...
^{“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

July 20, 2010 at 4:57 am

#1195404
RE: De-duplicating Customer Detail and assigning parent child structure to multiple customer records

Hi Matt
You're trying to dedupe a customer list, right? So you end up with a principal (or master) with an associated set of dupes?
Deduping companies is extraordinarily difficult and complicated...
^{“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

July 20, 2010 at 4:12 am

#1195388
RE: Optimizing query

LadyG (7/20/2010)
Yes, the clustered index on TransactionID and Date.
I did try Chris' suggestion with the calendar table, it did make a huge difference. Anyway I can optimise it further looking...
^{“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

July 20, 2010 at 2:24 am

#1195348
RE: Today's Random Word!

CirquedeSQLeil (7/19/2010)
skewered
Slowly
^{“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

July 19, 2010 at 10:01 am

#1194979
RE: Today's Random Word!

Paul White NZ (7/19/2010)
Chris Morris-439714 (7/19/2010)
Ok - you Sue, me Chris.
I've got to tighten up on defence: I was wide open to that!
A very expensive geezer called Fabio was recently...
^{“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

July 19, 2010 at 9:44 am

#1194969
RE: Today's Random Word!

Paul White NZ (7/19/2010)
dbowlin (7/16/2010)
I thought this was something that was just science fiction! A parasite that can manipulate the behaviour of its host, kind of cool and scary...
^{“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

July 19, 2010 at 8:47 am

#1194933
RE: Today's Random Word!

dbowlin (7/16/2010)
Paul White NZ (7/15/2010)
Paragordius tricuspidatus
(today's random worm)
Haven't done that for a while 🙂
I thought this was something that was just science fiction! A parasite that can manipulate the...
^{“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

July 19, 2010 at 8:31 am

#1194920
RE: Optimizing query

Test and check that you get the same results, and compare the times:
-- existing version
WHERE dbo.fn_ConvertToBusinessDate(pl.date) >= dateadd(day,-10,datediff(day,0,getdate()))
-- proposed version
WHERE pl.date >= DATEADD(mi,195,dateadd(day,-10,datediff(day,0,getdate()))) -- 195 MINUTES = 03:15:00.000
^{“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

July 19, 2010 at 5:06 am

#1194823
RE: Optimizing query

LadyG (7/15/2010)
Hi Chris,
How do I create a date table? Here's what the function does below
CREATE FUNCTION [dbo].[fn_ConvertToBusinessDate] (@Date DATETIME) RETURNS DATETIME AS
BEGIN
DECLARE @RollOverTime DATETIME
SELECT @RollOverTime = [RollOverTime]
FROM [dbo].[ac_Estate]
SELECT @Date =...
^{“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

July 19, 2010 at 4:11 am

#1194786

Viewing 15 posts - 7,636 through 7,650 (of 10,143 total)