Some mental gymnastics for the end of the week

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

SQL Server Development (2000)

Some mental gymnastics for the end of the week

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-04-11 : 05:44:50

I need to clean up some legasy data.
You will need to draw this on paper with three columns with headings Status1, status2 & status3.
I have a table, StatusDuration, with 3 columns: ProjectID, StatusID & ChangeDate. I have another table, ProjStatus, with 2 columns: ProjectID & StatusID. There is a trigger on ProjStatus on Insert/Update that writes to StatusDuration the ProjectID, the Inserted/Updated StatusID and GetDate for changeDate. This has been going along merrely and now the StatusDuration table has some nice data. Unfortunatly there is also a bit of chaos as the application was not designed for projects to go backwards, some of you can see it already, so where the application did not let you change to a lower states if your project rushed ahead of itself the DBA just changed it in the backend.

Back to the three columns on paper.
A project went into status1 on the 1st, it then went to status2 on the 2nd. Opps there was a mistake so it was moved back to status1 on the 3rd. It then progressed normally to status2 on the 4th and then on to status3 on the 5th. On the 6th it was not quite finished so went back to status2, some more miss comunication and it went to status3 on the 7th then back to status2 on the 8th. At last it correctly went to status3 on the 9th.
So my statusDuration table now has 9 records but only 3 are correct. Note: I am not wanting to log a history of status changes here I simply want to know how long it stayed in each status. Thus the correct records are: Status1 on the 1st, Status2 on the 4th and status3 on the 9th.*

I need a statment to clean this table up?

*Some process logic for the correct records: Status3 obviously take the last record, Status2 take the last record that is less than the first record of Status3.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-04-11 : 06:35:32

Scott

SELECT dur.ProjectId, dur.Status, dur.Changed FROM
(
SELECT ProjectId, Status, MIN(changeDate) AS Changed
FROM StatusDuration
WHERE Status=1
GROUP BY ProjectId, Status

UNION

SELECT ProjectId, Status, MAX(changeDate) AS Changed
FROM StatusDuration
WHERE Status=2 AND changeDate <= (SELECT MIN(changeDate)FROM StatusDuration WHERE Status=3)
GROUP BY ProjectId, Status

UNION

SELECT ProjectId, Status, MAX(changeDate) AS Changed
FROM StatusDuration
WHERE Status=3 AND Changedate >= (SELECT MAX(changeDate)FROM StatusDuration WHERE Status<3)
GROUP BY ProjectId, Status
)
AS dur
ORDER BY dur.Status

Not sure whether this is what you wanted but i thought id have a go at it anyway.
This returns the results as per your example

HTH

Andy

Edited by - AndyB13 on 04/11/2003 06:38:58

Edited by - AndyB13 on 04/11/2003 06:49:46

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-04-11 : 06:54:32

I added an extra where clause to the query for status 3 as i noticed that if you remove the status 3 record on the 9th then the query will say it was complete on the 7th which isnt true as it went back to status 2 on the 8th

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources