SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

melcraig
Starting Member

39 Posts

Posted - 05/16/2006 :  19:26:48  Show Profile  Reply with Quote

In t_ParticipantStatus I have 4 fields

EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
77 1 10/6/05 10/8/05
99 1 9/01/05 9/3/05
66 5
99 7


I want to select EmpID, StartDate, CompleteDate where ModuleID = '1' and CompletedDate > '10/1/05'
Then for those EmpID I want to update the StartDate and CompleteDate for those in ModduleID = '5' the same as the StartDate, CompleteDate for ModuleID = '1'
So my results will look like this:

EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
77 1 10/6/05 10/8/05
99 1 9/01/05 9/3/05
66 5 10/1/05 10/10/05
99 7


I think I have completely confused myself!
Thanks for your help!
Mel

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 05/16/2006 :  19:37:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's what I came up with:


SELECT 
	p.EmpId, 
	p.ModuleID, 
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId


And here's my test:


CREATE TABLE t_ParticipantStatus
(
	EmpID int,
	ModuleID char(1),
	StartDate datetime,
	CompleteDate datetime
)

INSERT INTO t_ParticipantStatus VALUES(66, '1', '10/1/05', '10/10/05')
INSERT INTO t_ParticipantStatus VALUES(77, '1', '10/6/05', '10/8/05')
INSERT INTO t_ParticipantStatus VALUES(99, '1', '9/1/05', '9/3/05')
INSERT INTO t_ParticipantStatus (EmpId, ModuleID) VALUES(66, '5')
INSERT INTO t_ParticipantStatus (EmpId, ModuleID) VALUES(99, '7')

SELECT *
FROM t_ParticipantStatus

SELECT 
	p.EmpId, 
	p.ModuleID, 
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId

DROP TABLE t_ParticipantStatus


Tara Kizer
aka tduggan

Edited by - tkizer on 05/16/2006 19:38:18
Go to Top of Page

melcraig
Starting Member

39 Posts

Posted - 05/17/2006 :  10:58:02  Show Profile  Reply with Quote
I think I'm missing something!

If there is a (matching EmpID with ModuleID=1) to EmpID with ModuleID = 5 I want to update the StartDate and CompleteDate for ModuleID=5 from StartDate and CompleteDate for that EmpID with ModuleID = 1.

So I want to change the StartDate and ComleteDate for EmpID 66 for ModuleID 5
EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
66 5

To this:
EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
66 5 10/1/05 10/10/05

I think I'm in an endless confusion loop?
Thanks, Mel

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 05/17/2006 :  11:07:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's what my code does. Run my test in pubs or Northwind database to check it out.

Tara Kizer
aka tduggan
Go to Top of Page

melcraig
Starting Member

39 Posts

Posted - 05/17/2006 :  13:08:15  Show Profile  Reply with Quote
I don't know what I'm doing wrong. All my ModuleID=5 the StartDate and CompleteDate are NULL.

And they are still NULL after I run this.
They do not update with the dates that CompletedDates > 10/1/05 for the ModuleID=1

There are 41 records with for ModuleID=1 with CompletedDate > 10/01/05.
This should update the ModuleID=5 StartDate and CompleteDate

I don't know what's wrong?

I will never get TSql!
Thanks, Mel
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 05/17/2006 :  13:16:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you run my test code in pubs or Northwind to see if it gives you the results that you expect?

Tara Kizer
aka tduggan
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 05/17/2006 :  13:18:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
When you say you want it to update, do you actually want the data in the database to be updated or just the result set to look like this? My query is a select statement, so it doesn't update any data. It can easily be modified to an update though.

Tara Kizer
aka tduggan
Go to Top of Page

melcraig
Starting Member

39 Posts

Posted - 05/17/2006 :  13:55:53  Show Profile  Reply with Quote
OMG I am such an idiot. I did run your test on Pubs and it did display the change But when I went back and ran a select with Module = 5 and date > 10/1/05 is was not actually changed.
I actually do want to update the data in the database.
There is NO help for me

Thanks, Mel
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 05/17/2006 :  14:01:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's the corresponding UPDATE statement:


UPDATE p
SET
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId


Please run this in a test environment first though.

Tara Kizer
aka tduggan
Go to Top of Page

melcraig
Starting Member

39 Posts

Posted - 05/17/2006 :  15:58:29  Show Profile  Reply with Quote
Thanks Tara for ALL your help.
I will test this in development first!
Mel
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000