Complex SQL Query with group by Clause -

i not sql expert have got query

use msdb declare     @jobstepcount int, @jobname sysname, @jobid uniqueidentifier  declare @jobstatus table (jobstatusid int, jobstatus varchar(20))  insert @jobstatus (jobstatusid, jobstatus) values (0, 'failed'),     (1, 'succeeded'), (2, 'retry'),     (3, 'cancelled')  set @jobname = 'expedient bookingrefresh'  select     @jobid = job_id     sysjobs j j.name = @jobname  select @jobstepcount = count(*) dbo.sysjobsteps job_id = @jobid  select rundate, runtime, runduration, jobstatus (   select     jstart.instance_id,     rundate,     runtime,     runduration,     min(h.run_status) run_status     sysjobhistory h inner join (     select         instance_id,         jstart.instance_id - @jobstepcount  instance_range,         jstart.run_date rundate,         convert(time, left(jstart.run_time, 2) + ':' + substring(jstart.run_time, 3,     2) + ':' + right(jstart.run_time, 2)) runtime,         convert(time, left(jstart.run_duration, 2) + ':' +      substring(jstart.run_duration, 3, 2) + ':' + right(jstart.run_duration, 2)) runduration         (   select             instance_id,             convert(date, convert(varchar(8), run_date), 112) run_date,             right('000000' + convert(varchar(6), run_time), 6) run_time,             right('000000' + convert(varchar(6), run_duration), 6) run_duration                     sysjobhistory hstart                     step_id = 0         ,             job_id = @jobid     ) jstart ) jstart     on         h.instance_id between jstart.instance_range , jstart.instance_id     ,         h.job_id = @jobid     group         jstart.instance_id,         rundate,         runtime,             runduration )     inner join     @jobstatus js on     js.jobstatusid = a.run_status  order     rundate, runtime 

i result 1 below:

2013-05-09  02:15:44.0000000    00:14:46.0000000    succeeded 2013-05-09  02:56:17.0000000    23:18:25.0000000    succeeded 2013-05-10  06:00:00.0000000    01:56:18.0000000    cancelled 

i looking result this:

        date         number of runs         success count            failure/cancel count            2013-05-09      2                    2                      0            2013-05-10      1                    0                      1 

i tried combinations sum date , want able result in desired format. can me here?

thanks.

try this...

select cast(rundate date), count(*) number_of_run, sum(case when jobstatus='succeeded' 1 else 0 end) success_count,    sum(case when jobstatus='cancelled' or jobstatus='failure' 1 else 0 end) failurecancelcount   --i m using table mentioned in question...... (select rundate, runtime, runduration, jobstatus (   select     jstart.instance_id,     rundate,     runtime,     runduration,     min(h.run_status) run_status     sysjobhistory h inner join (     select         instance_id,         jstart.instance_id - @jobstepcount  instance_range,         jstart.run_date rundate,         convert(time, left(jstart.run_time, 2) + ':' + substring(jstart.run_time, 3,     2) + ':' + right(jstart.run_time, 2)) runtime,         convert(time, left(jstart.run_duration, 2) + ':' +      substring(jstart.run_duration, 3, 2) + ':' + right(jstart.run_duration, 2)) runduration         (   select             instance_id,             convert(date, convert(varchar(8), run_date), 112) run_date,             right('000000' + convert(varchar(6), run_time), 6) run_time,             right('000000' + convert(varchar(6), run_duration), 6) run_duration                     sysjobhistory hstart                     step_id = 0         ,             job_id = @jobid     ) jstart ) jstart     on         h.instance_id between jstart.instance_range , jstart.instance_id     ,         h.job_id = @jobid     group         jstart.instance_id,         rundate,         runtime,             runduration )     inner join     @jobstatus js on     js.jobstatusid = a.run_status  order     rundate, runtime) t             group rundate             order rundate