June 2, 2009 at 10:03 am
Hi, I'm almost embarressed to post this question it looks quite simple - but its late and I just can't work it out.
I Have 3 tables - as follows
CREATE TABLE Cust ( CID INT IDENTITY(1,1) PRIMARY KEY,
CustName varchar(10),
Active bit );
INSERT INTO Cust (CustName, Active)
SELECT 'Tom', 1 UNION ALL
SELECT 'Anne', 0 UNION ALL
SELECT 'Paul', 1 UNION ALL
SELECT 'Julia', 1;
CREATE TABLE Milestone (MID INT IDENTITY(1,1),
MSCode varchar(3),
MSText varchar(100),
SEQ INT NOT NULL );
INSERT INTO Milestone (MSCode, MSText, SEQ)
SELECT 'PS', 'Project Start', 3 UNION ALL
SELECT 'PSM', 'Startup Meeting', 5 UNION ALL
SELECT 'RQ', 'Requirements', 7 UNION ALL
SELECT 'UM', 'User Meetings', 11 UNION ALL
SELECT 'SP', 'Specification', 13 UNION ALL
SELECT 'RSO', 'Requirements Sign Off', 17 UNION ALL
SELECT 'SD', 'System Design', 23 UNION ALL
SELECT 'PCO', 'Proof of Concept', 28;
CREATE TABLE Project (PID INT IDENTITY(1,1),
ProjName varchar(10) ,
StartMilestone varchar(3),
EndMilestone varchar(3),
CustID INT Foreign Key References Cust(CID) );
INSERT INTO Project (ProjName, StartMileStone, EndMilestone, CustID)
SELECT 'P1', 'PS', 'PSM', 1 UNION ALL
SELECT 'P2', 'RQ', 'SP', 1 UNION ALL
SELECT 'P3', 'SP', 'SD', 2 UNION ALL
SELECT 'A1', 'PS', 'PCO', 3 UNION ALL
SELECT 'A2', 'UM', 'SD', 4 UNION ALL
SELECT 'B1', 'PSM', 'SD', 4;
In the Milestones table the SEQ column is the sequence in which the milestones must be completed. The Identity column cannot be used because the client can re-assign SEQ values to re-order the Milestone table. My client wants to view all the projects which contain a given milestone, so if the supplied milestone falls within the start and end of the project milestones, then return that project if the customer is active.
DECLARE @MStone varchar(3)
SET @MStone = 'UM'
SELECT Project.ProjName, Cust.CustName
FROM Project
JOIN Cust on Project.CustID = Cust.CID
WHERE
...
... ( -- what subquery do I need to get the range ?? )
...
AND Cust.Active = 1
Required Results for Milestone 'UM' would be
A1 Paul
A2 Julia
B1 Julia
Thanks
Tom
June 2, 2009 at 10:40 am
I get 4 records back based on your sample data. Please see the code below and let me know what I missed.
CREATE TABLE dbo.Cust ( CID INT IDENTITY(1,1) PRIMARY KEY,
CustName varchar(10),
Active bit );
INSERT INTO dbo.Cust (CustName, Active)
SELECT 'Tom', 1 UNION ALL
SELECT 'Anne', 0 UNION ALL
SELECT 'Paul', 1 UNION ALL
SELECT 'Julia', 1;
CREATE TABLE dbo.Milestone (MID INT IDENTITY(1,1),
MSCode varchar(3),
MSText varchar(100),
SEQ INT NOT NULL );
INSERT INTO dbo.Milestone (MSCode, MSText, SEQ)
SELECT 'PS', 'Project Start', 3 UNION ALL
SELECT 'PSM', 'Startup Meeting', 5 UNION ALL
SELECT 'RQ', 'Requirements', 7 UNION ALL
SELECT 'UM', 'User Meetings', 11 UNION ALL
SELECT 'SP', 'Specification', 13 UNION ALL
SELECT 'RSO', 'Requirements Sign Off', 17 UNION ALL
SELECT 'SD', 'System Design', 23 UNION ALL
SELECT 'PCO', 'Proof of Concept', 28;
CREATE TABLE dbo.Project (PID INT IDENTITY(1,1),
ProjName varchar(10) ,
StartMileStone varchar(3),
EndMileStone varchar(3),
CustID INT Foreign Key References Cust(CID) );
INSERT INTO dbo.Project (ProjName, StartMileStone, EndMileStone, CustID)
SELECT 'P1', 'PS', 'PSM', 1 UNION ALL
SELECT 'P2', 'RQ', 'SP', 1 UNION ALL
SELECT 'P3', 'SP', 'SD', 2 UNION ALL
SELECT 'A1', 'PS', 'PCO', 3 UNION ALL
SELECT 'A2', 'UM', 'SD', 4 UNION ALL
SELECT 'B1', 'PSM', 'SD', 4;
select
*
from
dbo.Cust;
select
*
from
dbo.Milestone;
select
*
from
dbo.Project;
declare @MileStone varchar(3);
set @MileStone = 'UM';
select
*
from
dbo.Cust
inner join dbo.Project
on (Cust.CID = Project.CustID)
inner join dbo.Milestone ms
on (Project.StartMileStone = ms.MSCode)
inner join dbo.Milestone me
on (Project.EndMileStone = me.MSCode)
inner join dbo.Milestone mq
on (mq.SEQ between ms.SEQ and me.SEQ
and mq.MSCode = @MileStone)
where
Cust.Active = 1
order by
Cust.CID;
drop table dbo.Project;
drop table dbo.Milestone;
drop table dbo.Cust;
June 2, 2009 at 11:31 am
Thanks Lynn
Your solution is correct - I didn't examine my own test data thoroughly enough. :blush:
June 2, 2009 at 11:37 am
No problem. I just thought I might have missed something. Glad to hear that this works for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply