Count Rows In A Date Interval

I have a problem that I can't quite get started on solving.

I have a table of asset statuses. Each time the asset status changes, a
new row is inserted into the table.

CREATE TABLE dbo.Tbl_EMStatusHistory (
EMStatusID int IDENTITY (1, 1) NOT NULL ,
EMSessionID int NULL ,
AssetID int NOT NULL ,
AssetStatus int NOT NULL ,
StatusComment varchar (250) NULL ,
StatusDate smalldatetime NOT NULL ,
InsertUser sysname NOT NULL ,
InsertDate smalldatetime NULL ,
CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED
(
EMStatusID
)
)

Here is some sample data:

EMStatusID AssetID AssetStatus StatusDate DeviceType
4772622624OUT2003-10-05Monitor
3810022624IN2003-10-16Monitor
4726122624OUT2003-10-25 Monitor
3819322624IN2003-11-02 Monitor
3917122624RV2004-05-02 Monitor

For asset 22624, the current (most recent) status = RV. Before that, it
last
changed status on 2003-11-02, when it was IN. So it was IN from
2003-11-02 to
2004-05-02.
I need to produce a report that counts the devices by type that were IN
on a given report date,
like so:

ReportDateMonitorsTransmitters
05/01/20043426
05/02/20043730
05/03/20043932
05/04/20043531

The end user supplies the date range.

I am unsure how to approach this. I came up with one
Einsteinian-Newtonian method that involved three temp tables, a garden
hose, and a duck, and it was getting uglier by the minute. Has anyone
had experience with a requirement like this? Thanks, Dave


"Are you thinking what I'm thinking, Pinky?"

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!