procedsure not ececuting properly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

procedsure not ececuting properly

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 01:55:41

Hi first i will telll what i want to accomplish...

1)i have to write an spwhich will show users previous project
2) there is an sp which will show users current project
3)for current project i have done it
and am taking from the table associate info
for current project
4) whereas
when theuser gets deleted from the
project
the details from associateinfo will go of
and that project which he is deleted will become the previous project
5) i have to show this previous project in the sp
using this condition project end date is greater than the current date

the tables which i access are....
Project table:
CREATE TABLE dbo.UST_PROJECT_MASTER
(
PROJ_ID VARCHAR(15) NOT NULL ,
PROJ_NAME VARCHAR(255) NOT NULL ,
SUBMOD_ID VARCHAR(25) ,
SUBMOD_NAME VARCHAR(255) NULL ,
PROJ_TYPE VARCHAR(30) NULL ,
CLIENT_NAME VARCHAR(100) NULL ,
PROJ_MANAGER VARCHAR(30) NOT NULL ,
ENG_MODEL VARCHAR(30) NULL,
START_DATE DATETIME,
END_DATE DATETIME,
TENT_START_DATE DATETIME,
TENT_END_DATE DATETIME,
PROJ_DESC VARCHAR(255),
PROJ_SUPERVISOR VARCHAR(30),
PRIMARY KEY(PROJ_ID,SUBMOD_ID)
)
associate table:

CREATE TABLE UST_ASSOCIATE_MASTER_HISTORY
(
ASSOC_ID VARCHAR(10) PRIMARY KEY ,
ASSOC_NAME VARCHAR(30) ,
ACC_ENTRY_DATE DATETIME NULL ,
SEAT_ALLOC VARCHAR(15) NULL ,
VNET INT NULL ,
IP_ADDRESS VARCHAR(20) NULL ,
ASSOC_DESIG VARCHAR(20) NULL ,
ASSOC_SKILL VARCHAR(255) NULL ,
ASSOC_LOC VARCHAR(10) NULL ,
ASSOC_VISA VARCHAR(30) NULL ,
ASSOC_DOB VARCHAR(20) NULL ,
ASSOC_MOB VARCHAR(15) NULL ,
ASSOC_OFFICE VARCHAR(20) NULL ,
ASSOC_JOIN_DATE DATETIME NULL ,
ASSOC_LEAVE_DATE DATETIME NULL ,
REASON_LEAVE VARCHAR(30) NULL
)
associate info table( for current project)
CREATE TABLE UST_ASSOCIATE_INFO
(
ASS_ID VARCHAR(10) REFERENCES ASSOCIATE_MASTER(ASSOC_ID) ,
PROJECT_ID VARCHAR(15) REFERENCES ASSOCIATE_MASTER(PROJ_ID) ,
SUBID VARCHAR(25) REFERENCES ASSOCIATE_MASTER(SUBMOD_ID) ,
ASSOC_PROJ_ENTRY DATETIME ,
ASSOC_PROJ_REL_DATE DATETIME ,
ASSOC_BILL VARCHAR(10)
)
and the sp which i used for deleting the person from the project is:
DELETE * FROM UST_ASSOCIATE_MASTER WHERE ASSOC_ID = @ASSOC_ID

and the sp which i used to view the current project is:
SELECT

ASSOC_INFO.PROJ_ID ,
PROJ_MASTER.PROJ_NAME,
ASSOC_INFO.SUBMOD_ID ,
PROJ_MASTER.SUBMOD_NAME,
PROJ_MASTER.CLIENT_NAME,
PROJ_MASTER.PROJ_MANAGER,
PROJ_MASTER.PROJ_DESC,
ASSOC_INFO.ASSOC_PROJ_ENTRY

FROM
UST_PROJECT_MASTER AS PROJ_MASTER ,
UST_ASSOCIATE_INFO AS ASSOC_INFO
WHERE ASSOC_INFO.ASSOC_ID = @ASSOC_ID AND (ASSOC_INFO.PROJ_ID = PROJ_MASTER.PROJ_ID AND ASSOC_INFO.SUBMOD_ID=PROJ_MASTER.SUBMOD_ID)

and the query which i want to view the old project what i have written is:

