I've a ConsumerActivity table that records all the activities. Now, I need to find out the latest status for each Consumers on a given day.
For example, I need to write a query that gives me ConsumerID and latest Status on '2012-04-01 00:00:00'.
In this table, there is no activity on this date and as such I won't get any result. However, I want to get the result as:
Below is my simple scenario and query:
-- Create Table
CREATE TABLE [dbo].[ConsumerActivity](
[ConsumerID] [varchar](10) NOT NULL,
[ActivityDate] [datetime] NULL,
[Status] [varchar](10) NULL
) ON [PRIMARY]
-- Insert Some Data into this table
INSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, Status)
SELECT 101, '2012-01-10 00:00:00', 'INACTIVE'
SELECT 101, '2012-05-20 00:00:00', 'ACTIVE'
SELECT 102, '2012-02-10 00:00:00', 'BASE'
SELECT 102, '2012-03-15 00:00:00', 'PREMIUM'