Hey togehter,

 

i have write a SQL-Skript which gaves out the actually month, owner_name, assigned tickets, open tickets, waiting tickets and work hours for the User in the Helpdesk.

 

The Skript works fine

 

BUT

 

the ADJUSTMENT_HOURS not add correctly. PLEASE Help.

Thank you so much for help.

 

best regards Johnzko.

 

 select

DATE_FORMAT(now(),'%M %Y') AS  Month_Year,
month_AS_Number as Temp,
owner_name  as Owner_name,
JAN_HOURS,


CASE MONTH(CURDATE())
when 1
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 01 THEN total_assigned else 0 end )
when 2
THEN
-- '2'
 SUM(case when MONTH_AS_NUMBER = 02 THEN total_assigned else 0 end )
when 3
THEN
-- '3'
 SUM(case when MONTH_AS_NUMBER = 03 THEN total_assigned else 0 end )
when 4
THEN
-- '4'
 SUM(case when MONTH_AS_NUMBER = 04 THEN total_assigned else 0 end )
when 5
THEN
-- '5'
 SUM(case when MONTH_AS_NUMBER = 05 THEN total_assigned else 0 end )
when 6
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 06 THEN total_assigned else 0 end )
when 7
THEN
-- '7'
 SUM(case when MONTH_AS_NUMBER = 07 THEN total_assigned else 0 end )
when 8
THEN
-- '8'
 SUM(case when MONTH_AS_NUMBER = 08 THEN total_assigned else 0 end )
when 9
THEN
-- '9'
 SUM(case when MONTH_AS_NUMBER = 09 THEN total_assigned else 0 end )
when 10
THEN
-- '10'
 SUM(case when MONTH_AS_NUMBER = 10 THEN total_assigned else 0 end )
when 11
THEN
-- '11'
 SUM(case when MONTH_AS_NUMBER = 11 THEN total_assigned else 0 end )
when 12
THEN
-- '12'
 SUM(case when MONTH_AS_NUMBER = 12 THEN total_assigned else 0 end )
 ELSE
0
--  SUM(case when MONTH_AS_NUMBER = 07 THEN total_assigned else 0 end )
 END AS Total_Assigned,

 


CASE MONTH(CURDATE())
when 1
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 01 THEN ClosReslvRej else 0 end )
when 2
THEN
-- '2'
 SUM(case when MONTH_AS_NUMBER = 02 THEN ClosReslvRej else 0 end )
when 3
THEN
-- '3'
 SUM(case when MONTH_AS_NUMBER = 03 THEN ClosReslvRej else 0 end )
when 4
THEN
-- '4'
 SUM(case when MONTH_AS_NUMBER = 04 THEN ClosReslvRej else 0 end )
when 5
THEN
-- '5'
 SUM(case when MONTH_AS_NUMBER = 05 THEN ClosReslvRej else 0 end )
when 6
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 06 THEN ClosReslvRej else 0 end )
when 7
THEN
-- '7'
 SUM(case when MONTH_AS_NUMBER = 07 THEN ClosReslvRej else 0 end )
when 8
THEN
-- '8'
 SUM(case when MONTH_AS_NUMBER = 08 THEN ClosReslvRej else 0 end )
when 9
THEN
-- '9'
 SUM(case when MONTH_AS_NUMBER = 09 THEN ClosReslvRej else 0 end )
when 10
THEN
-- '10'
 SUM(case when MONTH_AS_NUMBER = 10 THEN ClosReslvRej else 0 end )
when 11
THEN
-- '11'
 SUM(case when MONTH_AS_NUMBER = 11 THEN ClosReslvRej else 0 end )
when 12
THEN
-- '12'
 SUM(case when MONTH_AS_NUMBER = 12 THEN ClosReslvRej else 0 end )
 ELSE  0

END AS Closed_Tickets,


CASE MONTH(CURDATE())
when 1
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 01 THEN openInProg else 0 end )
when 2
THEN
-- '2'
 SUM(case when MONTH_AS_NUMBER = 02 THEN openInProg else 0 end )
when 3
THEN
-- '3'
 SUM(case when MONTH_AS_NUMBER = 03 THEN openInProg else 0 end )
when 4
THEN
-- '4'
 SUM(case when MONTH_AS_NUMBER = 04 THEN openInProg else 0 end )
when 5
THEN
-- '5'
 SUM(case when MONTH_AS_NUMBER = 05 THEN openInProg else 0 end )
when 6
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 06 THEN openInProg else 0 end )
when 7
THEN
-- '7'
 SUM(case when MONTH_AS_NUMBER = 07 THEN openInProg else 0 end )
when 8
THEN
-- '8'
 SUM(case when MONTH_AS_NUMBER = 08 THEN openInProg else 0 end )
