Count consecutive days attended
- From: "Mike Hoff" <mike@xxxxxxxx>
- Date: Mon, 04 Dec 2006 11:13:29 GMT
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 which event they
attend, only if they attended one that day. 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'
.
- Follow-Ups:
- Re: Count consecutive days attended
- From: Joel Maslak
- Re: Count consecutive days attended
- From: Uri Dimant
- Re: Count consecutive days attended
- Next by Date: Re: Count consecutive days attended
- Next by thread: Re: Count consecutive days attended
- Index(es):
Relevant Pages
|