SELECT
PROJ.PROJ_ID,
PROJ.PROJ_NAME,
PROJ.CLIENT_NAME,
PROJ.PROJ_MANAGER,
PROJ.PROJ_DESC,
INFO.ASSOC_PROJ_ENTRY,
INFO.ASSOC_PROJ_REL_DATE
FROM
UST_PROJECT_MASTER PROJ,
UST_ASSOCIATE_MASTER ASSO,
UST_ASSOCIATE_INFO INFO

WHERE
PROJ.END_DATE < CONVERT(VARCHAR ,GETDATE(),112)
AND ASSO.ASSOC_ID = @ASSOC_ID

but its not extracting me the proper output as i wanted can anyone tell me...

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 02:04:54

may be this

SELECT 
PROJ.PROJ_ID,
PROJ.PROJ_NAME,
PROJ.CLIENT_NAME,
PROJ.PROJ_MANAGER,
PROJ.PROJ_DESC,
INFO.ASSOC_PROJ_ENTRY,
INFO.ASSOC_PROJ_REL_DATE
FROM
UST_PROJECT_MASTER PROJ
JOIN UST_ASSOCIATE_MASTER ASSO
ON PROJ.linkcolumn=ASSO.linkcolumn
LEFT JOIN UST_ASSOCIATE_INFO INFO
ON INFO.PROJ_ID = PROJ.PROJ_ID 
AND INFO.SUBMOD_ID=PROJ.SUBMOD_ID
AND PROJ.END_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND ASSO.ASSOC_ID = @ASSOC_ID
WHERE INFO.PROJ_ID IS NULL

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 02:35:50

but there is no cloumn in common which links associate master table and project master table

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 02:44:54

quote:
Originally posted by susan_151615

but there is no cloumn in common which links associate master table and project master table

susan

then why include it in query at all? you're not retrieving any values from it

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 02:51:42

as am getting the project details from the project master and associate id from associate master.

can u tell how to modify that query

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 02:55:35

quote:
Originally posted by susan_151615

as am getting the project details from the project master and associate id from associate master.

can u tell how to modify that query

susan

then what decides which associate works for which project?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 02:58:09

for the current project we can easily find by using the associate info details (associate id and project id) i dono how to do to view for previous projects can u help me in this

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 03:49:42

quote:
Originally posted by susan_151615

for the current project we can easily find by using the associate info details (associate id and project id) i dono how to do to view for previous projects can u help me in this

susan

why do you need associate_master table at all? should it be enough to return records in project_master which is not in associate_info? wont that be previous projects?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 04:59:50

ya ya exactly but howto modify the query according to it and pass the associate id

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 13:33:13

hi ig ot that sp executed i put it ina history table first and then feleted it

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 13:37:36

quote:
Originally posted by susan_151615

hi ig ot that sp executed i put it ina history table first and then feleted it

susan

why a history table. from your description i think you need only this

SELECT 
PROJ.PROJ_ID,
PROJ.PROJ_NAME,
PROJ.CLIENT_NAME,
PROJ.PROJ_MANAGER,
PROJ.PROJ_DESC,
INFO.ASSOC_PROJ_ENTRY,
INFO.ASSOC_PROJ_REL_DATE
FROM
UST_PROJECT_MASTER PROJ
LEFT JOIN UST_ASSOCIATE_INFO INFO
ON INFO.PROJ_ID = PROJ.PROJ_ID 
AND INFO.SUBMOD_ID=PROJ.SUBMOD_ID
AND PROJ.END_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND INFO.ASSOC_ID = @ASSOC_ID
WHERE INFO.PROJ_ID IS NULL

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-14 : 13:59:10

no actually i thought of doing in 2 ways one using history table (which is a very simple way) and another uusing some hwere conditions so since it took long ime i opted for the first one

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 14:05:47

quote:
Originally posted by susan_151615

no actually i thought of doing in 2 ways one using history table (which is a very simple way) and another uusing some hwere conditions so since it took long ime i opted for the first one

susan

Ok. But are you sure that what i've provided will not provide you correct results?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-15 : 00:50:36

no actually i have no idea if tit will work or not since i got errors and i felt difficulty in executing that i went for the easy method

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources