API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 22nd, 2017
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 122.95 KB | None | 0 0
raw download clone embed print report
  1. SELECT 'BB' AS "Id",
  2. 'Advanced IM Closure' AS "Description",
  3. ROUND ( (a.case_sayisi - b.case_sayisi) / a.case_sayisi * 100, 2)
  4. AS "Oran"
  5. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  6. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  7. COUNT (DISTINCT b.cid) case_sayisi,
  8. SUM (B.SURE) SURE
  9. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  10. a.INCIDENT_ID,
  11. A."NUMBER" cid,
  12. a.SURE
  13. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  14. A.INCIDENT_ID,
  15. A."NUMBER",
  16. A.AFFECTED_ITEM,
  17. a.tt_tech,
  18. A.PROBLEM_STATUS,
  19. C.SCHEDULE,
  20. product_type,
  21. OPEN_TIME,
  22. a.resolved_group,
  23. SUBSTR (
  24. c.NAME,
  25. INSTR (c.NAME, ' Assignment:') + 12,
  26. INSTR (c.NAME, 'Assignee:')
  27. - INSTR (c.NAME, ' Assignment:')
  28. - 13
  29. )
  30. ASSIGNMENT,
  31. SUBSTR (
  32. c.NAME,
  33. INSTR (c.NAME, 'Status:') + 7,
  34. INSTR (c.NAME, 'ParentAssignment:')
  35. - INSTR (c.NAME, 'Status:')
  36. - 8
  37. )
  38. STATU,
  39. SUBSTR (
  40. c.NAME,
  41. INSTR (c.NAME, ' Assignee:') + 10,
  42. LENGTH (c.NAME)
  43. - INSTR (c.NAME, 'Assignee:')
  44. - 1
  45. )
  46. ASSIGNEE,
  47. c.name,
  48. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  49. FULL_NAME,
  50. ROUND (
  51. (c.TOTAL - TO_DATE ('01.01.4000'))
  52. * 24,
  53. 2
  54. )
  55. SURE
  56. FROM SMKONS.PROBSUMMARYM1 a
  57. JOIN
  58. SMKONS.CLOCKSM1 c
  59. ON c.KEY_CHAR = a."NUMBER" --AND C.KEY_CHAR='SD10527'
  60. LEFT JOIN
  61. SMKONS.OPERATORM1 b
  62. ON B.NAME =
  63. SUBSTR (
  64. c.NAME,
  65. INSTR (c.NAME, ' Assignee:')
  66. + 10,
  67. LENGTH (c.NAME)
  68. - INSTR (c.NAME, 'Assignee:')
  69. - 1
  70. )
  71. WHERE A.PROBLEM_STATUS = 'Closed'
  72. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  73. 2017
  74. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  75. AND c.SCHEDULE = '24x7'
  76. AND c.name LIKE 'Composite 24x7 %') a
  77. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  78. AND a.STATU IN
  79. ('Open',
  80. 'Work In Progress',
  81. 'Vendor Resolved',
  82. 'Assign',
  83. 'Problem Closed',
  84. 'Pending Vendor',
  85. 'Pending Vendor Update',
  86. 'Pending Other',
  87. 'Replaced Problem',
  88. 'Re-Opened',
  89. 'Other Group Resolved',
  90. 'Change Completed')
  91. AND a.SCHEDULE = '24x7'
  92. AND a.affected_item NOT IN 'Gelir Güvencesi'
  93. AND a.tt_tech = 'Broadband'
  94. AND a.ASSIGNMENT IN
  95. (SELECT name
  96. FROM SMKONS.assignmentm1
  97. WHERE name IN
  98. ('BAS',
  99. 'CRM DATA OPERASYON',
  100. 'CRM INCIDENT',
  101. 'CRM INHOUSE',
  102. 'CRM KONFIGURASYON',
  103. 'CRM Konfigürasyon',
  104. 'CRM NETYUZ OIM CTS SUPPORT',
  105. 'CRM OPERASYON',
  106. 'Data Operasyon',
  107. 'IYS Destek PM',
  108. 'Servis Operasyon',
  109. 'CSS'))) b
  110. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  111. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  112. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  113. FROM (SELECT CLOSE_YEAR,
  114. MONTH,
  115. INCIDENT_ID,
  116. SURE
  117. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  118. CLOSE_YEAR,
  119. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  120. B.cid INCIDENT_ID,
  121. SUM (B.SURE) SURE
  122. FROM (SELECT TRUNC (RESOLVED_TIME)
  123. AS CLOSE_TIME,
  124. a.INCIDENT_ID,
  125. A."NUMBER" cid,
  126. a.SURE
  127. FROM (SELECT A.CLOSE_TIME
  128. RESOLVED_TIME,
  129. A.INCIDENT_ID,
  130. A."NUMBER",
  131. A.AFFECTED_ITEM,
  132. a.tt_tech,
  133. A.PROBLEM_STATUS,
  134. C.SCHEDULE,
  135. product_type,
  136. OPEN_TIME,
  137. a.resolved_group,
  138. SUBSTR (
  139. c.NAME,
  140. INSTR (
  141. c.NAME,
  142. ' Assignment:'
  143. )
  144. + 12,
  145. INSTR (
  146. c.NAME,
  147. 'Assignee:'
  148. )
  149. - INSTR (
  150. c.NAME,
  151. ' Assignment:'
  152. )
  153. - 13
  154. )
  155. ASSIGNMENT,
  156. SUBSTR (
  157. c.NAME,
  158. INSTR (
  159. c.NAME,
  160. 'Status:'
  161. )
  162. + 7,
  163. INSTR (
  164. c.NAME,
  165. 'ParentAssignment:'
  166. )
  167. - INSTR (
  168. c.NAME,
  169. 'Status:'
  170. )
  171. - 8
  172. )
  173. STATU,
  174. SUBSTR (
  175. c.NAME,
  176. INSTR (
  177. c.NAME,
  178. ' Assignee:'
  179. )
  180. + 10,
  181. LENGTH (c.NAME)
  182. - INSTR (
  183. c.NAME,
  184. 'Assignee:'
  185. )
  186. - 1
  187. )
  188. ASSIGNEE,
  189. c.name,
  190. NVL (
  191. b.FULL_NAME,
  192. 'HAVUZDA BEKLEME'
  193. )
  194. FULL_NAME,
  195. ROUND (
  196. (c.TOTAL
  197. - TO_DATE('01.01.4000'))
  198. * 24,
  199. 2
  200. )
  201. SURE
  202. FROM SMKONS.PROBSUMMARYM1 a
  203. JOIN
  204. SMKONS.CLOCKSM1 c
  205. ON c.KEY_CHAR =
  206. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  207. LEFT JOIN
  208. SMKONS.OPERATORM1 b
  209. ON B.NAME =
  210. SUBSTR (
  211. c.NAME,
  212. INSTR (
  213. c.NAME,
  214. ' Assignee:'
  215. )
  216. + 10,
  217. LENGTH (
  218. c.NAME
  219. )
  220. - INSTR (
  221. c.NAME,
  222. 'Assignee:'
  223. )
  224. - 1
  225. )
  226. WHERE A.PROBLEM_STATUS =
  227. 'Closed'
  228. AND TO_CHAR (
  229. a.CLOSE_TIME,
  230. 'YYYY'
  231. ) = 2017
  232. AND TO_CHAR (
  233. (a.CLOSE_TIME),
  234. 'MM'
  235. ) = 05
  236. AND c.SCHEDULE =
  237. '24x7'
  238. AND c.name LIKE
  239. 'Composite 24x7 %')
  240. a
  241. WHERE a.STATU NOT IN
  242. ('Resolved', 'Closed')
  243. AND a.STATU IN
  244. ('Open',
  245. 'Work In Progress',
  246. 'Vendor Resolved',
  247. 'Assign',
  248. 'Problem Closed',
  249. 'Pending Vendor',
  250. 'Pending Vendor Update',
  251. 'Pending Other',
  252. 'Replaced Problem',
  253. 'Re-Opened',
  254. 'Other Group Resolved',
  255. 'Change Completed')
  256. AND a.SCHEDULE = '24x7'
  257. AND a.affected_item NOT IN
  258. 'Gelir Güvencesi'
  259. AND a.tt_tech = 'Broadband'
  260. AND a.ASSIGNMENT IN
  261. (SELECT name
  262. FROM SMKONS.assignmentm1
  263. WHERE name IN
  264. ('BAS',
  265. 'CRM DATA OPERASYON',
  266. 'CRM INCIDENT',
  267. 'CRM INHOUSE',
  268. 'CRM KONFIGURASYON',
  269. 'CRM Konfigürasyon',
  270. 'CRM NETYUZ OIM CTS SUPPORT',
  271. 'CRM OPERASYON',
  272. 'Data Operasyon',
  273. 'IYS Destek PM',
  274. 'Servis Operasyon',
  275. 'CSS')))
  276. b
  277. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  278. TO_CHAR ( (CLOSE_TIME), 'MM'),
  279. B.cid)
  280. WHERE SURE > 4)
  281. GROUP BY CLOSE_YEAR, MONTH) B
  282. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
  283. UNION ALL
  284. SELECT 'Advanced IM Closure' AS "Id",
  285. 'SLA_OK' AS "Description",
  286. a.case_sayisi - b.case_sayisi
  287. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  288. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  289. COUNT (DISTINCT b.cid) case_sayisi,
  290. SUM (B.SURE) SURE
  291. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  292. a.INCIDENT_ID,
  293. A."NUMBER" cid,
  294. a.SURE
  295. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  296. A.INCIDENT_ID,
  297. A."NUMBER",
  298. A.AFFECTED_ITEM,
  299. a.tt_tech,
  300. A.PROBLEM_STATUS,
  301. C.SCHEDULE,
  302. product_type,
  303. OPEN_TIME,
  304. a.resolved_group,
  305. SUBSTR (
  306. c.NAME,
  307. INSTR (c.NAME, ' Assignment:') + 12,
  308. INSTR (c.NAME, 'Assignee:')
  309. - INSTR (c.NAME, ' Assignment:')
  310. - 13
  311. )
  312. ASSIGNMENT,
  313. SUBSTR (
  314. c.NAME,
  315. INSTR (c.NAME, 'Status:') + 7,
  316. INSTR (c.NAME, 'ParentAssignment:')
  317. - INSTR (c.NAME, 'Status:')
  318. - 8
  319. )
  320. STATU,
  321. SUBSTR (
  322. c.NAME,
  323. INSTR (c.NAME, ' Assignee:') + 10,
  324. LENGTH (c.NAME)
  325. - INSTR (c.NAME, 'Assignee:')
  326. - 1
  327. )
  328. ASSIGNEE,
  329. c.name,
  330. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  331. FULL_NAME,
  332. ROUND (
  333. (c.TOTAL - TO_DATE ('01.01.4000'))
  334. * 24,
  335. 2
  336. )
  337. SURE
  338. FROM SMKONS.PROBSUMMARYM1 a
  339. JOIN
  340. SMKONS.CLOCKSM1 c
  341. ON c.KEY_CHAR = a."NUMBER" --AND C.KEY_CHAR='SD10527'
  342. LEFT JOIN
  343. SMKONS.OPERATORM1 b
  344. ON B.NAME =
  345. SUBSTR (
  346. c.NAME,
  347. INSTR (c.NAME, ' Assignee:')
  348. + 10,
  349. LENGTH (c.NAME)
  350. - INSTR (c.NAME, 'Assignee:')
  351. - 1
  352. )
  353. WHERE A.PROBLEM_STATUS = 'Closed'
  354. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  355. 2017
  356. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  357. AND c.SCHEDULE = '24x7'
  358. AND c.name LIKE 'Composite 24x7 %') a
  359. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  360. AND a.STATU IN
  361. ('Open',
  362. 'Work In Progress',
  363. 'Vendor Resolved',
  364. 'Assign',
  365. 'Problem Closed',
  366. 'Pending Vendor',
  367. 'Pending Vendor Update',
  368. 'Pending Other',
  369. 'Replaced Problem',
  370. 'Re-Opened',
  371. 'Other Group Resolved',
  372. 'Change Completed')
  373. AND a.SCHEDULE = '24x7'
  374. AND a.affected_item NOT IN 'Gelir Güvencesi'
  375. AND a.tt_tech = 'Broadband'
  376. AND a.ASSIGNMENT IN
  377. (SELECT name
  378. FROM SMKONS.assignmentm1
  379. WHERE name IN
  380. ('BAS',
  381. 'CRM DATA OPERASYON',
  382. 'CRM INCIDENT',
  383. 'CRM INHOUSE',
  384. 'CRM KONFIGURASYON',
  385. 'CRM Konfigürasyon',
  386. 'CRM NETYUZ OIM CTS SUPPORT',
  387. 'CRM OPERASYON',
  388. 'Data Operasyon',
  389. 'IYS Destek PM',
  390. 'Servis Operasyon',
  391. 'CSS'))) b
  392. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  393. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  394. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  395. FROM (SELECT CLOSE_YEAR,
  396. MONTH,
  397. INCIDENT_ID,
  398. SURE
  399. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  400. CLOSE_YEAR,
  401. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  402. B.cid INCIDENT_ID,
  403. SUM (B.SURE) SURE
  404. FROM (SELECT TRUNC (RESOLVED_TIME)
  405. AS CLOSE_TIME,
  406. a.INCIDENT_ID,
  407. A."NUMBER" cid,
  408. a.SURE
  409. FROM (SELECT A.CLOSE_TIME
  410. RESOLVED_TIME,
  411. A.INCIDENT_ID,
  412. A."NUMBER",
  413. A.AFFECTED_ITEM,
  414. a.tt_tech,
  415. A.PROBLEM_STATUS,
  416. C.SCHEDULE,
  417. product_type,
  418. OPEN_TIME,
  419. a.resolved_group,
  420. SUBSTR (
  421. c.NAME,
  422. INSTR (
  423. c.NAME,
  424. ' Assignment:'
  425. )
  426. + 12,
  427. INSTR (
  428. c.NAME,
  429. 'Assignee:'
  430. )
  431. - INSTR (
  432. c.NAME,
  433. ' Assignment:'
  434. )
  435. - 13
  436. )
  437. ASSIGNMENT,
  438. SUBSTR (
  439. c.NAME,
  440. INSTR (
  441. c.NAME,
  442. 'Status:'
  443. )
  444. + 7,
  445. INSTR (
  446. c.NAME,
  447. 'ParentAssignment:'
  448. )
  449. - INSTR (
  450. c.NAME,
  451. 'Status:'
  452. )
  453. - 8
  454. )
  455. STATU,
  456. SUBSTR (
  457. c.NAME,
  458. INSTR (
  459. c.NAME,
  460. ' Assignee:'
  461. )
  462. + 10,
  463. LENGTH (c.NAME)
  464. - INSTR (
  465. c.NAME,
  466. 'Assignee:'
  467. )
  468. - 1
  469. )
  470. ASSIGNEE,
  471. c.name,
  472. NVL (
  473. b.FULL_NAME,
  474. 'HAVUZDA BEKLEME'
  475. )
  476. FULL_NAME,
  477. ROUND (
  478. (c.TOTAL
  479. - TO_DATE('01.01.4000'))
  480. * 24,
  481. 2
  482. )
  483. SURE
  484. FROM SMKONS.PROBSUMMARYM1 a
  485. JOIN
  486. SMKONS.CLOCKSM1 c
  487. ON c.KEY_CHAR =
  488. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  489. LEFT JOIN
  490. SMKONS.OPERATORM1 b
  491. ON B.NAME =
  492. SUBSTR (
  493. c.NAME,
  494. INSTR (
  495. c.NAME,
  496. ' Assignee:'
  497. )
  498. + 10,
  499. LENGTH (
  500. c.NAME
  501. )
  502. - INSTR (
  503. c.NAME,
  504. 'Assignee:'
  505. )
  506. - 1
  507. )
  508. WHERE A.PROBLEM_STATUS =
  509. 'Closed'
  510. AND TO_CHAR (
  511. a.CLOSE_TIME,
  512. 'YYYY'
  513. ) = 2017
  514. AND TO_CHAR (
  515. (a.CLOSE_TIME),
  516. 'MM'
  517. ) = 05
  518. AND c.SCHEDULE =
  519. '24x7'
  520. AND c.name LIKE
  521. 'Composite 24x7 %')
  522. a
  523. WHERE a.STATU NOT IN
  524. ('Resolved', 'Closed')
  525. AND a.STATU IN
  526. ('Open',
  527. 'Work In Progress',
  528. 'Vendor Resolved',
  529. 'Assign',
  530. 'Problem Closed',
  531. 'Pending Vendor',
  532. 'Pending Vendor Update',
  533. 'Pending Other',
  534. 'Replaced Problem',
  535. 'Re-Opened',
  536. 'Other Group Resolved',
  537. 'Change Completed')
  538. AND a.SCHEDULE = '24x7'
  539. AND a.affected_item NOT IN
  540. 'Gelir Güvencesi'
  541. AND a.tt_tech = 'Broadband'
  542. AND a.ASSIGNMENT IN
  543. (SELECT name
  544. FROM SMKONS.assignmentm1
  545. WHERE name IN
  546. ('BAS',
  547. 'CRM DATA OPERASYON',
  548. 'CRM INCIDENT',
  549. 'CRM INHOUSE',
  550. 'CRM KONFIGURASYON',
  551. 'CRM Konfigürasyon',
  552. 'CRM NETYUZ OIM CTS SUPPORT',
  553. 'CRM OPERASYON',
  554. 'Data Operasyon',
  555. 'IYS Destek PM',
  556. 'Servis Operasyon',
  557. 'CSS')))
  558. b
  559. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  560. TO_CHAR ( (CLOSE_TIME), 'MM'),
  561. B.cid)
  562. WHERE SURE > 4)
  563. GROUP BY CLOSE_YEAR, MONTH) B
  564. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
  565. UNION ALL
  566. SELECT 'Advanced IM Closure' AS "Id", 'SLA_NOK' AS "Description", b.case_sayisi
  567. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  568. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  569. COUNT (DISTINCT b.cid) case_sayisi,
  570. SUM (B.SURE) SURE
  571. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  572. a.INCIDENT_ID,
  573. A."NUMBER" cid,
  574. a.SURE
  575. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  576. A.INCIDENT_ID,
  577. A."NUMBER",
  578. A.AFFECTED_ITEM,
  579. a.tt_tech,
  580. A.PROBLEM_STATUS,
  581. C.SCHEDULE,
  582. product_type,
  583. OPEN_TIME,
  584. a.resolved_group,
  585. SUBSTR (
  586. c.NAME,
  587. INSTR (c.NAME, ' Assignment:') + 12,
  588. INSTR (c.NAME, 'Assignee:')
  589. - INSTR (c.NAME, ' Assignment:')
  590. - 13
  591. )
  592. ASSIGNMENT,
  593. SUBSTR (
  594. c.NAME,
  595. INSTR (c.NAME, 'Status:') + 7,
  596. INSTR (c.NAME, 'ParentAssignment:')
  597. - INSTR (c.NAME, 'Status:')
  598. - 8
  599. )
  600. STATU,
  601. SUBSTR (
  602. c.NAME,
  603. INSTR (c.NAME, ' Assignee:') + 10,
  604. LENGTH (c.NAME)
  605. - INSTR (c.NAME, 'Assignee:')
  606. - 1
  607. )
  608. ASSIGNEE,
  609. c.name,
  610. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  611. FULL_NAME,
  612. ROUND (
  613. (c.TOTAL - TO_DATE ('01.01.4000'))
  614. * 24,
  615. 2
  616. )
  617. SURE
  618. FROM SMKONS.PROBSUMMARYM1 a
  619. JOIN
  620. SMKONS.CLOCKSM1 c
  621. ON c.KEY_CHAR = a."NUMBER" --AND C.KEY_CHAR='SD10527'
  622. LEFT JOIN
  623. SMKONS.OPERATORM1 b
  624. ON B.NAME =
  625. SUBSTR (
  626. c.NAME,
  627. INSTR (c.NAME, ' Assignee:')
  628. + 10,
  629. LENGTH (c.NAME)
  630. - INSTR (c.NAME, 'Assignee:')
  631. - 1
  632. )
  633. WHERE A.PROBLEM_STATUS = 'Closed'
  634. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  635. 2017
  636. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  637. AND c.SCHEDULE = '24x7'
  638. AND c.name LIKE 'Composite 24x7 %') a
  639. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  640. AND a.STATU IN
  641. ('Open',
  642. 'Work In Progress',
  643. 'Vendor Resolved',
  644. 'Assign',
  645. 'Problem Closed',
  646. 'Pending Vendor',
  647. 'Pending Vendor Update',
  648. 'Pending Other',
  649. 'Replaced Problem',
  650. 'Re-Opened',
  651. 'Other Group Resolved',
  652. 'Change Completed')
  653. AND a.SCHEDULE = '24x7'
  654. AND a.affected_item NOT IN 'Gelir Güvencesi'
  655. AND a.tt_tech = 'Broadband'
  656. AND a.ASSIGNMENT IN
  657. (SELECT name
  658. FROM SMKONS.assignmentm1
  659. WHERE name IN
  660. ('BAS',
  661. 'CRM DATA OPERASYON',
  662. 'CRM INCIDENT',
  663. 'CRM INHOUSE',
  664. 'CRM KONFIGURASYON',
  665. 'CRM Konfigürasyon',
  666. 'CRM NETYUZ OIM CTS SUPPORT',
  667. 'CRM OPERASYON',
  668. 'Data Operasyon',
  669. 'IYS Destek PM',
  670. 'Servis Operasyon',
  671. 'CSS'))) b
  672. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  673. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  674. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  675. FROM (SELECT CLOSE_YEAR,
  676. MONTH,
  677. INCIDENT_ID,
  678. SURE
  679. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  680. CLOSE_YEAR,
  681. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  682. B.cid INCIDENT_ID,
  683. SUM (B.SURE) SURE
  684. FROM (SELECT TRUNC (RESOLVED_TIME)
  685. AS CLOSE_TIME,
  686. a.INCIDENT_ID,
  687. A."NUMBER" cid,
  688. a.SURE
  689. FROM (SELECT A.CLOSE_TIME
  690. RESOLVED_TIME,
  691. A.INCIDENT_ID,
  692. A."NUMBER",
  693. A.AFFECTED_ITEM,
  694. a.tt_tech,
  695. A.PROBLEM_STATUS,
  696. C.SCHEDULE,
  697. product_type,
  698. OPEN_TIME,
  699. a.resolved_group,
  700. SUBSTR (
  701. c.NAME,
  702. INSTR (
  703. c.NAME,
  704. ' Assignment:'
  705. )
  706. + 12,
  707. INSTR (
  708. c.NAME,
  709. 'Assignee:'
  710. )
  711. - INSTR (
  712. c.NAME,
  713. ' Assignment:'
  714. )
  715. - 13
  716. )
  717. ASSIGNMENT,
  718. SUBSTR (
  719. c.NAME,
  720. INSTR (
  721. c.NAME,
  722. 'Status:'
  723. )
  724. + 7,
  725. INSTR (
  726. c.NAME,
  727. 'ParentAssignment:'
  728. )
  729. - INSTR (
  730. c.NAME,
  731. 'Status:'
  732. )
  733. - 8
  734. )
  735. STATU,
  736. SUBSTR (
  737. c.NAME,
  738. INSTR (
  739. c.NAME,
  740. ' Assignee:'
  741. )
  742. + 10,
  743. LENGTH (c.NAME)
  744. - INSTR (
  745. c.NAME,
  746. 'Assignee:'
  747. )
  748. - 1
  749. )
  750. ASSIGNEE,
  751. c.name,
  752. NVL (
  753. b.FULL_NAME,
  754. 'HAVUZDA BEKLEME'
  755. )
  756. FULL_NAME,
  757. ROUND (
  758. (c.TOTAL
  759. - TO_DATE('01.01.4000'))
  760. * 24,
  761. 2
  762. )
  763. SURE
  764. FROM SMKONS.PROBSUMMARYM1 a
  765. JOIN
  766. SMKONS.CLOCKSM1 c
  767. ON c.KEY_CHAR =
  768. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  769. LEFT JOIN
  770. SMKONS.OPERATORM1 b
  771. ON B.NAME =
  772. SUBSTR (
  773. c.NAME,
  774. INSTR (
  775. c.NAME,
  776. ' Assignee:'
  777. )
  778. + 10,
  779. LENGTH (
  780. c.NAME
  781. )
  782. - INSTR (
  783. c.NAME,
  784. 'Assignee:'
  785. )
  786. - 1
  787. )
  788. WHERE A.PROBLEM_STATUS =
  789. 'Closed'
  790. AND TO_CHAR (
  791. a.CLOSE_TIME,
  792. 'YYYY'
  793. ) = 2017
  794. AND TO_CHAR (
  795. (a.CLOSE_TIME),
  796. 'MM'
  797. ) = 05
  798. AND c.SCHEDULE =
  799. '24x7'
  800. AND c.name LIKE
  801. 'Composite 24x7 %')
  802. a
  803. WHERE a.STATU NOT IN
  804. ('Resolved', 'Closed')
  805. AND a.STATU IN
  806. ('Open',
  807. 'Work In Progress',
  808. 'Vendor Resolved',
  809. 'Assign',
  810. 'Problem Closed',
  811. 'Pending Vendor',
  812. 'Pending Vendor Update',
  813. 'Pending Other',
  814. 'Replaced Problem',
  815. 'Re-Opened',
  816. 'Other Group Resolved',
  817. 'Change Completed')
  818. AND a.SCHEDULE = '24x7'
  819. AND a.affected_item NOT IN
  820. 'Gelir Güvencesi'
  821. AND a.tt_tech = 'Broadband'
  822. AND a.ASSIGNMENT IN
  823. (SELECT name
  824. FROM SMKONS.assignmentm1
  825. WHERE name IN
  826. ('BAS',
  827. 'CRM DATA OPERASYON',
  828. 'CRM INCIDENT',
  829. 'CRM INHOUSE',
  830. 'CRM KONFIGURASYON',
  831. 'CRM Konfigürasyon',
  832. 'CRM NETYUZ OIM CTS SUPPORT',
  833. 'CRM OPERASYON',
  834. 'Data Operasyon',
  835. 'IYS Destek PM',
  836. 'Servis Operasyon',
  837. 'CSS')))
  838. b
  839. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  840. TO_CHAR ( (CLOSE_TIME), 'MM'),
  841. B.cid)
  842. WHERE SURE > 4)
  843. GROUP BY CLOSE_YEAR, MONTH) B
  844. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
  845. union all
  846. SELECT 'Fixed' AS "Id",
  847. 'Advanced IM Closure' AS "Description",
  848. ROUND ( (a.case_sayisi - b.case_sayisi) / a.case_sayisi * 100, 2)
  849. AS "Oran"
  850. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  851. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  852. COUNT (DISTINCT b.cid) case_sayisi,
  853. SUM (B.SURE) SURE
  854. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  855. a.INCIDENT_ID,
  856. A."NUMBER" cid,
  857. a.SURE
  858. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  859. A.INCIDENT_ID,
  860. A."NUMBER",
  861. A.AFFECTED_ITEM,
  862. a.tt_tech,
  863. A.PROBLEM_STATUS,
  864. C.SCHEDULE,
  865. product_type,
  866. OPEN_TIME,
  867. a.resolved_group,
  868. SUBSTR (
  869. c.NAME,
  870. INSTR (c.NAME, ' Assignment:') + 12,
  871. INSTR (c.NAME, 'Assignee:')
  872. - INSTR (c.NAME, ' Assignment:')
  873. - 13
  874. )
  875. ASSIGNMENT,
  876. SUBSTR (
  877. c.NAME,
  878. INSTR (c.NAME, 'Status:') + 7,
  879. INSTR (c.NAME, 'ParentAssignment:')
  880. - INSTR (c.NAME, 'Status:')
  881. - 8
  882. )
  883. STATU,
  884. SUBSTR (
  885. c.NAME,
  886. INSTR (c.NAME, ' Assignee:') + 10,
  887. LENGTH (c.NAME)
  888. - INSTR (c.NAME, 'Assignee:')
  889. - 1
  890. )
  891. ASSIGNEE,
  892. c.name,
  893. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  894. FULL_NAME,
  895. ROUND (
  896. (c.TOTAL - TO_DATE ('01.01.4000'))
  897. * 24,
  898. 2
  899. )
  900. SURE
  901. FROM SMKONS.PROBSUMMARYM1 a
  902. JOIN
  903. SMKONS.CLOCKSM1 c
  904. ON c.KEY_CHAR = a."NUMBER"
  905. LEFT JOIN
  906. SMKONS.OPERATORM1 b
  907. ON B.NAME =
  908. SUBSTR (
  909. c.NAME,
  910. INSTR (c.NAME, ' Assignee:')
  911. + 10,
  912. LENGTH (c.NAME)
  913. - INSTR (c.NAME, 'Assignee:')
  914. - 1
  915. )
  916. WHERE A.PROBLEM_STATUS = 'Closed'
  917. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  918. 2017
  919. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  920. AND c.SCHEDULE = '24x7'
  921. AND c.name LIKE 'Composite 24x7 %') a
  922. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  923. AND a.STATU IN
  924. ('Open',
  925. 'Work In Progress',
  926. 'Vendor Resolved',
  927. 'Assign',
  928. 'Problem Closed',
  929. 'Pending Vendor',
  930. 'Pending Vendor Update',
  931. 'Pending Other',
  932. 'Replaced Problem',
  933. 'Re-Opened',
  934. 'Other Group Resolved',
  935. 'Change Completed')
  936. AND a.SCHEDULE = '24x7'
  937. AND a.affected_item NOT IN 'Gelir Güvencesi'
  938. AND a.tt_tech = 'Fix'
  939. AND a.ASSIGNMENT IN
  940. (SELECT name
  941. FROM SMKONS.assignmentm1
  942. WHERE name IN
  943. ('SOM Uygulama',
  944. 'TMS Destek PM',
  945. 'Mobil OHM Destek PM',
  946. 'Kurumsal OHM Destek PM',
  947. 'Bireysel OHM PM',
  948. 'ARECA-PCOM',
  949. 'ATS EOE',
  950. 'BGW EOE',
  951. 'CEBILL EOE',
  952. 'DASETS EOE',
  953. 'DHS EOE',
  954. 'FLEXIBILL EOE',
  955. 'FMS EOE',
  956. 'SMARTBILL EOE',
  957. 'TMS EOE',
  958. 'TTO EOE',
  959. 'TTS EOE',
  960. 'xDSL EOE',
  961. 'Olo Portal Destek PM',
  962. 'Dasets Destek PM',
  963.  
  964. 'TTVM Portal Destek PM',
  965. 'KMZP Destek PM',
  966. 'Telaura Uygulama',
  967. 'OCSP Destek PM',
  968. 'CW Uygulama',
  969. 'aTTIP Uygulama',
  970. 'Siebel Uygulama'))) b
  971. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  972. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  973. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  974. FROM (SELECT CLOSE_YEAR,
  975. MONTH,
  976. INCIDENT_ID,
  977. SURE
  978. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  979. CLOSE_YEAR,
  980. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  981. B.cid INCIDENT_ID,
  982. SUM (B.SURE) SURE
  983. FROM (SELECT TRUNC (RESOLVED_TIME)
  984. AS CLOSE_TIME,
  985. a.INCIDENT_ID,
  986. A."NUMBER" cid,
  987. a.SURE
  988. FROM (SELECT A.CLOSE_TIME
  989. RESOLVED_TIME,
  990. A.INCIDENT_ID,
  991. A."NUMBER",
  992. A.AFFECTED_ITEM,
  993. a.tt_tech,
  994. A.PROBLEM_STATUS,
  995. C.SCHEDULE,
  996. product_type,
  997. OPEN_TIME,
  998. a.resolved_group,
  999. SUBSTR (
  1000. c.NAME,
  1001. INSTR (
  1002. c.NAME,
  1003. ' Assignment:'
  1004. )
  1005. + 12,
  1006. INSTR (
  1007. c.NAME,
  1008. 'Assignee:'
  1009. )
  1010. - INSTR (
  1011. c.NAME,
  1012. ' Assignment:'
  1013. )
  1014. - 13
  1015. )
  1016. ASSIGNMENT,
  1017. SUBSTR (
  1018. c.NAME,
  1019. INSTR (
  1020. c.NAME,
  1021. 'Status:'
  1022. )
  1023. + 7,
  1024. INSTR (
  1025. c.NAME,
  1026. 'ParentAssignment:'
  1027. )
  1028. - INSTR (
  1029. c.NAME,
  1030. 'Status:'
  1031. )
  1032. - 8
  1033. )
  1034. STATU,
  1035. SUBSTR (
  1036. c.NAME,
  1037. INSTR (
  1038. c.NAME,
  1039. ' Assignee:'
  1040. )
  1041. + 10,
  1042. LENGTH (c.NAME)
  1043. - INSTR (
  1044. c.NAME,
  1045. 'Assignee:'
  1046. )
  1047. - 1
  1048. )
  1049. ASSIGNEE,
  1050. c.name,
  1051. NVL (
  1052. b.FULL_NAME,
  1053. 'HAVUZDA BEKLEME'
  1054. )
  1055. FULL_NAME,
  1056. ROUND (
  1057. (c.TOTAL
  1058. - TO_DATE('01.01.4000'))
  1059. * 24,
  1060. 2
  1061. )
  1062. SURE
  1063. FROM SMKONS.PROBSUMMARYM1 a
  1064. JOIN
  1065. SMKONS.CLOCKSM1 c
  1066. ON c.KEY_CHAR =
  1067. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  1068. LEFT JOIN
  1069. SMKONS.OPERATORM1 b
  1070. ON B.NAME =
  1071. SUBSTR (
  1072. c.NAME,
  1073. INSTR (
  1074. c.NAME,
  1075. ' Assignee:'
  1076. )
  1077. + 10,
  1078. LENGTH (
  1079. c.NAME
  1080. )
  1081. - INSTR (
  1082. c.NAME,
  1083. 'Assignee:'
  1084. )
  1085. - 1
  1086. )
  1087. WHERE A.PROBLEM_STATUS =
  1088. 'Closed'
  1089. AND TO_CHAR (
  1090. a.CLOSE_TIME,
  1091. 'YYYY'
  1092. ) = 2017
  1093. AND TO_CHAR (
  1094. (a.CLOSE_TIME),
  1095. 'MM'
  1096. ) = 05
  1097. AND c.SCHEDULE =
  1098. '24x7'
  1099. AND c.name LIKE
  1100. 'Composite 24x7 %')
  1101. a
  1102. WHERE a.STATU NOT IN
  1103. ('Resolved', 'Closed')
  1104. AND a.STATU IN
  1105. ('Open',
  1106. 'Work In Progress',
  1107. 'Vendor Resolved',
  1108. 'Assign',
  1109. 'Problem Closed',
  1110. 'Pending Vendor',
  1111. 'Pending Vendor Update',
  1112. 'Pending Other',
  1113. 'Replaced Problem',
  1114. 'Re-Opened',
  1115. 'Other Group Resolved',
  1116. 'Change Completed')
  1117. AND a.SCHEDULE = '24x7'
  1118. AND a.affected_item NOT IN
  1119. 'Gelir Güvencesi'
  1120. AND a.tt_tech = 'Fix'
  1121. AND a.ASSIGNMENT IN
  1122. (SELECT name
  1123. FROM SMKONS.assignmentm1
  1124. WHERE name IN
  1125. ('SOM Uygulama',
  1126. 'TMS Destek PM',
  1127. 'Mobil OHM Destek PM',
  1128. 'Kurumsal OHM Destek PM',
  1129. 'Bireysel OHM PM',
  1130. 'ARECA-PCOM',
  1131. 'ATS EOE',
  1132. 'BGW EOE',
  1133. 'CEBILL EOE',
  1134. 'DASETS EOE',
  1135. 'DHS EOE',
  1136. 'FLEXIBILL EOE',
  1137. 'FMS EOE',
  1138. 'SMARTBILL EOE',
  1139. 'TMS EOE',
  1140. 'TTO EOE',
  1141. 'TTS EOE',
  1142. 'xDSL EOE',
  1143. 'Olo Portal Destek PM',
  1144. 'Dasets Destek PM',
  1145.  
  1146. 'TTVM Portal Destek PM',
  1147. 'KMZP Destek PM',
  1148. 'Telaura Uygulama',
  1149. 'OCSP Destek PM',
  1150. 'CW Uygulama',
  1151. 'aTTIP Uygulama',
  1152. 'Siebel Uygulama')))
  1153. b
  1154. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  1155. TO_CHAR ( (CLOSE_TIME), 'MM'),
  1156. B.cid)
  1157. WHERE SURE > 4)
  1158. GROUP BY CLOSE_YEAR, MONTH) B
  1159. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
  1160. UNION ALL
  1161. SELECT 'Advanced IM Closure' AS "Id",
  1162. 'SLA_OK' AS "Description",
  1163. a.case_sayisi - b.case_sayisi
  1164. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  1165. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  1166. COUNT (DISTINCT b.cid) case_sayisi,
  1167. SUM (B.SURE) SURE
  1168. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  1169. a.INCIDENT_ID,
  1170. A."NUMBER" cid,
  1171. a.SURE
  1172. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  1173. A.INCIDENT_ID,
  1174. A."NUMBER",
  1175. A.AFFECTED_ITEM,
  1176. a.tt_tech,
  1177. A.PROBLEM_STATUS,
  1178. C.SCHEDULE,
  1179. product_type,
  1180. OPEN_TIME,
  1181. a.resolved_group,
  1182. SUBSTR (
  1183. c.NAME,
  1184. INSTR (c.NAME, ' Assignment:') + 12,
  1185. INSTR (c.NAME, 'Assignee:')
  1186. - INSTR (c.NAME, ' Assignment:')
  1187. - 13
  1188. )
  1189. ASSIGNMENT,
  1190. SUBSTR (
  1191. c.NAME,
  1192. INSTR (c.NAME, 'Status:') + 7,
  1193. INSTR (c.NAME, 'ParentAssignment:')
  1194. - INSTR (c.NAME, 'Status:')
  1195. - 8
  1196. )
  1197. STATU,
  1198. SUBSTR (
  1199. c.NAME,
  1200. INSTR (c.NAME, ' Assignee:') + 10,
  1201. LENGTH (c.NAME)
  1202. - INSTR (c.NAME, 'Assignee:')
  1203. - 1
  1204. )
  1205. ASSIGNEE,
  1206. c.name,
  1207. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  1208. FULL_NAME,
  1209. ROUND (
  1210. (c.TOTAL - TO_DATE ('01.01.4000'))
  1211. * 24,
  1212. 2
  1213. )
  1214. SURE
  1215. FROM SMKONS.PROBSUMMARYM1 a
  1216. JOIN
  1217. SMKONS.CLOCKSM1 c
  1218. ON c.KEY_CHAR = a."NUMBER" --AND C.KEY_CHAR='SD10527'
  1219. LEFT JOIN
  1220. SMKONS.OPERATORM1 b
  1221. ON B.NAME =
  1222. SUBSTR (
  1223. c.NAME,
  1224. INSTR (c.NAME, ' Assignee:')
  1225. + 10,
  1226. LENGTH (c.NAME)
  1227. - INSTR (c.NAME, 'Assignee:')
  1228. - 1
  1229. )
  1230. WHERE A.PROBLEM_STATUS = 'Closed'
  1231. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  1232. 2017
  1233. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  1234. AND c.SCHEDULE = '24x7'
  1235. AND c.name LIKE 'Composite 24x7 %') a
  1236. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  1237. AND a.STATU IN
  1238. ('Open',
  1239. 'Work In Progress',
  1240. 'Vendor Resolved',
  1241. 'Assign',
  1242. 'Problem Closed',
  1243. 'Pending Vendor',
  1244. 'Pending Vendor Update',
  1245. 'Pending Other',
  1246. 'Replaced Problem',
  1247. 'Re-Opened',
  1248. 'Other Group Resolved',
  1249. 'Change Completed')
  1250. AND a.SCHEDULE = '24x7'
  1251. AND a.affected_item NOT IN 'Gelir Güvencesi'
  1252. AND a.tt_tech = 'Fix'
  1253. AND a.ASSIGNMENT IN
  1254. (SELECT name
  1255. FROM SMKONS.assignmentm1
  1256. WHERE name IN
  1257. ('SOM Uygulama',
  1258. 'TMS Destek PM',
  1259. 'Mobil OHM Destek PM',
  1260. 'Kurumsal OHM Destek PM',
  1261. 'Bireysel OHM PM',
  1262. 'ARECA-PCOM',
  1263. 'ATS EOE',
  1264. 'BGW EOE',
  1265. 'CEBILL EOE',
  1266. 'DASETS EOE',
  1267. 'DHS EOE',
  1268. 'FLEXIBILL EOE',
  1269. 'FMS EOE',
  1270. 'SMARTBILL EOE',
  1271. 'TMS EOE',
  1272. 'TTO EOE',
  1273. 'TTS EOE',
  1274. 'xDSL EOE',
  1275. 'Olo Portal Destek PM',
  1276. 'Dasets Destek PM',
  1277.  
  1278. 'TTVM Portal Destek PM',
  1279. 'KMZP Destek PM',
  1280. 'Telaura Uygulama',
  1281. 'OCSP Destek PM',
  1282. 'CW Uygulama',
  1283. 'aTTIP Uygulama',
  1284. 'Siebel Uygulama'))) b
  1285. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  1286. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  1287. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  1288. FROM (SELECT CLOSE_YEAR,
  1289. MONTH,
  1290. INCIDENT_ID,
  1291. SURE
  1292. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  1293. CLOSE_YEAR,
  1294. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  1295. B.cid INCIDENT_ID,
  1296. SUM (B.SURE) SURE
  1297. FROM (SELECT TRUNC (RESOLVED_TIME)
  1298. AS CLOSE_TIME,
  1299. a.INCIDENT_ID,
  1300. A."NUMBER" cid,
  1301. a.SURE
  1302. FROM (SELECT A.CLOSE_TIME
  1303. RESOLVED_TIME,
  1304. A.INCIDENT_ID,
  1305. A."NUMBER",
  1306. A.AFFECTED_ITEM,
  1307. a.tt_tech,
  1308. A.PROBLEM_STATUS,
  1309. C.SCHEDULE,
  1310. product_type,
  1311. OPEN_TIME,
  1312. a.resolved_group,
  1313. SUBSTR (
  1314. c.NAME,
  1315. INSTR (
  1316. c.NAME,
  1317. ' Assignment:'
  1318. )
  1319. + 12,
  1320. INSTR (
  1321. c.NAME,
  1322. 'Assignee:'
  1323. )
  1324. - INSTR (
  1325. c.NAME,
  1326. ' Assignment:'
  1327. )
  1328. - 13
  1329. )
  1330. ASSIGNMENT,
  1331. SUBSTR (
  1332. c.NAME,
  1333. INSTR (
  1334. c.NAME,
  1335. 'Status:'
  1336. )
  1337. + 7,
  1338. INSTR (
  1339. c.NAME,
  1340. 'ParentAssignment:'
  1341. )
  1342. - INSTR (
  1343. c.NAME,
  1344. 'Status:'
  1345. )
  1346. - 8
  1347. )
  1348. STATU,
  1349. SUBSTR (
  1350. c.NAME,
  1351. INSTR (
  1352. c.NAME,
  1353. ' Assignee:'
  1354. )
  1355. + 10,
  1356. LENGTH (c.NAME)
  1357. - INSTR (
  1358. c.NAME,
  1359. 'Assignee:'
  1360. )
  1361. - 1
  1362. )
  1363. ASSIGNEE,
  1364. c.name,
  1365. NVL (
  1366. b.FULL_NAME,
  1367. 'HAVUZDA BEKLEME'
  1368. )
  1369. FULL_NAME,
  1370. ROUND (
  1371. (c.TOTAL
  1372. - TO_DATE('01.01.4000'))
  1373. * 24,
  1374. 2
  1375. )
  1376. SURE
  1377. FROM SMKONS.PROBSUMMARYM1 a
  1378. JOIN
  1379. SMKONS.CLOCKSM1 c
  1380. ON c.KEY_CHAR =
  1381. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  1382. LEFT JOIN
  1383. SMKONS.OPERATORM1 b
  1384. ON B.NAME =
  1385. SUBSTR (
  1386. c.NAME,
  1387. INSTR (
  1388. c.NAME,
  1389. ' Assignee:'
  1390. )
  1391. + 10,
  1392. LENGTH (
  1393. c.NAME
  1394. )
  1395. - INSTR (
  1396. c.NAME,
  1397. 'Assignee:'
  1398. )
  1399. - 1
  1400. )
  1401. WHERE A.PROBLEM_STATUS =
  1402. 'Closed'
  1403. AND TO_CHAR (
  1404. a.CLOSE_TIME,
  1405. 'YYYY'
  1406. ) = 2017
  1407. AND TO_CHAR (
  1408. (a.CLOSE_TIME),
  1409. 'MM'
  1410. ) = 05
  1411. AND c.SCHEDULE =
  1412. '24x7'
  1413. AND c.name LIKE
  1414. 'Composite 24x7 %')
  1415. a
  1416. WHERE a.STATU NOT IN
  1417. ('Resolved', 'Closed')
  1418. AND a.STATU IN
  1419. ('Open',
  1420. 'Work In Progress',
  1421. 'Vendor Resolved',
  1422. 'Assign',
  1423. 'Problem Closed',
  1424. 'Pending Vendor',
  1425. 'Pending Vendor Update',
  1426. 'Pending Other',
  1427. 'Replaced Problem',
  1428. 'Re-Opened',
  1429. 'Other Group Resolved',
  1430. 'Change Completed')
  1431. AND a.SCHEDULE = '24x7'
  1432. AND a.affected_item NOT IN
  1433. 'Gelir Güvencesi'
  1434. AND a.tt_tech = 'Fix'
  1435. AND a.ASSIGNMENT IN
  1436. (SELECT name
  1437. FROM SMKONS.assignmentm1
  1438. WHERE name IN
  1439. ('SOM Uygulama',
  1440. 'TMS Destek PM',
  1441. 'Mobil OHM Destek PM',
  1442. 'Kurumsal OHM Destek PM',
  1443. 'Bireysel OHM PM',
  1444. 'ARECA-PCOM',
  1445. 'ATS EOE',
  1446. 'BGW EOE',
  1447. 'CEBILL EOE',
  1448. 'DASETS EOE',
  1449. 'DHS EOE',
  1450. 'FLEXIBILL EOE',
  1451. 'FMS EOE',
  1452. 'SMARTBILL EOE',
  1453. 'TMS EOE',
  1454. 'TTO EOE',
  1455. 'TTS EOE',
  1456. 'xDSL EOE',
  1457. 'Olo Portal Destek PM',
  1458. 'Dasets Destek PM',
  1459.  
  1460. 'TTVM Portal Destek PM',
  1461. 'KMZP Destek PM',
  1462. 'Telaura Uygulama',
  1463. 'OCSP Destek PM',
  1464. 'CW Uygulama',
  1465. 'aTTIP Uygulama',
  1466. 'Siebel Uygulama')))
  1467. b
  1468. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  1469. TO_CHAR ( (CLOSE_TIME), 'MM'),
  1470. B.cid)
  1471. WHERE SURE > 4)
  1472. GROUP BY CLOSE_YEAR, MONTH) B
  1473. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
  1474. UNION ALL
  1475. SELECT 'Advanced IM Closure' AS "Id", 'SLA_NOK' AS "Description", b.case_sayisi
  1476. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY') CLOSE_YEAR,
  1477. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  1478. COUNT (DISTINCT b.cid) case_sayisi,
  1479. SUM (B.SURE) SURE
  1480. FROM (SELECT TRUNC (RESOLVED_TIME) AS CLOSE_TIME,
  1481. a.INCIDENT_ID,
  1482. A."NUMBER" cid,
  1483. a.SURE
  1484. FROM (SELECT A.CLOSE_TIME RESOLVED_TIME,
  1485. A.INCIDENT_ID,
  1486. A."NUMBER",
  1487. A.AFFECTED_ITEM,
  1488. a.tt_tech,
  1489. A.PROBLEM_STATUS,
  1490. C.SCHEDULE,
  1491. product_type,
  1492. OPEN_TIME,
  1493. a.resolved_group,
  1494. SUBSTR (
  1495. c.NAME,
  1496. INSTR (c.NAME, ' Assignment:') + 12,
  1497. INSTR (c.NAME, 'Assignee:')
  1498. - INSTR (c.NAME, ' Assignment:')
  1499. - 13
  1500. )
  1501. ASSIGNMENT,
  1502. SUBSTR (
  1503. c.NAME,
  1504. INSTR (c.NAME, 'Status:') + 7,
  1505. INSTR (c.NAME, 'ParentAssignment:')
  1506. - INSTR (c.NAME, 'Status:')
  1507. - 8
  1508. )
  1509. STATU,
  1510. SUBSTR (
  1511. c.NAME,
  1512. INSTR (c.NAME, ' Assignee:') + 10,
  1513. LENGTH (c.NAME)
  1514. - INSTR (c.NAME, 'Assignee:')
  1515. - 1
  1516. )
  1517. ASSIGNEE,
  1518. c.name,
  1519. NVL (b.FULL_NAME, 'HAVUZDA BEKLEME')
  1520. FULL_NAME,
  1521. ROUND (
  1522. (c.TOTAL - TO_DATE ('01.01.4000'))
  1523. * 24,
  1524. 2
  1525. )
  1526. SURE
  1527. FROM SMKONS.PROBSUMMARYM1 a
  1528. JOIN
  1529. SMKONS.CLOCKSM1 c
  1530. ON c.KEY_CHAR = a."NUMBER" --AND C.KEY_CHAR='SD10527'
  1531. LEFT JOIN
  1532. SMKONS.OPERATORM1 b
  1533. ON B.NAME =
  1534. SUBSTR (
  1535. c.NAME,
  1536. INSTR (c.NAME, ' Assignee:')
  1537. + 10,
  1538. LENGTH (c.NAME)
  1539. - INSTR (c.NAME, 'Assignee:')
  1540. - 1
  1541. )
  1542. WHERE A.PROBLEM_STATUS = 'Closed'
  1543. AND TO_CHAR (a.CLOSE_TIME, 'YYYY') =
  1544. 2017
  1545. AND TO_CHAR ( (a.CLOSE_TIME), 'MM') = 05
  1546. AND c.SCHEDULE = '24x7'
  1547. AND c.name LIKE 'Composite 24x7 %') a
  1548. WHERE a.STATU NOT IN ('Resolved', 'Closed')
  1549. AND a.STATU IN
  1550. ('Open',
  1551. 'Work In Progress',
  1552. 'Vendor Resolved',
  1553. 'Assign',
  1554. 'Problem Closed',
  1555. 'Pending Vendor',
  1556. 'Pending Vendor Update',
  1557. 'Pending Other',
  1558. 'Replaced Problem',
  1559. 'Re-Opened',
  1560. 'Other Group Resolved',
  1561. 'Change Completed')
  1562. AND a.SCHEDULE = '24x7'
  1563. AND a.affected_item NOT IN 'Gelir Güvencesi'
  1564. AND a.tt_tech = 'Fix'
  1565. AND a.ASSIGNMENT IN
  1566. (SELECT name
  1567. FROM SMKONS.assignmentm1
  1568. WHERE name IN
  1569. ('SOM Uygulama',
  1570. 'TMS Destek PM',
  1571. 'Mobil OHM Destek PM',
  1572. 'Kurumsal OHM Destek PM',
  1573. 'Bireysel OHM PM',
  1574. 'ARECA-PCOM',
  1575. 'ATS EOE',
  1576. 'BGW EOE',
  1577. 'CEBILL EOE',
  1578. 'DASETS EOE',
  1579. 'DHS EOE',
  1580. 'FLEXIBILL EOE',
  1581. 'FMS EOE',
  1582. 'SMARTBILL EOE',
  1583. 'TMS EOE',
  1584. 'TTO EOE',
  1585. 'TTS EOE',
  1586. 'xDSL EOE',
  1587. 'Olo Portal Destek PM',
  1588. 'Dasets Destek PM',
  1589.  
  1590. 'TTVM Portal Destek PM',
  1591. 'KMZP Destek PM',
  1592. 'Telaura Uygulama',
  1593. 'OCSP Destek PM',
  1594. 'CW Uygulama',
  1595. 'aTTIP Uygulama',
  1596. 'Siebel Uygulama'))) b
  1597. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  1598. TO_CHAR ( (CLOSE_TIME), 'MM')) A,
  1599. ( SELECT CLOSE_YEAR, MONTH, COUNT (INCIDENT_ID) case_sayisi
  1600. FROM (SELECT CLOSE_YEAR,
  1601. MONTH,
  1602. INCIDENT_ID,
  1603. SURE
  1604. FROM ( SELECT TO_CHAR ( (CLOSE_TIME), 'YYYY')
  1605. CLOSE_YEAR,
  1606. TO_CHAR ( (CLOSE_TIME), 'MM') MONTH,
  1607. B.cid INCIDENT_ID,
  1608. SUM (B.SURE) SURE
  1609. FROM (SELECT TRUNC (RESOLVED_TIME)
  1610. AS CLOSE_TIME,
  1611. a.INCIDENT_ID,
  1612. A."NUMBER" cid,
  1613. a.SURE
  1614. FROM (SELECT A.CLOSE_TIME
  1615. RESOLVED_TIME,
  1616. A.INCIDENT_ID,
  1617. A."NUMBER",
  1618. A.AFFECTED_ITEM,
  1619. a.tt_tech,
  1620. A.PROBLEM_STATUS,
  1621. C.SCHEDULE,
  1622. product_type,
  1623. OPEN_TIME,
  1624. a.resolved_group,
  1625. SUBSTR (
  1626. c.NAME,
  1627. INSTR (
  1628. c.NAME,
  1629. ' Assignment:'
  1630. )
  1631. + 12,
  1632. INSTR (
  1633. c.NAME,
  1634. 'Assignee:'
  1635. )
  1636. - INSTR (
  1637. c.NAME,
  1638. ' Assignment:'
  1639. )
  1640. - 13
  1641. )
  1642. ASSIGNMENT,
  1643. SUBSTR (
  1644. c.NAME,
  1645. INSTR (
  1646. c.NAME,
  1647. 'Status:'
  1648. )
  1649. + 7,
  1650. INSTR (
  1651. c.NAME,
  1652. 'ParentAssignment:'
  1653. )
  1654. - INSTR (
  1655. c.NAME,
  1656. 'Status:'
  1657. )
  1658. - 8
  1659. )
  1660. STATU,
  1661. SUBSTR (
  1662. c.NAME,
  1663. INSTR (
  1664. c.NAME,
  1665. ' Assignee:'
  1666. )
  1667. + 10,
  1668. LENGTH (c.NAME)
  1669. - INSTR (
  1670. c.NAME,
  1671. 'Assignee:'
  1672. )
  1673. - 1
  1674. )
  1675. ASSIGNEE,
  1676. c.name,
  1677. NVL (
  1678. b.FULL_NAME,
  1679. 'HAVUZDA BEKLEME'
  1680. )
  1681. FULL_NAME,
  1682. ROUND (
  1683. (c.TOTAL
  1684. - TO_DATE('01.01.4000'))
  1685. * 24,
  1686. 2
  1687. )
  1688. SURE
  1689. FROM SMKONS.PROBSUMMARYM1 a
  1690. JOIN
  1691. SMKONS.CLOCKSM1 c
  1692. ON c.KEY_CHAR =
  1693. a."NUMBER" --AND C.KEY_CHAR='SD10527'
  1694. LEFT JOIN
  1695. SMKONS.OPERATORM1 b
  1696. ON B.NAME =
  1697. SUBSTR (
  1698. c.NAME,
  1699. INSTR (
  1700. c.NAME,
  1701. ' Assignee:'
  1702. )
  1703. + 10,
  1704. LENGTH (
  1705. c.NAME
  1706. )
  1707. - INSTR (
  1708. c.NAME,
  1709. 'Assignee:'
  1710. )
  1711. - 1
  1712. )
  1713. WHERE A.PROBLEM_STATUS =
  1714. 'Closed'
  1715. AND TO_CHAR (
  1716. a.CLOSE_TIME,
  1717. 'YYYY'
  1718. ) = 2017
  1719. AND TO_CHAR (
  1720. (a.CLOSE_TIME),
  1721. 'MM'
  1722. ) = 05
  1723. AND c.SCHEDULE =
  1724. '24x7'
  1725. AND c.name LIKE
  1726. 'Composite 24x7 %')
  1727. a
  1728. WHERE a.STATU NOT IN
  1729. ('Resolved', 'Closed')
  1730. AND a.STATU IN
  1731. ('Open',
  1732. 'Work In Progress',
  1733. 'Vendor Resolved',
  1734. 'Assign',
  1735. 'Problem Closed',
  1736. 'Pending Vendor',
  1737. 'Pending Vendor Update',
  1738. 'Pending Other',
  1739. 'Replaced Problem',
  1740. 'Re-Opened',
  1741. 'Other Group Resolved',
  1742. 'Change Completed')
  1743. AND a.SCHEDULE = '24x7'
  1744. AND a.affected_item NOT IN
  1745. 'Gelir Güvencesi'
  1746. AND a.tt_tech = 'Fix'
  1747. AND a.ASSIGNMENT IN
  1748. (SELECT name
  1749. FROM SMKONS.assignmentm1
  1750. WHERE name IN
  1751. ('SOM Uygulama',
  1752. 'TMS Destek PM',
  1753. 'Mobil OHM Destek PM',
  1754. 'Kurumsal OHM Destek PM',
  1755. 'Bireysel OHM PM',
  1756. 'ARECA-PCOM',
  1757. 'ATS EOE',
  1758. 'BGW EOE',
  1759. 'CEBILL EOE',
  1760. 'DASETS EOE',
  1761. 'DHS EOE',
  1762. 'FLEXIBILL EOE',
  1763. 'FMS EOE',
  1764. 'SMARTBILL EOE',
  1765. 'TMS EOE',
  1766. 'TTO EOE',
  1767. 'TTS EOE',
  1768. 'xDSL EOE',
  1769. 'Olo Portal Destek PM',
  1770. 'Dasets Destek PM',
  1771.  
  1772. 'TTVM Portal Destek PM',
  1773. 'KMZP Destek PM',
  1774. 'Telaura Uygulama',
  1775. 'OCSP Destek PM',
  1776. 'CW Uygulama',
  1777. 'aTTIP Uygulama',
  1778. 'Siebel Uygulama')))
  1779. b
  1780. GROUP BY TO_CHAR ( (CLOSE_TIME), 'YYYY'),
  1781. TO_CHAR ( (CLOSE_TIME), 'MM'),
  1782. B.cid)
  1783. WHERE SURE > 4)
  1784. GROUP BY CLOSE_YEAR, MONTH) B
  1785. WHERE A.CLOSE_YEAR = B.CLOSE_YEAR(+) AND A.MONTH = B.MONTH(+)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!