Update Query 2 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update Query 2

Author

Topic

beatkeeper25
Starting Member

27 Posts

Posted - 2013-08-06 : 21:46:48

Table1 consists of MemberID INT, CourseID INT, CompletionDate DATETIME, DueDate DATETIME.
Table2 consists of CourseID and RecurrenceMonths INT
Table3 consists of MemberID and Unit
Function1-gets all children of parent unit (returns table)

Tasks to be completed are:
1) For all users who belong to result set of function (select * from function1(unit1)), transfer CompletionDate from course1 to course2 WHERE CompletionDate for Course2 is older than CompletionDate for Course1 AND CompletionDate for Course1 less than 1 year old.
2) For all those CompletionDates that are transferred to Course2, clear them for Course1.
3) Update DueDate for each row that is transferred. Due date is CompletionDate + RecurrenceMonths

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-06 : 22:25:54

What have you tried so far?

Here are some links to help you post your questiopn so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PS - We don't do homework, but we will help guide you into a solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 00:51:34

Didnt you get a start from this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187307

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beatkeeper25
Starting Member

27 Posts

Posted - 2013-08-07 : 10:37:27

Yes, I did get that to work, but not sure how to incorporate these new filters. Need to perform this for only memberid that are in the result set of a function. Also need to clear the CompletionDate for Course1 after date is moved to Course2. When doing multiple tasks on a set, do you save it to a temp table or something?

beatkeeper25
Starting Member

27 Posts

Posted - 2013-08-07 : 16:02:34

OK, I think this is everything I need. Don't want to incorporate step 2 from above at this time. I'm new to SQL coding, so let me know how to shorten this or optimize.

--create temp table and populate with course 1063 data
IF OBJECT_ID('tempdb.dbo.#TempTable1063') IS NOT NULL
DROP TABLE dbo.#TempTable1063;

SET NOCOUNT ON
CREATE TABLE #TempTable1063
(
MemberID INT NOT NULL PRIMARY KEY,
CourseID INT NOT NULL,
CompletionDate DATETIME,
SourceID INT
);

INSERT INTO #TempTable1063(MemberID, CourseID, CompletionDate, SourceID)
SELECT MemberID, CourseID, CompletionDate, SourceID
FROM dbo.MemberCourseRequirement
WHERE courseID=1063

--create temp table and populate with course 8779 data

IF OBJECT_ID('tempdb.dbo.#TempTable8779') IS NOT NULL
DROP TABLE dbo.#TempTable8779;

CREATE TABLE #TempTable8779
(
MemberID INT NOT NULL PRIMARY KEY,
CourseID INT NOT NULL,
CompletionDate DATETIME,
SourceID INT
);

INSERT INTO #TempTable8779(MemberID, CourseID, CompletionDate, SourceID)
SELECT MemberID, CourseID, CompletionDate, SourceID
FROM dbo.MemberCourseRequirement
WHERE courseID=8779

--combine data into #TempCombined
IF OBJECT_ID('tempdb.dbo.#TempCombined') IS NOT NULL
DROP TABLE dbo.#TempCombined;
SELECT a.completiondate AS '8779 Comp Date', b.completiondate AS '1063 comp date', a.memberID
INTO #TempCombined
FROM #TempTable8779 AS a
JOIN #TempTable1063 AS b
ON a.MemberID = b.MemberID
AND a.CompletionDate < b.CompletionDate
WHERE b.CompletionDate > dateadd(year, -1, getdate())
ORDER BY '8779 Comp Date' DESC

--create set of affected users and put into #Users
IF OBJECT_ID('tempdb.dbo.#Users') IS NOT NULL
DROP TABLE dbo.#Users;
select *
into #Users
from dbo.membercourserequirement
where memberid in (
select memberid
from vcurrentmember
where unitid in (select unitid
from dbo.fn_getchildrenofparentunit(39))
and isdefault = 1)
and courseid = 1063
and completiondate > dateadd(year, -1, getdate());

--Update Data, transfer completion date
SET NOCOUNT OFF
PRINT N'Transfering Completion Dates from 1063 to 8779'
UPDATE dbo.MemberCourseRequirement
SET CompletionDate = s.[1063 comp date]
FROM dbo.MemberCourseRequirement g
INNER JOIN #TempCombined s ON g.memberid=s.memberid
WHERE g.CourseID=8779 AND EXISTS(SELECT MemberID FROM #Users)

--update the duedate from the completion dates
UPDATE dbo.MemberCourseRequirement
SET duedate=DATEADD(yy,1,ISNULL(completiondate,0))
WHERE memberid IN (SELECT memberid FROM #Users) AND courseid=8779
AND NOT duedate=DATEADD(yy,1,ISNULL(completiondate,0)) AND ISNULL(completiondate,0)>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources