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...

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...

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...

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...

You're welcome, thanks for the generous feedback.

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...

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' =...

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...

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...

Slowly

A very expensive geezer called Fabio was recently...

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...

dbowlin (7/16/2010)

---

I thought this was something that was just science fiction! A parasite that can manipulate the...

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

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 =...