Re: Count within intervals

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/03/04 

Date: Thu, 03 Jun 2004 22:39:00 +0200

On 3 Jun 2004 10:58:30 -0700, Dave wrote:

>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
>47726 22624 OUT 2003-10-05 Monitor
>38100 22624 IN 2003-10-16 Monitor
>47261 22624 OUT 2003-10-25 Monitor
>38193 22624 IN 2003-11-02 Monitor
>39171 22624 RV 2004-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:
>
>ReportDate Monitors Transmitters
>05/01/2004 34 26
>05/02/2004 37 30
>05/03/2004 39 32
>05/04/2004 35 31
>
>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

Hi Dave,

Your sample data dooesn't fit your DML (IN, OUT and RV can't be used in an
int column and the DeviceType column is completely missing), so I could
not test my suggestion. The best way to provide sample data is to use
INSERT statements (saves me the hassle of typing) and please, do check
that they are correct!!

I won't give you a complete solution, but I'll give you a few pointers.

First, you need a calendar table that holds all dates this report could
ever be generated for. Google this newsgroup for "calendar table" to find
suggestions how to make such a table - I'm too lazy at the moment to look
it up for you <g>

You, we can get to work. From the calendar table and the EMStatusHistory
(I removed the silly tbl-prefix - sorry, but I refuse to type that in a
suggested query), we create a list of all dates each asset was IN. This
result set will be used as a derived table in the final query, but we'll
test it seperately first:

 SELECT c.CalendarDate, a.AssetID, a.DeviceType
 FROM Calendar AS c
 INNER JOIN EMStatusHistory AS a
       ON a.AssetStatus = 'IN'
       AND a.StatusDate <= c.CalendarDate
       AND NOT EXISTS
             (SELECT *
              FROM EMStatusHistory AS b
              WHERE b.StatusDate <= c.CalendarDate
              AND b.StatusDate > a.StatusDate)
 WHERE c.CalendarDate BETWEEN @StartDate AND @EndDate

Run this first and check the results. If they match your expectation,
continue. If not, debug.

We now have all the data we need to assemble the final report. All that's
left to do is to do some group by and the standard crosstab query trick:

 SELECT CalendarDate,
            SUM (CASE WHEN DeviceType = 'Monitor' THEN 1 END)
                                                    AS Monitors,
            SUM (CASE WHEN DeviceType = 'Transmitter' THEN 1 END)
                                                    AS Transmitters
 FROM (insert the above query here) AS x
 GROUP BY CalendarDate, DeviceType

(DISCLAIMER - all the above is untested)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: inputting criteria from a form
    ... report is bound to the query. ... report opens, so that the date controls are available for referencing when ... could simply click on a date in each calendar, ...
    (microsoft.public.access.queries)
  • Re: Changing Record Source for Report
    ... I have one report that can be run using either Query C or Query F. I ... > selects either fiscal or calendar. ... I realize I can use VB to rewrite the SQL ...
    (microsoft.public.access.reports)
  • Re: My calculated expression field is not being populated in the t
    ... I can get that but the problem is that I have a prompt to run the report ... which is 'Calendar Year'. ... now I have the report looking at a calendar year query and the query criteria ... > any calculation will be MUCH faster than a disk fetch); ...
    (microsoft.public.access.tablesdbdesign)
  • Changing Record Source for Report
    ... I would like to know how to change the record source for a report in visual ... I have one report that can be run using either Query C or Query F. I ... selects either fiscal or calendar. ...
    (microsoft.public.access.reports)
  • Re: calendar report
    ... Create all the tables prior to opening the report. ... >> Use a temporary table that resembles your union query. ... In the calendar report, I ... >>> forms and subforms recordsource to the recordset, ...
    (microsoft.public.access.reports)