Database

select	PI,JobID,FormID,ShiftID,min(SW) as SW,min(SG) as SG,max(EG) as EG,
        max(EW) as EW,Min(StartEvent) as StartEvent,Max(EndEvent) as EndEvent
from someTable
group by PI,JobID,FormID,ShiftID

milo-xml wrote:

I wanted an challenge, I got one

select min(dt.[pi]),min(dt.jobid),min(dt.formid),min(dt.shiftid), min(dt.startevent),
(
select top 1 (dt.sw)
 from datatable dt
order by dt.endevent

)as sw,
(
select top 1 (dt.sg)
 from datatable dt
order by dt.endevent

)as sg,
(
select top 1 (dt.endevent)
 from datatable dt
order by dt.endevent desc

) as endevent,
(
select top 1 (dt.eg)
 from datatable dt
order by dt.endevent desc

) as eg,
(
select top 1 (dt.ew)
 from datatable dt
order by dt.endevent desc

) as ew
 from datatable dt

group by dt.[pi],dt.jobid,dt.shiftid

SELECT t1.PI,
    t1.JobID,
    t1.FormID,
    t1.ShiftID,
    t2.StartEvent,
    t2.SW,
    t2.SG,
    t3.EndEvent,
    t3.EG,
    t3.EW
FROM
    (
        SELECT DISTINCT PI, JobID, FormID, ShiftID
        FROM [table]
    ) t1
    CROSS APPLY
        (
            SELECT TOP 1 StartEvent, SW, SG
            FROM [table]
            WHERE PI = t1.PI
                AND JobID = t1.JobID
                AND FormID = t1.FormID
                AND ShiftID = t1.ShiftID
            ORDER BY StartEvent ASC
        ) t2
    CROSS APPLY
        (
            SELECT TOP 1 EndEvent, EG, EW
            FROM [table]
            WHERE PI = t1.PI
                AND JobID = t1.JobID
                AND FormID = t1.FormID
                AND ShiftID = t1.ShiftID
            ORDER BY EndEvent DESC
        ) t3