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
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA