Mike Hoff wrote:
> Hello,
>
> I am looking for a way to determine the maximum number of consecutive days
> that each client has attended an event over a certain date range.
> (consecutive being key, and my problem).
> For example: Over the date range 1/1/06 - 12/31/06, Bob attended 42
> consecutive, Jim 17 consecutive, Mary 103 consecutive. Each client can
> attend an event any day they choose. It doesnt matter what the event was,
> only that they atteneded something. Events occur 7-days a week (do not need
> only business days or avoiding holidays). I am also planning to filter the
> view so that I can get things like only people who attended 100+ consecutive
> etc. Any help on getting this number of consecutive days is greatly
> appreciated.
>
> Here are tables...
>
> CREATE TABLE [dbo].[tblClient] (
>
> [ClientID] [int] IDENTITY (1, 1) NOT NULL ,
>
> [ClientFirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
>
> CREATE TABLE [dbo].[tblEvent] (
>
> [EventCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>
> [EventName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
>
> CREATE TABLE [dbo].[tblAttend] (
>
> [AttendID] [int] IDENTITY (1, 1) NOT NULL ,
>
> [AttendClientID] [int] NOT NULL ,
>
> [AttendEventCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
>
> [AttendStart] [datetime] NOT NULL ,
>
> [AttendStop] [datetime] NOT NULL
>
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
>
> INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob'
>
> INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Jim'
>
> INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Mary'
>
>
>
> INSERT INTO tblEvent (EventCode, EventName) VALUES 'MEET', 'MEETING'
>
> INSERT INTO tblEvent (EventCode, EventName) VALUES 'SPEECH', 'SPEECH
> PROGRAM'
>
> INSERT INTO tblEvent (EventCode, EventName) VALUES 'CLASS', 'CLASS SESSION'
>
> INSERT INTO tblEvent (EventCode, EventName) VALUES 'DANCE', 'DANCE CLASS'
>
>
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 1, 'MEET', '20060101 10:00 AM', '20060101 11:00 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 1, 'MEET', '20060102 7:00 AM', '20060102 8:00 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 1, 'CLASS', '20060103 11:00 AM', '20060103 11:30 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 1, 'DANCE', '20060104 9:00 AM', '20060104 10:00 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 2, 'MEET', '20060102 5:00 AM', '20060102 6:00 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 2, 'MEET', '20060103 11:00 AM', '20060103 11:30 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 2, 'MEET', '20060107 7:00 AM', '20060107 8:00 AM'
>
> INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart,
> AttendStop) VALUES 3, 'CLASS', '20060102 9:00 AM', '20060102 10:00 AM'