Scripting

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