SELECT 'Inbound' "Status",  

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) '1 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) '2 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) '3 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) '4 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)  '5 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)  '6 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)  '7 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)  '8 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)  '9 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)  '10 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)  '11 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)  '12 PM', 

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=13 THEN 1 ELSE NULL END) '1 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=14 THEN 1 ELSE NULL END) '2 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=15 THEN 1 ELSE NULL END) '3 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=16 THEN 1 ELSE NULL END) '4 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=17 THEN 1 ELSE NULL END) '5 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=18 THEN 1 ELSE NULL END) '6 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=19 THEN 1 ELSE NULL END) '7 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=20 THEN 1 ELSE NULL END) '8 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=21 THEN 1 ELSE NULL END) '9 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=22 THEN 1 ELSE NULL END) '10 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=23 THEN 1 ELSE NULL END) '11 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,createdtime/1000,'1970-01-01 00:00:00'))=0 THEN 1 ELSE NULL END) '12 AM' from workorder where workorder.ISPARENT=1 AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2013-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2013-12-31 23:59',21)

UNION 

SELECT 'Completed' "Status", 

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END)'1AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END)'2AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END)'3AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END)'4AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)'5AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)'6AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)'7AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)'8AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)'9AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)'10AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)'11AM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)'12PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=13 THEN 1 ELSE NULL END)'1PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=14 THEN 1 ELSE NULL END)'2PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=15 THEN 1 ELSE NULL END)'3PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=16 THEN 1 ELSE NULL END)'4PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=17 THEN 1 ELSE NULL END)'5PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=18 THEN 1 ELSE NULL END)'6PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=19 THEN 1 ELSE NULL END)'7PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=20 THEN 1 ELSE NULL END)'8PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=21 THEN 1 ELSE NULL END)'9PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=22 THEN 1 ELSE NULL END)'10PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=23 THEN 1 ELSE NULL END)'11PM',

COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN datepart(hh,dateadd(s,completedtime/1000,'1970-01-01 00:00:00'))=0 THEN 1 ELSE NULL END)'12AM' from workorder where workorder.ISPARENT=1 AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2013-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2013-12-31 23:59',21)

UNION 

SELECT 'OverDue' "Status", 

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) '1 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) '2 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) '3 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) '4 AM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)  '5 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)  '6 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)  '7 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)  '8 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)  '9 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)  '10 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)  '11 AM' ,

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)  '12 PM', 

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=13 THEN 1 ELSE NULL END) '1 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=14 THEN 1 ELSE NULL END) '2 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=15 THEN 1 ELSE NULL END) '3 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=16 THEN 1 ELSE NULL END) '4 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=17 THEN 1 ELSE NULL END) '5 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=18 THEN 1 ELSE NULL END) '6 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=19 THEN 1 ELSE NULL END) '7 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=20 THEN 1 ELSE NULL END) '8 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=21 THEN 1 ELSE NULL END) '9 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=22 THEN 1 ELSE NULL END) '10 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=23 THEN 1 ELSE NULL END) '11 PM',

COUNT(CASE WHEN datepart(hh,dateadd(s,duebytime/1000,'1970-01-01 00:00:00'))=0 THEN 1 ELSE NULL END) '12 AM' from workorder  LEFT JOIN WorkOrderStates ON workorder.WORKORDERID = WorkOrderStates.WORKORDERID where WorkOrderStates.ISOVERDUE=1 and workorder.ISPARENT=1 AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2013-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2013-12-31 23:59',21)