Need help in defining a stored procedure

14 Jul 2005 3:37 PM

romy

Hi

I need to define a SP which returns the frequency of occurrences of Events
in a certain range of dates.

Parameters: range of dates (DateFrom ,DateTo)

Input:
An events table which its relevant fields are: EventCode, EventDate

Output:
For each eventcode return the average Frequency in which the event occur.

Example:

EventCode,EventDate
1                01/01/2005
1                01/03/2005
1                01/04/2005
2                01/01/2005
2                01/02/2005

The Frequency of occurrences of EventCode 1 is 45, Because the first event
occurred in 01/01/2005 the second event occurred 2 months after (or 60
days).
the third event for EventCode 1 occurred 30 days after the second. (60+
30)/2 equals 45.
Likewise the Frequency of occurrences of EventCode 2 is 30.

Your help is very much appreciated.

Romy

14 Jul 2005 2:50 PM

Itzik Ben-Gan

Romy, try:

select eventcode, avg(diff) as freq
from (select eventcode,
        datediff(day, eventdate,
          (select min(eventdate)
           from events as e2
           where e2.eventcode = e1.eventcode
             and e2.eventdate > e1.eventdate)) as diff
      from events as e1) as d
group by eventcode

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Show quote

"romy" <romy1***@hotpop.com> wrote in message
news:e0mcoGIiFHA.708@TK2MSFTNGP09.phx.gbl...
> Hi
>
> I need to define a SP which returns the frequency of occurrences of Events
> in a certain range of dates.
>
> Parameters: range of dates (DateFrom ,DateTo)
>
> Input:
> An events table which its relevant fields are: EventCode, EventDate
>
> Output:
> For each eventcode return the average Frequency in which the event occur.
>
> Example:
>
> EventCode,EventDate
> 1                01/01/2005
> 1                01/03/2005
> 1                01/04/2005
> 2                01/01/2005
> 2                01/02/2005
>
> The Frequency of occurrences of EventCode 1 is 45, Because the first event
> occurred in 01/01/2005 the second event occurred 2 months after (or 60
> days).
> the third event for EventCode 1 occurred 30 days after the second. (60+
> 30)/2 equals 45.
> Likewise the Frequency of occurrences of EventCode 2 is 30.
>
> Your help is very much appreciated.
>
> Romy
>
>

14 Jul 2005 3:06 PM

Aaron Bertrand [SQL Server MVP]

> 1                01/01/2005
> 1                01/03/2005
> 1                01/04/2005
> 2                01/01/2005
> 2                01/02/2005
>
> The Frequency of occurrences of EventCode 1 is 45, Because the first event
> occurred in 01/01/2005 the second event occurred 2 months after (or 60
> days).

Can you please use unambiguous date formats? YYYYMMDD or YYYY-MM-DD is much
better than what you're using. We're not all in England/Canada, and we
weren't all initially aware that 01/03/2005 was March 1st as opposed to
January 3rd!

Also, proper DDL is usually helpful (see http://www.aspfaq.com/5006 ... )

CREATE TABLE dbo.floob
(
EventCode INT,
dt SMALLDATETIME
)
SET NOCOUNT ON
GO

INSERT dbo.floob SELECT 1, '20050101'
INSERT dbo.floob SELECT 1, '20050401'
INSERT dbo.floob SELECT 1, '20050301'
INSERT dbo.floob SELECT 2, '20050101'
INSERT dbo.floob SELECT 2, '20050201'
GO

SELECT
EventCode,
Frequency = AVG(lapse)
FROM
(SELECT
EventCode,
lapse = DATEDIFF
(
   DAY,
   dt,
   (SELECT MIN(dt)
    FROM dbo.floob fB
    WHERE fA.dt < fB.dt
    AND fA.EventCode = fB.EventCode)
)
FROM dbo.floob fA
) x
GROUP BY
EventCode

GO

DROP TABLE dbo.floob
GO