This is a SQL Server 2005 forum, right?

To test, use this

-- Prepare sample data

DECLARE@Sample TABLE (Person_ID TINYINT, Person_Number CHAR(3), Date DATETIME, Item CHAR(2), Qty TINYINT, Visit_ID TINYINT, Visit_Number CHAR(3))

SET DATEFORMAT...

rowterminator = '\r'

http://msdn2.microsoft.com/en-us/library/ms191485.aspx

declare @sample table (Party_id int, count int, seq int, flag bit)

insert@sample (Party_id, count, seq)

select213777, 1, 1 union all

select213777, 2, 1 union all

select213777, 2, 2 union all

select213778, 1, 1 union...

The path to the file is from the server point of view!

If you write "c:" it is ON THE SERVER, not your local machine "c:".

use unc paths...

I think you can do this, if you redesign the Template tableCREATE TABLETemplate

(

Descr VARCHAR(20),

E3_Field INT,

E1_Exp_Level1 VARCHAR(4)

)

INSERTTemplate

SELECT'Admin Fees', 132, '0132' UNION All

SELECT'Broker fees', 135, '0135' UNION ALL

SELECT'Legal Fees', 139,...

DECLARE@Sample TABLE (PkCol INT, Col1 INT, Col2 INT, Col3 INT)

INSERT@Sample

SELECT1, 1, 2, 3 UNION ALL

SELECT2, 3, 5, 7

SELECTPkCol,

Col1,

Col2,

Col3,

SUM(Col1 + Col2 + Col3) AS SumOfAllCol

FROM@Sample

GROUP BYPkCol,

Col1,

Col2,

Col3

WITHROLLUP

HAVINGGROUPING(Col1) = 0

AND GROUPING(Col2) = 0

AND...

Sergiy, I think you are reasonable skilled at many things for Microsoft SQL Server.

But in this specific topic I think I have proved that this is not one of your...

I now come to the conclusion I must create less sample data, so I started with 4000 records

INSERTTestData

(

StaffName,

ReviewDate

)

SELECTsn.Number,

36524 + ABS(CHECKSUM(NEWID())) % 7304

FROMmaster..spt_values AS sn

INNER JOINmaster..spt_values AS rd ON rd.Type =...

I tried to rerun Sergiys suggestion, and this time my server died after 2 hours and 7 minutes.

There were no longer space to expand my tempdb!

sp_spaceused testdata

namerowsreserveddataindex_sizeunused

TestData512000 ...

And just for the fun of it (to prove Sergiy wrong about my original suggestion with the hidden triangular join which he didn't like and wasn't afraid to tell), I...

I did some test with SET STATISTICS IO ON and got this result for original 15 records

Sergiy

Table 'TestData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,...

I took the liberty to optimize Sergiys suggestion a little.

Two things.

SELECT @N = MAX (CNT)

FROM (select COUNT(*) CNT

FROM TestData

GROUP BY StaffName , year(reviewdate)

) DT

and

INNER JOIN Tally T ON...

I have to break my promise.

After over 30 minutes for first run of Sergiys suggestion I had to cancel the query.

My profiler then showed my this result for Sergiy

Duration -...

Here is how I both created sample data and timetested...

SET NOCOUNT ON

-- Create sample data

CREATE TABLETestData

(

StaffName VARCHAR(40),

ReviewDate SMALLDATETIME

)

GO

-- Populate the table with 512000 records

INSERTTestData

(

StaffName,

ReviewDate

)

SELECTsn.Number,

36524 + ABS(CHECKSUM(NEWID())) % 7304

FROMmaster..spt_values AS sn

INNER...

Sergiy (1/31/2008)

---

Peter, my solution has 3 internal iterations: one per year.

It does not iterate neither over staff members, nor over reviews.

It builds 3 temp tables for each year and joins...