Help!!! on stored procedure!!!

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

Transact-SQL (2000)

Help!!! on stored procedure!!!

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-12-04 : 16:32:26

Hi all,

I have table1, table2

+ Table1 has AccountID, RoomID
+ Table2 has AccountID, RoomID, D_Date

I want to create a SP so it will do the following things for me:

IF table1.accountID = table2.AccountID
1)set table2.D_Date to empty
2)set table2.D_date = D_Date + 1 where table2.RoomID= table1.RoomID

ELSE
set table2.D_date = D_Date + 1 where table2.RoomID= table1.RoomID

thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-04 : 16:42:24

Your question doesn't make any sense.

If there is a record in table 2 that matches on both AccountID and RoomID, incriment the date. If there is not a matching record, incriment the date.

I think that your question is flawed. Try to repost your question.
Michael

BTW, here's the code I started on to solve the problem. This is really for me in case you repost

 
CREATE TABLE #Table1(AccountID INT, RoomID INT)
CREATE TABLE #Table2(AccountID INT, RoomID INT, D_Date DATETIME)

INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 1)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 2)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 3)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 4)


INSERT INTO #Table2(AccountID, RoomID, D_Date) VALUES(1, 2, '12/2/2002')
INSERT INTO #Table2(AccountID, RoomID, D_Date) VALUES(1, 4, '12/4/2002')

SELECT t1.AccountID, t1.RoomID, t2.D_Date
FROM #Table1 t1
INNER JOIN #Table2 t2 ON t1.AccountID = t2.AccountID AND t1.RoomID = t2.RoomID


DROP TABLE #Table1
DROP TABLE #Table2

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-04 : 16:46:42

CREATE TABLE #Table1(AccountID INT, RoomID INT)
CREATE TABLE #Table2(AccountID INT, RoomID INT, D_Date DATETIME)

INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 1)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 2)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 3)
INSERT INTO #Table1(AccountID, RoomID) VALUES(1, 4)

INSERT INTO #Table2(AccountID, RoomID, D_Date) VALUES(1, 4, '12/2/2002')
INSERT INTO #Table2(AccountID, RoomID, D_Date) VALUES(3, 4, '12/4/2002')
INSERT INTO #Table2(AccountID, RoomID, D_Date) VALUES(1,5, '12/3/2002')

SELECT t1.AccountID, t1.RoomID, t2.AccountID, t2.RoomID, t2.D_Date
FROM #Table2 t2
LEFT JOIN #Table1 t1 ON t1.AccountID = t2.AccountID

UPDATE #Table2
Set #Table2.D_DATE =
CASE
WHEN #Table1.AccountID IS NOT NULL AND
#Table2.RoomID <> #Table1.RoomID THEN NULL
ELSE DATEADD(d,1,#Table2.D_date)
END
FROM
#Table2 LEFT JOIN #Table1 ON #Table1.AccountID = #Table2.AccountID

SELECT t2.AccountID, t2.RoomID, t2.D_Date
FROM #Table2 t2

DROP TABLE #Table1
DROP TABLE #Table2

Edited by - ValterBorges on 12/04/2002 17:02:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources