Re: Count consecutive days attended
- From: "Uri Dimant" <urid@xxxxxxxxxxx>
- Date: Mon, 4 Dec 2006 14:52:42 +0200
Sorry, DDL should be.
"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:OBIddK6FHHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
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'
.
- References:
- Count consecutive days attended
- From: Mike Hoff
- Re: Count consecutive days attended
- From: Uri Dimant
- Count consecutive days attended
- Prev by Date: Re: Count consecutive days attended
- Next by Date: Re: Count consecutive days attended
- Previous by thread: Re: Count consecutive days attended
- Next by thread: Re: Count consecutive days attended
- Index(es):
Relevant Pages
|
Loading
