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

--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