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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy