The APPLICATIONS table relates to the APPL_STATUSES table by the APPLICATIONS_ID. Whenever a status of an application changes a record is written in APPL_STATUSES along with the status and the date. That is considered the active status until another one comes along with a new date.
Just confirming, what you have in statuses is something like this:
INSERT INTO APPL_STATUSES
VALUES ( 1, 'start', '20100101'),
( 1, 'other', '20100115'),
(1, 'ended', '20100119')
Now, you want to count this application as start on each day for 01/01 through 01/14, then 01/15 through 01/18 is other.
So, if you wanted this to be easy, it'd really look like this:
INSERT INTO APPL_STATUSES
VALUES ( 1, 'start', '20100101'),
( 1, 'start', '20100102'),
( 1, 'start', '20100103'),
( 1, 'start', '20100104'),
( 1, 'start', '20100105'),
( 1, 'start', '20100106'),
( 1, 'start', '20100107'),
( 1, 'start', '20100108'),
( 1, 'start', '20100109'),
( 1, 'start', '20100110'),
( 1, 'start', '20100111'),
( 1, 'start', '20100112'),
( 1, 'start', '20100113'),
( 1, 'start', '20100114'),
( 1, 'other', '20100115'),
( 1, 'other', '20100116'),
( 1, 'other', '20100117'),
( 1, 'other', '20100118'),
(1, 'ended', '20100119')
And then you could simply grab the status on any particular day and sum it up.
Now, how long does the 'ended' status go for and how long does it need to be considered? I assume something that 'ended' four years ago would really not be part of the report anymore?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA