Q:

SQL Server Aggregating Rows based on Date and Status

Hi I want to know the job execution time based on the table below.
For example 1/24/17 , The time difference between Running and Completed is 17min.
**JobName**     **CurrentJobStatus**    **TimeOfChange**
    Customer    Completed           1/24/17 0:17
    Customer    Running             1/24/17 0:00
    Customer    Waiting             1/23/17 20:30
    Customer    Completed           1/21/17 0:47
    Customer    Idle               1/21/17 16:00
    Customer    Running             1/21/17 0:30
    Customer    Completed           1/20/17 0:31
    Customer    Idle                1/20/17 16:00
    Customer    Running             1/20/17 0:15
    Customer    Waiting             1/20/17 20:30
    Customer    Completed           1/19/17 0:37
    Customer    Idle                1/19/17 16:00
    Customer    Running             1/19/17 0:20
    Customer    Waiting             1/19/17 20:30

A:

Assuming there's only 1 Running and Completed job status per day and you want the time difference between the two, you can use conditional aggregation by putting a case statement inside max or min:
select * , datediff(minute, completed, running) 
from (
    select 
        convert(date, TimeOfChange) change_date,
        max(case when CurrentJobStatus = 'Completed' then TimeOfChange end) completed,
        min(case when CurrentJobStatus = 'Running' then TimeOfChange end) running
    from mytable
    group by convert(date, TimeOfChange)
) t order by change_date

