Re: Count consecutive days attended

Mike if you post DLL ,please make sure that it does work. Can you provide
an expected result?

SELECT * FROM tblAttend A JOIN tblClient C
ON A.AttendClientID=C.ClientID
WHERE AttendStart>='20060101' AND AttendStop<='20061231'
Bob attended 42
consecutive,



"Mike Hoff" <mike@xxxxxxxx> wrote in message
news:tpTch.2314$Ga1.1230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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'







.

Relevant Pages

  • Re: Consecutive days attending
    ... INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob' ... INSERT INTO tblEvent (EventCode, EventName) VALUES 'MEET', 'MEETING' ... INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart, ...
    (microsoft.public.sqlserver)
  • Re: Consecutive days attending
    ... INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob' ... INSERT INTO tblEvent (EventCode, EventName) VALUES 'MEET', 'MEETING' ... INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart, ...
    (microsoft.public.sqlserver)
  • Re: Count consecutive days attended
    ... SELECT * FROM tblAttend A JOIN tblClient C ... INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob' ... INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart, ...
    (microsoft.public.sqlserver.programming)
  • Consecutive days attending
    ... INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob' ... INSERT INTO tblEvent (EventCode, EventName) VALUES 'MEET', 'MEETING' ... INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart, ...
    (microsoft.public.sqlserver)
  • Count consecutive days attended
    ... INSERT INTO tblClientHeader (ClientFirstName) VALUES 'Bob' ... INSERT INTO tblEvent (EventCode, EventName) VALUES 'MEET', 'MEETING' ... INSERT INTO tblAttend (AttendClientID, AttendEventCode, AttendStart, ...
    (microsoft.public.sqlserver.programming)
Loading