when 9
THEN
-- '9'
 SUM(case when MONTH_AS_NUMBER = 09 THEN openInProg else 0 end )
when 10
THEN
-- '10'
 SUM(case when MONTH_AS_NUMBER = 10 THEN openInProg else 0 end )
when 11
THEN
-- '11'
 SUM(case when MONTH_AS_NUMBER = 11 THEN openInProg else 0 end )
when 12
THEN
-- '12'
 SUM(case when MONTH_AS_NUMBER = 12 THEN openInProg else 0 end )
 ELSE
0

END AS Still_Open,

 
CASE MONTH(CURDATE())
when 1
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 01 THEN Waiting else 0 end )
when 2
THEN
-- '2'
 SUM(case when MONTH_AS_NUMBER = 02 THEN Waiting else 0 end )
when 3
THEN
-- '3'
 SUM(case when MONTH_AS_NUMBER = 03 THEN Waiting else 0 end )
when 4
THEN
-- '4'
 SUM(case when MONTH_AS_NUMBER = 04 THEN Waiting else 0 end )
when 5
THEN
-- '5'
 SUM(case when MONTH_AS_NUMBER = 05 THEN Waiting else 0 end )
when 6
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 06 THEN Waiting else 0 end )
when 7
THEN
-- '7'
 SUM(case when MONTH_AS_NUMBER = 07 THEN Waiting else 0 end )
when 8
THEN
-- '8'
 SUM(case when MONTH_AS_NUMBER = 08 THEN Waiting else 0 end )
when 9
THEN
-- '9'
 SUM(case when MONTH_AS_NUMBER = 09 THEN Waiting else 0 end )
when 10
THEN
-- '10'
 SUM(case when MONTH_AS_NUMBER = 10 THEN Waiting else 0 end )
when 11
THEN
-- '11'
 SUM(case when MONTH_AS_NUMBER = 11 THEN Waiting else 0 end )
when 12
THEN
-- '12'
 SUM(case when MONTH_AS_NUMBER = 12 THEN Waiting else 0 end )
 ELSE 0

END AS Waiting_User,


CASE MONTH(CURDATE())
when 1
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 01 THEN JAN_Hours else 0 end )
when 2
THEN
-- '2'
 SUM(case when MONTH_AS_NUMBER = 02 THEN FEB_Hours else 0 end )
when 3
THEN
-- '3'
 SUM(case when MONTH_AS_NUMBER = 03 THEN MAR_Hours else 0 end )
when 4
THEN
-- '4'
 SUM(case when MONTH_AS_NUMBER = 04 THEN APR_Hours else 0 end )
when 5
THEN
-- '5'
 SUM(case when MONTH_AS_NUMBER = 05 THEN MAY_Hours else 0 end )
when 6
THEN
-- '1'
 SUM(case when MONTH_AS_NUMBER = 06 THEN JUN_Hours else 0 end )
when 7
THEN
-- '7'
 SUM(case when MONTH_AS_NUMBER = 07 THEN JUL_Hours else 0 end )
when 8
THEN
-- '8'
 SUM(case when MONTH_AS_NUMBER = 08 THEN AUG_Hours else 0 end )
when 9
THEN
-- '9'
 SUM(case when MONTH_AS_NUMBER = 09 THEN SEP_Hours else 0 end )
when 10
THEN
-- '10'
 SUM(case when MONTH_AS_NUMBER = 10 THEN OCT_Hours else 0 end )
when 11
THEN
-- '11'
 SUM(case when MONTH_AS_NUMBER = 11 THEN NOV_Hours else 0 end )
when 12
THEN
-- '12'
 SUM(case when MONTH_AS_NUMBER = 12 THEN DEC_Hours else 0 end )
 ELSE 0
 END AS Work_Hours

 

FROM

