"Sam" <S**@discussions.microsoft.com> wrote in message
news:B5C1BA05-194D-42FB-8744-A0FAD3639DCD@microsoft.com...
> Hi,
>
> I've included the necessary scripts to generate tables and insert sample
> data at the bottom of this post.
>
> Here are basic descriptions of 4 tables I included in my scripts:
>
> tblCompany contains all necessary information about our clients i.e.
> company
> name, phone #, etc.
>
> tblDeals contains contract information i.e. contract date, etc. This table
> has a one-to-many relationship to tblCompany table which allows us to have
> multiple contracts w/ the same company.
>
> tblActivityType is used to define activity types i.e. phone call, voice
> mail, etc.
>
> tblActivityTransactions is the activity log. All activities get logged
> into
> this table. This table uses ActivityTypeID to describe the activity and
> DealID to link it to the proper contract.
>
> My business goal is to monitor our customer service employees by
> determining
> the "Last Contact Date" for our active projects. It is critical for me to
> mention that for an activity to "qualify" as our last contact we MUST
> initiate it. So if a client sends us an email, that should not be
> considered
> by the query as a contact.
>
> A contract should be returned by the query EVEN IF our customer service
> dept. has not contacted the client. In this case, the Last Contact Date
> column should have a NULL value.
>
> To demonstrate this in my sample, I purposely did not include an activity
> for DealID # 4.
>
> Also, keep in mind, I need the last contact date. So if we made a phone
> call
> on Monday and left a voice mail on Tuesday, the last contact date should
> be
> Tuesday's date. I'm interested in the "LAST" contact date.
>
> What I would like my query to return is this:
>
> Company Name / DealID / Activity Type / Last Contact Date
> ---------------------------------------------------------
> ABC Corporation - 1 - Voice Mail - 6/21/2005
> XYZ Corporation - 2 - Phone Call - 5/15/2005
> 123 Corporation - 3 - NULL - NULL
> ABC Corporation - 4 - NULL - NULL
>
> Here's the explanantion why I should get this:
> First of, we have two contracts w/ ABC Corporation i.e. DealID 1 and
> DealID 4
> Although we communicated w/ ABC Corporation for DealID 1 more than once, I
> need the last contact date which is the date for voice mail on 6/21/2005.
> Although 123 Corporation sent us an email on 6/15/2005, the query should
> return NULLs as client's emails do NOT qualify in this query. We must
> initiate the contact.
> Although DealID 4 w/ ABC Corporation does NOT have any activities in
> tblActivityTransactions, we still need the query to include it and return
> NULLs.
>
> Again, the idea is to see how our customer service dept. is doing at a
> glance.
>
> I've been struggling w/ this stored procedure for quite some time. The one
> I
> have that sort of produces what I need is as follows. However, this SP
> does
> not show me clients we haven't contacted so it's not really working.
>
> I'd really could use some help here. Thanks in advance.
>
> Here's the query I have so far -- which needs help:
> ***********************************************
> SELECT tblCompany.CompanyName, tblDeals.DealID,
> tblActivityTransactions.ActivityDateTime,
> tblActivityType.ActivityDescription
> FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
> tblDeals.CompanyID
>     INNER JOIN tblActivityTransactions ON tblDeals.DealID =
> tblActivityTransactions.DealID
>     INNER JOIN tblActivityType ON tblActivityType.ActivityTypeID =
> tblActivityTransactions.ActivityTypeID
> ***********************************************
>
> Here's the script to create the necessary tables
> ***********************************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblActivityTransactions_tblActivityType]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> FK_tblActivityTransactions_tblActivityType
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblActivityTransactions_tblDeals]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> FK_tblActivityTransactions_tblDeals
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblActivityTransactions]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblActivityTransactions]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblActivityType]') and OBJECTPROPERTY(id,
> N'IsUserTable')
> = 1)
> drop table [dbo].[tblActivityType]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblCompany]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblDeals]
> GO
>
> CREATE TABLE [dbo].[tblActivityTransactions] (
> [ActivityID] [int] IDENTITY (1, 1) NOT NULL ,
> [ActivityTypeID] [tinyint] NOT NULL ,
> [ActivityDateTime] [smalldatetime] NOT NULL ,
> [DealID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblActivityType] (
> [ActivityTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
> [ActivityDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblCompany] (
> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblDeals] (
> [DealID] [int] IDENTITY (1, 1) NOT NULL ,
> [ContractDate] [smalldatetime] NOT NULL ,
> [CompanyID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityTransactions] ADD
> CONSTRAINT [PK_tblActivityTransactions] PRIMARY KEY  CLUSTERED
> (
> [ActivityID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityType] ADD
> CONSTRAINT [PK_tblActivityType] PRIMARY KEY  CLUSTERED
> (
> [ActivityTypeID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblCompany] ADD
> CONSTRAINT [PK_tblCompanyName] PRIMARY KEY  CLUSTERED
> (
> [CompanyID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblDeals] ADD
> CONSTRAINT [PK_tblDeals] PRIMARY KEY  CLUSTERED
> (
> [DealID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityTransactions] ADD
> CONSTRAINT [FK_tblActivityTransactions_tblActivityType] FOREIGN KEY
> (
> [ActivityTypeID]
> ) REFERENCES [dbo].[tblActivityType] (
> [ActivityTypeID]
> ),
> CONSTRAINT [FK_tblActivityTransactions_tblDeals] FOREIGN KEY
> (
> [DealID]
> ) REFERENCES [dbo].[tblDeals] (
> [DealID]
> )
> GO
>
> ALTER TABLE [dbo].[tblDeals] ADD
> CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
> (
> [CompanyID]
> ) REFERENCES [dbo].[tblCompany] (
> [CompanyID]
> )
> GO
>
> ***********************************************
>
> Here's the sample data INSERT statements
> ***********************************************
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(1,'ABC
> Corporation')
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(2,'XYZ
> Corporation')
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(3,'123
> Corporation')
>
>
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(1,'Apr
> 1
> 2005 12:00:00:000AM',1)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(2,'May
> 1
> 2005 12:00:00:000AM',2)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(3,'Jun
> 1
> 2005 12:00:00:000AM',3)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(4,'Jul
> 1
> 2005 12:00:00:000AM',1)
>
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(1,'Phone Call')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(2,'Voice Mail')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(3,'We sent email')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(4,'Client sent us email')
>
>
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,1,'Apr
> 15
> 2005 12:00:00:000AM',1)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,1,'May
> 15
> 2005 12:00:00:000AM',2)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(3,4,'Jun
> 15
> 2005 12:00:00:000AM',3)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,2,'Jun
> 21
> 2005 12:00:00:000AM',1)
>
> ***********************************************
>
>
> --
> Thanks,
>
> Sam

"Uri Dimant" wrote:

> Sam
> Sorry, I coundn't run your DDL to test it , it throws alot of errors
>
>
> "Sam" <S**@discussions.microsoft.com> wrote in message
> news:B5C1BA05-194D-42FB-8744-A0FAD3639DCD@microsoft.com...
> > Hi,
> >
> > I've included the necessary scripts to generate tables and insert sample
> > data at the bottom of this post.
> >
> > Here are basic descriptions of 4 tables I included in my scripts:
> >
> > tblCompany contains all necessary information about our clients i.e.
> > company
> > name, phone #, etc.
> >
> > tblDeals contains contract information i.e. contract date, etc. This table
> > has a one-to-many relationship to tblCompany table which allows us to have
> > multiple contracts w/ the same company.
> >
> > tblActivityType is used to define activity types i.e. phone call, voice
> > mail, etc.
> >
> > tblActivityTransactions is the activity log. All activities get logged
> > into
> > this table. This table uses ActivityTypeID to describe the activity and
> > DealID to link it to the proper contract.
> >
> > My business goal is to monitor our customer service employees by
> > determining
> > the "Last Contact Date" for our active projects. It is critical for me to
> > mention that for an activity to "qualify" as our last contact we MUST
> > initiate it. So if a client sends us an email, that should not be
> > considered
> > by the query as a contact.
> >
> > A contract should be returned by the query EVEN IF our customer service
> > dept. has not contacted the client. In this case, the Last Contact Date
> > column should have a NULL value.
> >
> > To demonstrate this in my sample, I purposely did not include an activity
> > for DealID # 4.
> >
> > Also, keep in mind, I need the last contact date. So if we made a phone
> > call
> > on Monday and left a voice mail on Tuesday, the last contact date should
> > be
> > Tuesday's date. I'm interested in the "LAST" contact date.
> >
> > What I would like my query to return is this:
> >
> > Company Name / DealID / Activity Type / Last Contact Date
> > ---------------------------------------------------------
> > ABC Corporation - 1 - Voice Mail - 6/21/2005
> > XYZ Corporation - 2 - Phone Call - 5/15/2005
> > 123 Corporation - 3 - NULL - NULL
> > ABC Corporation - 4 - NULL - NULL
> >
> > Here's the explanantion why I should get this:
> > First of, we have two contracts w/ ABC Corporation i.e. DealID 1 and
> > DealID 4
> > Although we communicated w/ ABC Corporation for DealID 1 more than once, I
> > need the last contact date which is the date for voice mail on 6/21/2005.
> > Although 123 Corporation sent us an email on 6/15/2005, the query should
> > return NULLs as client's emails do NOT qualify in this query. We must
> > initiate the contact.
> > Although DealID 4 w/ ABC Corporation does NOT have any activities in
> > tblActivityTransactions, we still need the query to include it and return
> > NULLs.
> >
> > Again, the idea is to see how our customer service dept. is doing at a
> > glance.
> >
> > I've been struggling w/ this stored procedure for quite some time. The one
> > I
> > have that sort of produces what I need is as follows. However, this SP
> > does
> > not show me clients we haven't contacted so it's not really working.
> >
> > I'd really could use some help here. Thanks in advance.
> >
> > Here's the query I have so far -- which needs help:
> > ***********************************************
> > SELECT tblCompany.CompanyName, tblDeals.DealID,
> > tblActivityTransactions.ActivityDateTime,
> > tblActivityType.ActivityDescription
> > FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
> > tblDeals.CompanyID
> >     INNER JOIN tblActivityTransactions ON tblDeals.DealID =
> > tblActivityTransactions.DealID
> >     INNER JOIN tblActivityType ON tblActivityType.ActivityTypeID =
> > tblActivityTransactions.ActivityTypeID
> > ***********************************************
> >
> > Here's the script to create the necessary tables
> > ***********************************************
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_tblActivityTransactions_tblActivityType]') and
> > OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> > FK_tblActivityTransactions_tblActivityType
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_tblActivityTransactions_tblDeals]') and
> > OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> > FK_tblActivityTransactions_tblDeals
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[tblActivityTransactions]') and OBJECTPROPERTY(id,
> > N'IsUserTable') = 1)
> > drop table [dbo].[tblActivityTransactions]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[tblActivityType]') and OBJECTPROPERTY(id,
> > N'IsUserTable')
> > = 1)
> > drop table [dbo].[tblActivityType]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') =
> > 1)
> > drop table [dbo].[tblCompany]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[tblDeals]
> > GO
> >
> > CREATE TABLE [dbo].[tblActivityTransactions] (
> > [ActivityID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ActivityTypeID] [tinyint] NOT NULL ,
> > [ActivityDateTime] [smalldatetime] NOT NULL ,
> > [DealID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[tblActivityType] (
> > [ActivityTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
> > [ActivityDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[tblCompany] (
> > [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> > [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[tblDeals] (
> > [DealID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ContractDate] [smalldatetime] NOT NULL ,
> > [CompanyID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[tblActivityTransactions] ADD
> > CONSTRAINT [PK_tblActivityTransactions] PRIMARY KEY  CLUSTERED
> > (
> > [ActivityID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[tblActivityType] ADD
> > CONSTRAINT [PK_tblActivityType] PRIMARY KEY  CLUSTERED
> > (
> > [ActivityTypeID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[tblCompany] ADD
> > CONSTRAINT [PK_tblCompanyName] PRIMARY KEY  CLUSTERED
> > (
> > [CompanyID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[tblDeals] ADD
> > CONSTRAINT [PK_tblDeals] PRIMARY KEY  CLUSTERED
> > (
> > [DealID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[tblActivityTransactions] ADD
> > CONSTRAINT [FK_tblActivityTransactions_tblActivityType] FOREIGN KEY
> > (
> > [ActivityTypeID]
> > ) REFERENCES [dbo].[tblActivityType] (
> > [ActivityTypeID]
> > ),
> > CONSTRAINT [FK_tblActivityTransactions_tblDeals] FOREIGN KEY
> > (
> > [DealID]
> > ) REFERENCES [dbo].[tblDeals] (
> > [DealID]
> > )
> > GO
> >
> > ALTER TABLE [dbo].[tblDeals] ADD
> > CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
> > (
> > [CompanyID]
> > ) REFERENCES [dbo].[tblCompany] (
> > [CompanyID]
> > )
> > GO
> >
> > ***********************************************
> >
> > Here's the sample data INSERT statements
> > ***********************************************
> > INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(1,'ABC
> > Corporation')
> > INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(2,'XYZ
> > Corporation')
> > INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(3,'123
> > Corporation')
> >
> >
> > INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(1,'Apr
> > 1
> > 2005 12:00:00:000AM',1)
> > INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(2,'May
> > 1
> > 2005 12:00:00:000AM',2)
> > INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(3,'Jun
> > 1
> > 2005 12:00:00:000AM',3)
> > INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(4,'Jul
> > 1
> > 2005 12:00:00:000AM',1)
> >
> > INSERT INTO [tblActivityType]
> > ([ActivityTypeID],[ActivityDescription])VALUES(1,'Phone Call')
> > INSERT INTO [tblActivityType]
> > ([ActivityTypeID],[ActivityDescription])VALUES(2,'Voice Mail')
> > INSERT INTO [tblActivityType]
> > ([ActivityTypeID],[ActivityDescription])VALUES(3,'We sent email')
> > INSERT INTO [tblActivityType]
> > ([ActivityTypeID],[ActivityDescription])VALUES(4,'Client sent us email')
> >
> >
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,1,'Apr
> > 15
> > 2005 12:00:00:000AM',1)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,1,'May
> > 15
> > 2005 12:00:00:000AM',2)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(3,4,'Jun
> > 15
> > 2005 12:00:00:000AM',3)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,2,'Jun
> > 21
> > 2005 12:00:00:000AM',1)
> >
> > ***********************************************
> >
> >
> > --
> > Thanks,
> >
> > Sam
>
>
>

"Sam" wrote:

> Hi,
>
> I've included the necessary scripts to generate tables and insert sample
> data at the bottom of this post.
>
> Here are basic descriptions of 4 tables I included in my scripts:
>
> tblCompany contains all necessary information about our clients i.e. company
> name, phone #, etc.
>
> tblDeals contains contract information i.e. contract date, etc. This table
> has a one-to-many relationship to tblCompany table which allows us to have
> multiple contracts w/ the same company.
>
> tblActivityType is used to define activity types i.e. phone call, voice
> mail, etc.
>
> tblActivityTransactions is the activity log. All activities get logged into
> this table. This table uses ActivityTypeID to describe the activity and
> DealID to link it to the proper contract.
>
> My business goal is to monitor our customer service employees by determining
> the "Last Contact Date" for our active projects. It is critical for me to
> mention that for an activity to "qualify" as our last contact we MUST
> initiate it. So if a client sends us an email, that should not be considered
> by the query as a contact.
>
> A contract should be returned by the query EVEN IF our customer service
> dept. has not contacted the client. In this case, the Last Contact Date
> column should have a NULL value.
>
> To demonstrate this in my sample, I purposely did not include an activity
> for DealID # 4.
>
> Also, keep in mind, I need the last contact date. So if we made a phone call
> on Monday and left a voice mail on Tuesday, the last contact date should be
> Tuesday's date. I'm interested in the "LAST" contact date.
>
> What I would like my query to return is this:
>
> Company Name / DealID / Activity Type / Last Contact Date
> ---------------------------------------------------------
> ABC Corporation - 1 - Voice Mail - 6/21/2005
> XYZ Corporation - 2 - Phone Call - 5/15/2005
> 123 Corporation - 3 - NULL - NULL
> ABC Corporation - 4 - NULL - NULL
> 
> Here's the explanantion why I should get this:
> First of, we have two contracts w/ ABC Corporation i.e. DealID 1 and DealID 4
> Although we communicated w/ ABC Corporation for DealID 1 more than once, I
> need the last contact date which is the date for voice mail on 6/21/2005.
> Although 123 Corporation sent us an email on 6/15/2005, the query should
> return NULLs as client's emails do NOT qualify in this query. We must
> initiate the contact.
> Although DealID 4 w/ ABC Corporation does NOT have any activities in
> tblActivityTransactions, we still need the query to include it and return
> NULLs.
>
> Again, the idea is to see how our customer service dept. is doing at a glance.
>
> I've been struggling w/ this stored procedure for quite some time. The one I
> have that sort of produces what I need is as follows. However, this SP does
> not show me clients we haven't contacted so it's not really working.
>
> I'd really could use some help here. Thanks in advance.
>
> Here's the query I have so far -- which needs help:
> ***********************************************
> SELECT tblCompany.CompanyName, tblDeals.DealID,
> tblActivityTransactions.ActivityDateTime, tblActivityType.ActivityDescription
> FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
> tblDeals.CompanyID
>      INNER JOIN tblActivityTransactions ON tblDeals.DealID =
> tblActivityTransactions.DealID
>      INNER JOIN tblActivityType ON tblActivityType.ActivityTypeID =
> tblActivityTransactions.ActivityTypeID
> ***********************************************
>
> Here's the script to create the necessary tables
> ***********************************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblActivityTransactions_tblActivityType]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> FK_tblActivityTransactions_tblActivityType
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblActivityTransactions_tblDeals]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> FK_tblActivityTransactions_tblDeals
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblActivityTransactions]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblActivityTransactions]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblActivityType]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tblActivityType]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblCompany]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblDeals]
> GO
>
> CREATE TABLE [dbo].[tblActivityTransactions] (
>     [ActivityID] [int] IDENTITY (1, 1) NOT NULL ,
>     [ActivityTypeID] [tinyint] NOT NULL ,
>     [ActivityDateTime] [smalldatetime] NOT NULL ,
>     [DealID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblActivityType] (
>     [ActivityTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
>     [ActivityDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblCompany] (
>     [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
>     [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblDeals] (
>     [DealID] [int] IDENTITY (1, 1) NOT NULL ,
>     [ContractDate] [smalldatetime] NOT NULL ,
>     [CompanyID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityTransactions] ADD
>     CONSTRAINT [PK_tblActivityTransactions] PRIMARY KEY  CLUSTERED
>     (
>         [ActivityID]
>     )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityType] ADD
>     CONSTRAINT [PK_tblActivityType] PRIMARY KEY  CLUSTERED
>     (
>         [ActivityTypeID]
>     )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblCompany] ADD
>     CONSTRAINT [PK_tblCompanyName] PRIMARY KEY  CLUSTERED
>     (
>         [CompanyID]
>     )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblDeals] ADD
>     CONSTRAINT [PK_tblDeals] PRIMARY KEY  CLUSTERED
>     (
>         [DealID]
>     )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblActivityTransactions] ADD
>     CONSTRAINT [FK_tblActivityTransactions_tblActivityType] FOREIGN KEY
>     (
>         [ActivityTypeID]
>     ) REFERENCES [dbo].[tblActivityType] (
>         [ActivityTypeID]
>     ),
>     CONSTRAINT [FK_tblActivityTransactions_tblDeals] FOREIGN KEY
>     (
>         [DealID]
>     ) REFERENCES [dbo].[tblDeals] (
>         [DealID]
>     )
> GO
>
> ALTER TABLE [dbo].[tblDeals] ADD
>     CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
>     (
>         [CompanyID]
>     ) REFERENCES [dbo].[tblCompany] (
>         [CompanyID]
>     )
> GO
>
> ***********************************************
>
> Here's the sample data INSERT statements
> ***********************************************
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(1,'ABC
> Corporation')
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(2,'XYZ
> Corporation')
> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(3,'123
> Corporation')
>
>
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(1,'Apr  1
> 2005 12:00:00:000AM',1)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(2,'May  1
> 2005 12:00:00:000AM',2)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(3,'Jun  1
> 2005 12:00:00:000AM',3)
> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(4,'Jul  1
> 2005 12:00:00:000AM',1)
>
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(1,'Phone Call')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(2,'Voice Mail')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(3,'We sent email')
> INSERT INTO [tblActivityType]
> ([ActivityTypeID],[ActivityDescription])VALUES(4,'Client sent us email')
>
>
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,1,'Apr 15
> 2005 12:00:00:000AM',1)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,1,'May 15
> 2005 12:00:00:000AM',2)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(3,4,'Jun 15
> 2005 12:00:00:000AM',3)
> INSERT INTO [tblActivityTransactions]
> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,2,'Jun 21
> 2005 12:00:00:000AM',1)
>
> ***********************************************
>
>
> --
> Thanks,
>
> Sam

"Sam" <S**@discussions.microsoft.com> wrote in message
news:26381C7C-1D51-4486-8E76-5E60A6AE88E5@microsoft.com...
> Sorry. Made a little mistake w/ INSERT statements.
>
> Please use the following INSERT statement for sample data
>
> *****************************************
> INSERT INTO [tblCompany] ([CompanyName])VALUES('ABC Corporation')
> INSERT INTO [tblCompany] ([CompanyName])VALUES('XYZ Corporation')
> INSERT INTO [tblCompany] ([CompanyName])VALUES('123 Corporation')
> INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Apr  1 2005
> 12:00:00:000AM',1)
> INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('May  1 2005
> 12:00:00:000AM',2)
> INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Jun  1 2005
> 12:00:00:000AM',3)
> INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Jul  1 2005
> 12:00:00:000AM',1)
> INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Phone Call')
> INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Voice Mail')
> INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('We sent
> email')
> INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Client sent
> us
> email')
> INSERT INTO [tblActivityTransactions]
> ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,'Apr 15 2005
> 12:00:00:000AM',1)
> INSERT INTO [tblActivityTransactions]
> ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,'May 15 2005
> 12:00:00:000AM',2)
> INSERT INTO [tblActivityTransactions]
> ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,'Jun 15 2005
> 12:00:00:000AM',3)
> INSERT INTO [tblActivityTransactions]
> ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,'Jun 21 2005
> 12:00:00:000AM',1)
> *************************************************
> --
> Thanks,
>
> Sam
>
>
> "Sam" wrote:
>
>> Hi,
>>
>> I've included the necessary scripts to generate tables and insert sample
>> data at the bottom of this post.
>>
>> Here are basic descriptions of 4 tables I included in my scripts:
>>
>> tblCompany contains all necessary information about our clients i.e.
>> company
>> name, phone #, etc.
>>
>> tblDeals contains contract information i.e. contract date, etc. This
>> table
>> has a one-to-many relationship to tblCompany table which allows us to
>> have
>> multiple contracts w/ the same company.
>>
>> tblActivityType is used to define activity types i.e. phone call, voice
>> mail, etc.
>>
>> tblActivityTransactions is the activity log. All activities get logged
>> into
>> this table. This table uses ActivityTypeID to describe the activity and
>> DealID to link it to the proper contract.
>>
>> My business goal is to monitor our customer service employees by
>> determining
>> the "Last Contact Date" for our active projects. It is critical for me to
>> mention that for an activity to "qualify" as our last contact we MUST
>> initiate it. So if a client sends us an email, that should not be
>> considered
>> by the query as a contact.
>>
>> A contract should be returned by the query EVEN IF our customer service
>> dept. has not contacted the client. In this case, the Last Contact Date
>> column should have a NULL value.
>>
>> To demonstrate this in my sample, I purposely did not include an activity
>> for DealID # 4.
>>
>> Also, keep in mind, I need the last contact date. So if we made a phone
>> call
>> on Monday and left a voice mail on Tuesday, the last contact date should
>> be
>> Tuesday's date. I'm interested in the "LAST" contact date.
>>
>> What I would like my query to return is this:
>>
>> Company Name / DealID / Activity Type / Last Contact Date
>> ---------------------------------------------------------
>> ABC Corporation - 1 - Voice Mail - 6/21/2005
>> XYZ Corporation - 2 - Phone Call - 5/15/2005
>> 123 Corporation - 3 - NULL - NULL
>> ABC Corporation - 4 - NULL - NULL
>>
>> Here's the explanantion why I should get this:
>> First of, we have two contracts w/ ABC Corporation i.e. DealID 1 and
>> DealID 4
>> Although we communicated w/ ABC Corporation for DealID 1 more than once,
>> I
>> need the last contact date which is the date for voice mail on 6/21/2005.
>> Although 123 Corporation sent us an email on 6/15/2005, the query should
>> return NULLs as client's emails do NOT qualify in this query. We must
>> initiate the contact.
>> Although DealID 4 w/ ABC Corporation does NOT have any activities in
>> tblActivityTransactions, we still need the query to include it and return
>> NULLs.
>>
>> Again, the idea is to see how our customer service dept. is doing at a
>> glance.
>>
>> I've been struggling w/ this stored procedure for quite some time. The
>> one I
>> have that sort of produces what I need is as follows. However, this SP
>> does
>> not show me clients we haven't contacted so it's not really working.
>>
>> I'd really could use some help here. Thanks in advance.
>>
>> Here's the query I have so far -- which needs help:
>> ***********************************************
>> SELECT tblCompany.CompanyName, tblDeals.DealID,
>> tblActivityTransactions.ActivityDateTime,
>> tblActivityType.ActivityDescription
>> FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
>> tblDeals.CompanyID
>>      INNER JOIN tblActivityTransactions ON tblDeals.DealID =
>> tblActivityTransactions.DealID
>>      INNER JOIN tblActivityType ON tblActivityType.ActivityTypeID =
>> tblActivityTransactions.ActivityTypeID
>> ***********************************************
>>
>> Here's the script to create the necessary tables
>> ***********************************************
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[FK_tblActivityTransactions_tblActivityType]') and
>> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
>> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
>> FK_tblActivityTransactions_tblActivityType
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
>> N'IsForeignKey') = 1)
>> ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[FK_tblActivityTransactions_tblDeals]') and
>> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
>> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
>> FK_tblActivityTransactions_tblDeals
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[tblActivityTransactions]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[tblActivityTransactions]
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[tblActivityType]') and OBJECTPROPERTY(id,
>> N'IsUserTable')
>> = 1)
>> drop table [dbo].[tblActivityType]
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') =
>> 1)
>> drop table [dbo].[tblCompany]
>> GO
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') =
>> 1)
>> drop table [dbo].[tblDeals]
>> GO
>>
>> CREATE TABLE [dbo].[tblActivityTransactions] (
>> [ActivityID] [int] IDENTITY (1, 1) NOT NULL ,
>> [ActivityTypeID] [tinyint] NOT NULL ,
>> [ActivityDateTime] [smalldatetime] NOT NULL ,
>> [DealID] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[tblActivityType] (
>> [ActivityTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
>> [ActivityDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>> NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[tblCompany] (
>> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
>> [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>> NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[tblDeals] (
>> [DealID] [int] IDENTITY (1, 1) NOT NULL ,
>> [ContractDate] [smalldatetime] NOT NULL ,
>> [CompanyID] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblActivityTransactions] ADD
>> CONSTRAINT [PK_tblActivityTransactions] PRIMARY KEY  CLUSTERED
>> (
>> [ActivityID]
>> )  ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblActivityType] ADD
>> CONSTRAINT [PK_tblActivityType] PRIMARY KEY  CLUSTERED
>> (
>> [ActivityTypeID]
>> )  ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblCompany] ADD
>> CONSTRAINT [PK_tblCompanyName] PRIMARY KEY  CLUSTERED
>> (
>> [CompanyID]
>> )  ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblDeals] ADD
>> CONSTRAINT [PK_tblDeals] PRIMARY KEY  CLUSTERED
>> (
>> [DealID]
>> )  ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblActivityTransactions] ADD
>> CONSTRAINT [FK_tblActivityTransactions_tblActivityType] FOREIGN KEY
>> (
>> [ActivityTypeID]
>> ) REFERENCES [dbo].[tblActivityType] (
>> [ActivityTypeID]
>> ),
>> CONSTRAINT [FK_tblActivityTransactions_tblDeals] FOREIGN KEY
>> (
>> [DealID]
>> ) REFERENCES [dbo].[tblDeals] (
>> [DealID]
>> )
>> GO
>>
>> ALTER TABLE [dbo].[tblDeals] ADD
>> CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
>> (
>> [CompanyID]
>> ) REFERENCES [dbo].[tblCompany] (
>> [CompanyID]
>> )
>> GO
>>
>> ***********************************************
>>
>> Here's the sample data INSERT statements
>> ***********************************************
>> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(1,'ABC
>> Corporation')
>> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(2,'XYZ
>> Corporation')
>> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(3,'123
>> Corporation')
>>
>>
>> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(1,'Apr
>> 1
>> 2005 12:00:00:000AM',1)
>> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(2,'May
>> 1
>> 2005 12:00:00:000AM',2)
>> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(3,'Jun
>> 1
>> 2005 12:00:00:000AM',3)
>> INSERT INTO [tblDeals] ([DealID],[ContractDate],[CompanyID])VALUES(4,'Jul
>> 1
>> 2005 12:00:00:000AM',1)
>>
>> INSERT INTO [tblActivityType]
>> ([ActivityTypeID],[ActivityDescription])VALUES(1,'Phone Call')
>> INSERT INTO [tblActivityType]
>> ([ActivityTypeID],[ActivityDescription])VALUES(2,'Voice Mail')
>> INSERT INTO [tblActivityType]
>> ([ActivityTypeID],[ActivityDescription])VALUES(3,'We sent email')
>> INSERT INTO [tblActivityType]
>> ([ActivityTypeID],[ActivityDescription])VALUES(4,'Client sent us email')
>>
>>
>> INSERT INTO [tblActivityTransactions]
>> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,1,'Apr
>> 15
>> 2005 12:00:00:000AM',1)
>> INSERT INTO [tblActivityTransactions]
>> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,1,'May
>> 15
>> 2005 12:00:00:000AM',2)
>> INSERT INTO [tblActivityTransactions]
>> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(3,4,'Jun
>> 15
>> 2005 12:00:00:000AM',3)
>> INSERT INTO [tblActivityTransactions]
>> ([ActivityID],[ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,2,'Jun
>> 21
>> 2005 12:00:00:000AM',1)
>>
>> ***********************************************
>>
>>
>> --
>> Thanks,
>>
>> Sam

"Uri Dimant" wrote:

> Sam
> SELECT * FROM
> (
> SELECT tblCompany.CompanyName,
> tblDeals.DealID,tblActivityTransactions.ActivityTypeID,
> tblActivityTransactions.ActivityDateTime,
> tblActivityType.ActivityDescription
> FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
> tblDeals.CompanyID
>      LEFT JOIN tblActivityTransactions ON tblDeals.DealID =
> tblActivityTransactions.DealID
>      LEFT JOIN tblActivityType ON tblActivityType.ActivityTypeID =
> tblActivityTransactions.ActivityTypeID
> ) AS Der
> WHERE ActivityDateTime =(SELECT MAX(ActivityDateTime) FROM
> tblActivityTransactions TA
> WHERE TA.DealID=Der.DealID) OR ActivityTypeID IS NULL
>
> But I don't  understand this  one
> ---Although 123 Corporation sent us an email on 6/15/2005, the query should
> ---return NULLs as client's emails do NOT qualify in this query. We must
> ---initiate the contact.
>
> How do we know that whether you initiated the contact or not?
>
>
>
>
>
> "Sam" <S**@discussions.microsoft.com> wrote in message
> news:26381C7C-1D51-4486-8E76-5E60A6AE88E5@microsoft.com...
> > Sorry. Made a little mistake w/ INSERT statements.
> >
> > Please use the following INSERT statement for sample data
> >
> > *****************************************
> > INSERT INTO [tblCompany] ([CompanyName])VALUES('ABC Corporation')
> > INSERT INTO [tblCompany] ([CompanyName])VALUES('XYZ Corporation')
> > INSERT INTO [tblCompany] ([CompanyName])VALUES('123 Corporation')
> > INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Apr  1 2005
> > 12:00:00:000AM',1)
> > INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('May  1 2005
> > 12:00:00:000AM',2)
> > INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Jun  1 2005
> > 12:00:00:000AM',3)
> > INSERT INTO [tblDeals] ([ContractDate],[CompanyID])VALUES('Jul  1 2005
> > 12:00:00:000AM',1)
> > INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Phone Call')
> > INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Voice Mail')
> > INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('We sent
> > email')
> > INSERT INTO [tblActivityType] ([ActivityDescription])VALUES('Client sent
> > us
> > email')
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,'Apr 15 2005
> > 12:00:00:000AM',1)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(1,'May 15 2005
> > 12:00:00:000AM',2)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(4,'Jun 15 2005
> > 12:00:00:000AM',3)
> > INSERT INTO [tblActivityTransactions]
> > ([ActivityTypeID],[ActivityDateTime],[DealID])VALUES(2,'Jun 21 2005
> > 12:00:00:000AM',1)
> > *************************************************
> > --
> > Thanks,
> >
> > Sam
> >
> >
> > "Sam" wrote:
> >
> >> Hi,
> >>
> >> I've included the necessary scripts to generate tables and insert sample
> >> data at the bottom of this post.
> >>
> >> Here are basic descriptions of 4 tables I included in my scripts:
> >>
> >> tblCompany contains all necessary information about our clients i.e.
> >> company
> >> name, phone #, etc.
> >>
> >> tblDeals contains contract information i.e. contract date, etc. This
> >> table
> >> has a one-to-many relationship to tblCompany table which allows us to
> >> have
> >> multiple contracts w/ the same company.
> >>
> >> tblActivityType is used to define activity types i.e. phone call, voice
> >> mail, etc.
> >>
> >> tblActivityTransactions is the activity log. All activities get logged
> >> into
> >> this table. This table uses ActivityTypeID to describe the activity and
> >> DealID to link it to the proper contract.
> >>
> >> My business goal is to monitor our customer service employees by
> >> determining
> >> the "Last Contact Date" for our active projects. It is critical for me to
> >> mention that for an activity to "qualify" as our last contact we MUST
> >> initiate it. So if a client sends us an email, that should not be
> >> considered
> >> by the query as a contact.
> >>
> >> A contract should be returned by the query EVEN IF our customer service
> >> dept. has not contacted the client. In this case, the Last Contact Date
> >> column should have a NULL value.
> >>
> >> To demonstrate this in my sample, I purposely did not include an activity
> >> for DealID # 4.
> >>
> >> Also, keep in mind, I need the last contact date. So if we made a phone
> >> call
> >> on Monday and left a voice mail on Tuesday, the last contact date should
> >> be
> >> Tuesday's date. I'm interested in the "LAST" contact date.
> >>
> >> What I would like my query to return is this:
> >>
> >> Company Name / DealID / Activity Type / Last Contact Date
> >> ---------------------------------------------------------
> >> ABC Corporation - 1 - Voice Mail - 6/21/2005
> >> XYZ Corporation - 2 - Phone Call - 5/15/2005
> >> 123 Corporation - 3 - NULL - NULL
> >> ABC Corporation - 4 - NULL - NULL
> >>
> >> Here's the explanantion why I should get this:
> >> First of, we have two contracts w/ ABC Corporation i.e. DealID 1 and
> >> DealID 4
> >> Although we communicated w/ ABC Corporation for DealID 1 more than once,
> >> I
> >> need the last contact date which is the date for voice mail on 6/21/2005.
> >> Although 123 Corporation sent us an email on 6/15/2005, the query should
> >> return NULLs as client's emails do NOT qualify in this query. We must
> >> initiate the contact.
> >> Although DealID 4 w/ ABC Corporation does NOT have any activities in
> >> tblActivityTransactions, we still need the query to include it and return
> >> NULLs.
> >>
> >> Again, the idea is to see how our customer service dept. is doing at a
> >> glance.
> >>
> >> I've been struggling w/ this stored procedure for quite some time. The
> >> one I
> >> have that sort of produces what I need is as follows. However, this SP
> >> does
> >> not show me clients we haven't contacted so it's not really working.
> >>
> >> I'd really could use some help here. Thanks in advance.
> >>
> >> Here's the query I have so far -- which needs help:
> >> ***********************************************
> >> SELECT tblCompany.CompanyName, tblDeals.DealID,
> >> tblActivityTransactions.ActivityDateTime,
> >> tblActivityType.ActivityDescription
> >> FROM tblCompany INNER JOIN tblDeals ON tblCompany.CompanyID =
> >> tblDeals.CompanyID
> >>      INNER JOIN tblActivityTransactions ON tblDeals.DealID =
> >> tblActivityTransactions.DealID
> >>      INNER JOIN tblActivityType ON tblActivityType.ActivityTypeID =
> >> tblActivityTransactions.ActivityTypeID
> >> ***********************************************
> >>
> >> Here's the script to create the necessary tables
> >> ***********************************************
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[FK_tblActivityTransactions_tblActivityType]') and
> >> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> >> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> >> FK_tblActivityTransactions_tblActivityType
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
> >> N'IsForeignKey') = 1)
> >> ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[FK_tblActivityTransactions_tblDeals]') and
> >> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> >> ALTER TABLE [dbo].[tblActivityTransactions] DROP CONSTRAINT
> >> FK_tblActivityTransactions_tblDeals
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[tblActivityTransactions]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[tblActivityTransactions]
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[tblActivityType]') and OBJECTPROPERTY(id,
> >> N'IsUserTable')
> >> = 1)
> >> drop table [dbo].[tblActivityType]
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') =
> >> 1)
> >> drop table [dbo].[tblCompany]
> >> GO
> >>
> >> if exists (select * from dbo.sysobjects where id =
> >> object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') =
> >> 1)
> >> drop table [dbo].[tblDeals]
> >> GO
> >>
> >> CREATE TABLE [dbo].[tblActivityTransactions] (
> >> [ActivityID] [int] IDENTITY (1, 1) NOT NULL ,
> >> [ActivityTypeID] [tinyint] NOT NULL ,
> >> [ActivityDateTime] [smalldatetime] NOT NULL ,
> >> [DealID] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[tblActivityType] (
> >> [ActivityTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
> >> [ActivityDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[tblCompany] (
> >> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> >> [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> >> NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[tblDeals] (
> >> [DealID] [int] IDENTITY (1, 1) NOT NULL ,
> >> [ContractDate] [smalldatetime] NOT NULL ,
> >> [CompanyID] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblActivityTransactions] ADD
> >> CONSTRAINT [PK_tblActivityTransactions] PRIMARY KEY  CLUSTERED
> >> (
> >> [ActivityID]
> >> )  ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblActivityType] ADD
> >> CONSTRAINT [PK_tblActivityType] PRIMARY KEY  CLUSTERED
> >> (
> >> [ActivityTypeID]
> >> )  ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblCompany] ADD
> >> CONSTRAINT [PK_tblCompanyName] PRIMARY KEY  CLUSTERED
> >> (
> >> [CompanyID]
> >> )  ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblDeals] ADD
> >> CONSTRAINT [PK_tblDeals] PRIMARY KEY  CLUSTERED
> >> (
> >> [DealID]
> >> )  ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblActivityTransactions] ADD
> >> CONSTRAINT [FK_tblActivityTransactions_tblActivityType] FOREIGN KEY
> >> (
> >> [ActivityTypeID]
> >> ) REFERENCES [dbo].[tblActivityType] (
> >> [ActivityTypeID]
> >> ),
> >> CONSTRAINT [FK_tblActivityTransactions_tblDeals] FOREIGN KEY
> >> (
> >> [DealID]
> >> ) REFERENCES [dbo].[tblDeals] (
> >> [DealID]
> >> )
> >> GO
> >>
> >> ALTER TABLE [dbo].[tblDeals] ADD
> >> CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
> >> (
> >> [CompanyID]
> >> ) REFERENCES [dbo].[tblCompany] (
> >> [CompanyID]
> >> )
> >> GO
> >>
> >> ***********************************************
> >>
> >> Here's the sample data INSERT statements
> >> ***********************************************
> >> INSERT INTO [tblCompany] ([CompanyID],[CompanyName])VALUES(1,'ABC

"Hugo Kornelis" wrote:

> On Fri, 25 Nov 2005 08:59:04 -0800, Sam wrote:
>
> >Uri,
> >
> >First of, thank you very much for all your help. Your SQL statement returns
> >a record for 123 Corp. for client sending us an email. This is exactly where
> >I'm getting stuck.
>
> (snip)
>
> >Ultimately what I want to get is this...
> >
> >ABC Corporation    1    2    2005-06-21 00:00:00    Voice Mail
> >XYZ Corporation    2    1    2005-05-15 00:00:00    Phone Call
> >123 Corporation    3    NULL    NULL    NULL
> >ABC Corporation    4    NULL    NULL    NULL
> >
> >Again, thanks for your help.
>
> Hi Sam,
>
> Thanks for supplying the scripts I could use for testing.
>
> The query below returns exactly the results above:
>
> SELECT     c.CompanyName, d.DealID, a.ActivityTypeID,
>            a.ActivityDateTime, t.ActivityDescription
> FROM       tblDeals AS d
> INNER JOIN tblCompany AS c
>       ON   c.CompanyID = d.CompanyID
> LEFT  JOIN tblActivityTransactions AS a
>       ON   a.DealID = d.DealID
>       AND  a.ActivityTypeID IN (1, 2, 3)
>       AND  a.ActivityDateTime = (SELECT MAX(a2.ActivityDateTime)
>                                  FROM   tblActivityTransactions AS a2
>                                  WHERE  a2.DealID = a.DealID
>                                  AND    a2.ActivityTypeID IN (1, 2, 3))
> LEFT  JOIN tblActivityType AS t
>       ON   t.ActivityTypeID = a.ActivityTypeID
>
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>