CombinedID

Hi,

Please find the SQL script to create a mini version of my database and some data at the bottom of this post. Please insert the data in the order I posted below.

Here's a quick narative of what the database is all about. This is a database that allows our company to keep track of all new contracts -- we call them deals -- and our progress on these contracts. You can think of it as a project management system. The key point here is that we have different types of projects so each contract goes through a different set of phases as we work on it. The phases a certain type of project goes through are
determined in tblPhaseType. So when we have a new deal, we put its data into tblDeals table and select the PhaseTypeID for that deal based on the kind of project it is.

Now, here's my question. Determining what phases we've completed for a specific deal is real easy. All I have to do is just, select the completed phases from tblProduction and INNER JOIN it with tblDeals and tblCompany and I'm done.

My challange is determining the remaining phases. For this, I have to first determine all phases for a specific contract then subtract out the ones that are already completed. To achieve this, I came up with a method but I'm not sure if that's the best way to do it. I combine the the Deal and Phase ID's to come up with a unique ID which I call CombinedID. Then I tell my stored procedure to exclude all CombinedID's that are in the sub-query which comes from tblProduction.

Here's the code for that stored procedure.
--------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spRemainingPhases
AS SELECT dbo.tblDeals.DealID, dbo.tblPhase.PhaseID,
CAST(dbo.tblDeals.DealID AS varchar(2)) + CAST(dbo.tblPhase.PhaseID AS
varchar(2))
 AS CombinedID
FROM dbo.tblDeals INNER JOIN
 dbo.tblPhaseType ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblDeals.PhaseTypeID INNER JOIN
 dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblPhase.PhaseTypeID
WHERE (NOT ((CAST(dbo.tblDeals.DealID AS varchar(2)) +
CAST(dbo.tblPhase.PhaseID AS varchar(2))) IN
 (SELECT CAST(tblProduction.DealID AS
varchar(2)) + CAST(tblProduction.PhaseID AS varchar(2)) AS CombinedID
 FROM tblProduction)))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------

My question is: Is this the best way to handle this. I'm just not so crazy
about this CombinedID business. I should be able use and AND construct and
use DealID and PhaseID as they are. I just couldn't get this to work though.

Here's the SQL Script create all the necessary tables in the database
----------

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_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:27 AM ******/
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
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:28 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:29 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR
[TransactionTimeStamp],
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of
phase structure this deal will go through', N'user', N'dbo', N'table',
N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage
value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column',
N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo',
N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

=========================================

And here's the data
----------

INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer
Info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write
script',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit
commercial',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer
info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write
script',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print
ad',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000
000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000
000000000000e-001)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(1,'Aug 5 2003
12:00:00:000AM',1,120.0000)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(2,'Sep 9 2003
12:00:00:000AM',2,150.0000)
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10
2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10
2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10
2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10
2003 10:24:00:000AM','Finished designing ad copy')

The key here is a LEFT OUTER JOIN to your tblProduction. This query should get you the remaining phases for all of your deals:

SELECT
dbo.tblDeals.DealID,
dbo.tblPhase.PhaseID
FROM
dbo.tblPhaseType
INNER JOIN
dbo.tblDeals ON dbo.tblDeals.PhaseTypeID = dbo.tblPhaseType.PhaseTypeID
INNER JOIN
dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID = dbo.tblPhase.PhaseTypeID
LEFT OUTER JOIN
dbo.tblProduction ON dbo.tblProduction.DealID = dbo.tblDeals.DealID AND dbo.tblProduction.PhaseID = dbo.tblPhase.PhaseID
WHERE
 dbo.tblProduction.DealID IS NULL

Terri|||That's exactly it. Thank you very much Terri. Your help is very much appreciated.

com plus catalog requirement?

Hello, this is Subhani .

I am installing Sql 2005 standard version.

The problem is that while I am installing Sql 2005 the two warning messages are given that “com plus catalog requirement” & “minimum hardware requirement”So please suggest me, what I have to do to install successfully

Hi Subhani, see this article below:

http://msdn2.microsoft.com/en-us/library/ms143690.aspx

|||

..Or this other one:

http://msdn2.microsoft.com/en-us/library/ms143698.aspx