UPDATEing a table from SUM operation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATEing a table from SUM operation

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-11-21 : 08:20:10

Hi everyone

I'm attempting to develop the backbone procedure of a system that is to display summarised crime data, for a certain geographical area our a certain time period.

The way I'm developing the procedure, a temp table is created which holds all the various crime categories. Then the SUM of the total crimes, for the area, time period and crime type are calculated and UPDATEd in the temp table.

So the main loop of the table looks like:

-- Loop through each row 
DECLARE
@RowCurrent	INT,
@RowCount	INT,
@CrimeTypeID	TINYINT,
@CrimeFreq	INT

SELECT @RowCurrent = 1
SELECT @RowCount = COUNT(*) FROM @TT_CrimeData

WHILE @RowCurrent <= @RowCount BEGIN

	-- Retrieve crime type ID
	SELECT @CrimeTypeID = CrimeCategoryID FROM @TT_CrimeData WHERE UID = @RowCurrent
	PRINT 'Current @CrimeTypeID: ' + CAST( @CrimeTypeID AS VARCHAR(3) )

	-- Retrieve crime level for area, up until max month limit
	SELECT @CrimeFreq = SUM( RC.Frequency )
	FROM
	RecordedCrime AS RC
	INNER JOIN 
	@TT_AreaBeatIDs AS TT_AB ON
	TT_AB.BeatID = RC.BeatID
	WHERE
	RC.CrimeCategoryID = @CrimeTypeID
	AND
	(
		@NewestMonthWithData  >= 4 AND 
		( -- Get just this years data
			RC.CrimeMonth <= @NewestMonthWithData AND -- Max month, or before
			RC.CrimeMonth >= 4 AND -- not before April 
			RC.CrimeYear = @FinancialYear -- for this year
		)
	)
	OR
	(
		@NewestMonthWithData <= 3 AND 
		( -- Get this years data and next years
			(
			RC.CrimeMonth >= 4 AND -- After April
			RC.CrimeYear = @FinancialYear -- This year
			)
			AND
			(
			RC.CrimeMonth <= @NewestMonthWithData AND -- Before April
			RC.CrimeYear = @FinancialYear + 1 -- Next year
			)
		) 
	)

	PRINT 'Current @CrimeFreq: ' + CAST( @CrimeFreq AS VARCHAR(4) )
		
	-- Save data to temp table
	UPDATE
	@TT_CrimeData
	SET
	CrimeFreqThisYear = @CrimeFreq
	WHERE
	CrimeCategoryID = @CrimeTypeID

	-- Next row
	SELECT @RowCurrent = @RowCurrent + 1
END

As you can imagine, this creates a fair bit of work for SQL Server, looping round 12 times.

My attempts at doing this without using loops have failed. Any advice?