Ignoring the valid questions posted by Evil, something like this might get you started:

--Create Table Consumer

CREATE TABLE #Consumer(

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

[ConsumerName] [varchar](100) NULL,

[ConsumerType] [varchar](20) NULL

) ON [PRIMARY]

GO

--Create Table ConsumerActivity

GO

CREATE TABLE #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 #Consumer (ConsumerID, ConsumerName, ConsumerType)

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

GO

-- Insert some data into ConsumerActivity table

GO

INSERT INTO #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'

DECLARE @AsOfDate DATETIME = '2012-10-25'

SELECT ConsumerType, ActiveOfType=COUNT(Status), Points=ISNULL(SUM(Points), 0)

FROM #Consumer a

LEFT OUTER JOIN (

SELECT ConsumerID, Status=MIN(Status), Points=SUM(Pts1 + Pts2)

FROM #ConsumerActivity

WHERE ActivityDate <= @AsOfDate

GROUP BY ConsumerID) b

ON a.ConsumerID = b.ConsumerID AND Status <> 'DEACTIVATE'

GROUP BY ConsumerType

DROP TABLE #Consumer

DROP TABLE #ConsumerActivity