October 27, 2010 at 2:26 pm
I wish to transpose my results so that I see the 'Start' and 'Finish' date on the same row for a particular ProgressGroup.
Assumptions: There will always be a 'Start' ProgressMessage for a ProgressGroup, but not always a 'Finish' ProgressMessage for the same ProgressGroup
CREATE TABLE Progress
(
ProgressID INT,
ProgressSource VARCHAR(20),
ProgressMessage VARCHAR(20),
ProgressDate SmallDateTime,
ProgressGroup VARCHAR(20)
)
;
INSERT INTO Progress
VALUES
(1,'SSIS1','Start','2010-01-01 09:00:00','111-111-111'),
(2,'SSIS1','Finished','2010-01-01 09:05:0','111-111-111'),
(3,'SSIS1','Start','2010-01-01 09:12:00','222-222-222'),
(4,'SSIS1','Finished','2010-01-01 09:13:00','222-222-222'),
(5,'SSIS1','Start','2010-01-01 09:15:00','333-333-333'),
(6,'SSIS1','Finished','2010-01-01 09:20:00','333-333-333'),
(7,'SSIS1','Start','2010-01-01 09:25:00','444-444-444')
;
SELECT * FROM Progress;
I wish to turn my results into the below:
Progress GroupStartedFinished
111-111-1112010-01-01 09:00:002010-01-01 09:05:00
222-222-2222010-01-01 09:12:002010-01-01 09:13:00
......
444-444-4442010-01-01 09:25:00
Any help would be greatly appreciated.
Many Thanks
October 27, 2010 at 2:45 pm
I am thinking what you need is this:
select ProgressGroup,
min(ProgressDate) as Started,
case when max(ProgressDate) = min(ProgressDate) then ' ' else max(ProgressDate) end as Finished
from Progress where ProgressMessage in ('Start','Finished')
group by ProgressGroup,
order by ProgressGroup
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply