Just a few clarifications, if I understand this right.

In the pts columns, for the final item, shouldn't pts1 be 0 and Pts2 be -1100 to zero the account off?

Can a customer be reactivated, or is DEACTIVATE always the last entry in the table for a particular ConsumerID? The reason for this is if you can get 'past' a deactivate you want only work from the most recent deactivation for totalling, which makes logging table detection a mite more complex.

Also, your script is a bit off, you #'d all the column names and left the main tables as regulars. I don't think that's what you meant to do... This works better:

IF OBJECT_ID('tempdb..#Consumer') IS NOT NULL

DROP TABLE #Consumer

IF OBJECT_ID('tempdb..#ConsumerActivity') IS NOT NULL

DROP TABLE #ConsumerActivity

--Create Table #Consumer

CREATE TABLE [dbo].[#Consumer](

[ConsumerID] [varchar](10) NOT NULL,

[ConsumerName] [varchar](100) NULL,

[ConsumerType] [varchar](20) NULL

) ON [PRIMARY]

GO

--Create Table #ConsumerActivity

GO

CREATE TABLE [dbo].[#ConsumerActivity](

[ConsumerID] [varchar](10) NOT NULL,

[ActivityDate] [datetime] NULL,

[Pts1] [numeric](9,0) NULL,

[Pts2] [numeric] (9,0) NULL,

[Status] [varchar] (10) NULL,

[Description] [varchar](100) NULL

) ON [PRIMARY]

GO

-- Insert some data into #Consumer table

GO

INSERT INTO dbo.#Consumer (ConsumerID, ConsumerName, ConsumerType)

VALUES ('1234', 'Jack Bennet','Elite')

GO

-- Insert some data into #ConsumerActivity table

GO

INSERT INTO dbo.#ConsumerActivity (ConsumerID, ActivityDate, Pts1, Pts2, Status, Description)

SELECT '1234','2012-06-04 00:00:00.000', 600,0, 'NEW','Pts earned on first time purchase'

UNION ALL

SELECT '1234','2012-08-20 00:00:00.000', 0,-200, NULL,'Points used for purchase'

UNION ALL

SELECT '1234','2012-10-20 00:00:00.000', 700,0, NULL,' Pts earned on New purchase'

UNION ALL

SELECT '1234','2012-10-25 00:00:00.000', 1200,-1200, 'DEACTIVATE','Account Deactivated and points adjusted'

GO

SELECT * FROM #Consumer

SELECT * FROM #ConsumerActivity