(
select
second_part.OWNER_NAME ,
second_part.MONTH_AS_NUMBER,
second_part.YEAR ,
SUM(case when JAN_TIX IS null then 0 else JAN_TIX end) AS JAN_Tix,
FORMAT(SUM(case when JAN_WH  IS null then 0 else  JAN_WH end)  , 2) AS JAN_Hours ,

SUM(case when FEB_TIX IS null then 0 else  FEB_TIX end) AS FEB_Tix,
FORMAT(SUM(case when FEB_WH  IS null then 0 else   FEB_WH end) , 2)  AS FEB_Hours ,

SUM(case when MAR_TIX IS null then 0 else MAR_TIX end) AS MAR_Tix,
FORMAT(SUM(case when MAR_WH IS null then 0 else  MAR_WH end)  , 2) AS MAR_Hours , 

SUM(case when APR_TIX  IS null then 0 else APR_TIX end) AS APR_Tix,
FORMAT(SUM(case when APR_WH IS null then 0 else  APR_WH end)  , 2) AS APR_Hours , 

SUM(case when MAY_TIX IS null then 0 else MAY_TIX end) AS MAY_Tix,
FORMAT(SUM(case when MAY_WH IS null then 0 else  MAY_WH end)  , 2) AS MAY_Hours , 

SUM(case when JUN_TIX IS null then 0 else JUN_TIX end) AS JUN_Tix,
FORMAT(SUM(case when JUN_WH IS null then 0 else  JUN_WH end)  , 2) AS JUN_Hours , 

SUM(case when JUL_TIX IS null then 0 else JUL_TIX end) AS JUL_Tix,
FORMAT(SUM(case when JUL_WH  IS null then 0 else  JUL_WH end)  , 2) AS JUL_Hours ,

SUM(case when AUG_TIX IS null then 0 else AUG_TIX end) AS AUG_Tix,
FORMAT(SUM(case when AUG_WH IS null then 0 else  AUG_WH end)  , 2) AS AUG_Hours , 

SUM(case when SEP_TIX IS null then 0 else SEP_TIX end) AS SEP_Tix,
FORMAT(SUM(case when SEP_WH IS null then 0 else  SEP_WH end)  , 2) AS SEP_Hours , 

SUM(case when OCT_TIX IS null then 0 else OCT_TIX end) AS OCT_Tix,
FORMAT(SUM(case when OCT_WH IS null then 0 else  OCT_WH end)  , 2) AS OCT_Hours ,

SUM(case when NOV_TIX IS null then 0 else NOV_TIX end) AS NOV_Tix,
FORMAT(SUM(case when NOV_WH IS null then 0 else  NOV_WH end)  , 2) AS NOV_Hours ,

SUM(case when DEC_TIX IS null then 0 else DEC_TIX end) AS DEC_Tix,
FORMAT(SUM(case when DEC_WH IS null then 0 else  DEC_WH end)  , 2) AS DEC_Hours ,


sum(second_part.ClosReslvRej) as ClosReslvRej,
sum(second_part.openInProg ) as openInProg ,
sum(second_part.Waiting ) as Waiting,
sum(second_part.ClosReslvRej + second_part.openInProg + second_part.Waiting ) as total_assigned


from
(
select month,
OWNER_NAME,
SUM(JAN) JAN_TIX ,
SUM(JAN_HOURS) JAN_WH ,
SUM(FEB) FEB_TIX,
SUM(FEB_HOURS) FEB_WH,
SUM(MAR) MAR_TIX,
SUM(MAR_HOURS) MAR_WH,
SUM(APR) APR_TIX,
SUM(APR_HOURS) APR_WH,
SUM(MAY) MAY_TIX,
SUM(MAY_HOURS) MAY_WH,
SUM(JUN) JUN_TIX,
SUM(JUN_HOURS) JUN_WH,
SUM(JUL) JUL_TIX,
SUM(JUL_HOURS) JUL_WH,
SUM(AUG) AUG_TIX,
SUM(AUG_HOURS) AUG_WH,
SUM(SEP) SEP_TIX,
SUM(SEP_HOURS) SEP_WH,
SUM(OCT) OCT_TIX,
SUM(OCT_HOURS) OCT_WH,
SUM(NOV) NOV_TIX,
SUM(NOV_HOURS) NOV_WH,
SUM("DEC") DEC_TIX,
SUM(DEC_HOURS) DEC_WH


from (


select
OWNER_NAME, month
,sum(HOURS_WORKED) AS HOURS_WORKED
,sum(case when month_as_NUMBER = 01 then NUM_OF_TICKETS end) AS JAN
,sum(case when month_as_NUMBER = 01 then HOURS_WORKED  end) AS JAN_HOURS
,sum(case when month_as_NUMBER = 02 then NUM_OF_TICKETS end) AS FEB
,sum(case when month_as_NUMBER = 02 then HOURS_WORKED  end) AS FEB_HOURS
,sum(case when month_as_NUMBER = 03 then NUM_OF_TICKETS end) AS MAR
,sum(case when month_as_NUMBER = 03 then HOURS_WORKED  end) AS MAR_HOURS
,sum(case when month_as_NUMBER = 04 then NUM_OF_TICKETS end) AS APR
,sum(case when month_as_NUMBER = 04 then HOURS_WORKED  end) AS APR_HOURS
,sum(case when month_as_NUMBER = 05 then NUM_OF_TICKETS end) AS MAY
,sum(case when month_as_NUMBER = 05 then HOURS_WORKED  end) AS MAY_HOURS
,sum(case when month_as_NUMBER = 06 then NUM_OF_TICKETS end) AS JUN
,sum(case when month_as_NUMBER = 06 then HOURS_WORKED  end) AS JUN_HOURS
,sum(case when month_as_NUMBER = 07 then NUM_OF_TICKETS end) AS JUL
,sum(case when month_as_NUMBER = 07 then HOURS_WORKED  end) AS JUL_HOURS
,sum(case when month_as_NUMBER = 08 then NUM_OF_TICKETS end) AS AUG
,sum(case when month_as_NUMBER = 08 then HOURS_WORKED  end) AS AUG_HOURS
,sum(case when month_as_NUMBER = 09 then NUM_OF_TICKETS end) AS SEP
,sum(case when month_as_NUMBER = 09 then HOURS_WORKED  end) AS SEP_HOURS
,sum(case when month_as_NUMBER = 10 then NUM_OF_TICKETS end) AS OCT
,sum(case when month_as_NUMBER = 10 then HOURS_WORKED  end) AS OCT_HOURS

,sum(case when month_as_NUMBER = 11 then NUM_OF_TICKETS end) AS NOV
,sum(case when month_as_NUMBER = 11 then HOURS_WORKED  end) AS NOV_HOURS

,sum(case when month_as_NUMBER = 12 then NUM_OF_TICKETS end) AS "DEC"
,sum(case when month_as_NUMBER = 12 then HOURS_WORKED  end) AS DEC_HOURS


FROM (


   select
 
   S.STATE ,
   ifnull((select U.FULL_NAME from USER U where T.OWNER_ID = U.ID),' Unassigned') as OWNER_NAME,

   DATE_FORMAT(start, '%m') month_as_NUMBER,
   COUNT(T.ID) NUM_OF_TICKETS,
 
   DATE_FORMAT(start, '%M') MONTH,
   DATE_FORMAT(start, '%Y') YEAR,

 

   FORMAT(SUM((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS),2) as HOURS_WORKED,
   

   time_to_sec(timediff(stop, start))/3600.0 hours
  
   from
   (HD_WORK W, HD_TICKET T, USER U, HD_STATUS S )
   where
   W.HD_TICKET_ID = T.ID
   and
   T.HD_STATUS_ID = S.ID
   and
 
   W.USER_ID = U.ID
  
 
   GROUP BY
   U.FULL_NAME,
   DATE_FORMAT(start, '%m') , S.STATE
  
   order by 
   U.FULL_NAME ,Month(start)

)K
WHERE YEAR = "2014"
GROUP BY OWNER_NAME, MONTH,YEAR


) kk
group by
OWNER_NAME   ,month

 

 


) first_part,

 

(
SELECT YEAR, MONTH , OWNER_NAME , MONTH_AS_NUMBER,

SUM(case when status = 'Closed' then NUM_OF_TICKETS else 0 end +
 case when status = 'Resolved' then NUM_OF_TICKETS else 0 end +
 case when status = 'Rejected' then NUM_OF_TICKETS else 0 end +
 case when status = 'Rejected for quality reasons' then NUM_OF_TICKETS else 0 end +
 case when status = 'Withdrawn by customer' then NUM_OF_TICKETS else 0 end

)  AS ClosReslvRej,

SUM(case when status = 'Opened' then NUM_OF_TICKETS else 0 end +
 case when status = 'In Progress' then NUM_OF_TICKETS else 0 end

)  AS openInProg,

SUM(case when status = 'Waiting on Customer respond' then NUM_OF_TICKETS else 0 end +
 case when status = 'Postponed' then NUM_OF_TICKETS else 0 end

)  AS Waiting

 

 
FROM (

 

SELECT COUNT(ID) AS "NUM_OF_TICKETS",
 
 MONTH , YEAR, STATUS , OWNER_NAME , MONTH_AS_NUMBER
 FROM
(
select HD_TICKET.ID,
DATE_FORMAT(HD_TICKET.TIME_OPENED,'%m') MONTH_AS_NUMBER,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%M') MONTH,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%Y') YEAR,
HD_STATUS.NAME as STATUS,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME


from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID


order by Month(TIME_CLOSED),OWNER_NAME,
 HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL
 )  AAA

GROUP BY  MONTH , YEAR, STATUS , OWNER_NAME
ORDER BY OWNER_NAME,MONTH_AS_NUMBER, YEAR, MONTH, STATUS

) DDD
WHERE YEAR ="2014"


group by
YEAR, MONTH, OWNER_NAME , MONTH_AS_NUMBER

 

) second_part

WHERE second_part.OWNER_NAME= first_part.OWNER_NAME
AND second_part.month= first_part.month
  group by second_part.OWNER_NAME , second_part.MONTH_AS_NUMBER

order by second_part.OWNER_NAME , second_part.MONTH_AS_NUMBER


) GOG
GROUP BY OWNER_NAME

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share