October 17, 2005 at 8:31 am
Is there anyways to do this?
I have list of dates against a case (date it was opened, date a report was done and is next due etc). Everything is in a table is called DATES with the headings; case ref...datecode...actual date...next date.
eg.
ABC/0002/0860...FILREV...NULL...NULL
ABC/0002/0860...STATUS...2005-01-25...2005-01-25
DEF/0001/4756...FILREV...NULL...NULL
DEF/0001/4756...STATUS...2005-01-25...2005-01-25
GHI/0001/2000...STATUS...2005-01-25...2005-01-25
STU/0001/3000...FILREV...2005-01-25...2005-01-25
Where the FILREV is null, I need to update it with the STATUS dates.
Where there is no FILREV, I need to create it with the STATUS dates
Where the is no STATUS, I leave FILREV alone.
October 17, 2005 at 10:20 am
Test before running script in production.
create table #tempA (CaseRef varchar(50), DateCode varchar(10), DateOpened datetime, DateDue datetime)
INSERT INTO #tempA VALUES ('ABC/0002/0860', 'FILREV', Null,Null)
INSERT INTO #tempA VALUES ('ABC/0002/0860', 'STATUS', '2005-01-25','2005-01-25')
INSERT INTO #tempA VALUES ('DEF/0001/4756', 'FILREV', Null,Null)
INSERT INTO #tempA VALUES ('DEF/0001/4756', 'STATUS', '2005-01-25','2005-01-25')
INSERT INTO #tempA VALUES ('GHI/0001/2000', 'STATUS', '2005-01-25','2005-01-25')
INSERT INTO #tempA VALUES ('STU/0001/3000', 'FILREV', '2005-01-25','2005-01-25')
UPDATE #tempA SET #tempA.DateOpened = b.DateOpened, #tempA.DateDue = b.DateDue
FROM #tempA INNER JOIN #tempA b ON #tempA.CaseRef = b.CaseRef
WHERE #tempA.DateCode = 'FILREV' AND #tempA.DateOpened IS NULL and #tempA.DateDue IS NULL
AND b.DateCode = 'STATUS'
INSERT INTO #tempA
SELECT a.CaseRef, 'FILREV', a.DateOpened, a.DateDue
FROM #tempA a LEFT OUTER JOIN #tempA b ON a.CaseRef = b.CaseRef AND b.DateCode = 'FILREV'
WHERE b.CaseRef IS NULL AND a.DateCode = 'STATUS'
SELECT * FROM #tempA
DROP TABLE #tempA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply