API tools faq
paste
Guest User

Untitled

a guest
Apr 21st, 2018
66
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.77 KB | None | 0 0
raw download clone embed print report
  1. --Removed comments to protect the guilty
  2. CREATE VIEW [dbo].[vw_PPM_CompletionSummary_Resource_ID_Added]
  3.  
  4. AS
  5.  
  6.  
  7. SELECT
  8. SD.fld_str_Name AS 'Division',
  9. DM.fld_str_Name AS 'DivManager',
  10. SC.fld_str_Name AS 'Cluster',
  11. OM.fld_str_Name AS 'OpsManager',
  12. S.fld_int_ID AS 'StoreID',
  13. S.fld_str_Name AS 'Store',
  14. ST.fld_str_Description AS 'StoreType',
  15. PT.fld_str_Name AS 'PPMType',
  16. PT.fld_int_ID AS 'PPMTypeID',
  17. MT.fld_str_Name AS 'Discipline',
  18. MT.fld_int_ID AS 'DisciplineID',
  19. SL.fld_int_ID AS 'PPMRef',
  20. SL.fld_dat_WeekEnding AS 'WeekEnding',
  21. WN.WeekNumber AS 'SchedWeekNo',
  22. YEAR(SL.fld_dat_WeekEnding) AS 'SchedYear',
  23. 1 AS 'PPMDue',
  24. -- case when SL.fld_int_StatusID = 2 then 1 else 0 end as 'PPMComplete',
  25. CASE WHEN SL.fld_int_StatusID = 2 THEN 1 WHEN ProjComp.ProjComp IS NOT NULL THEN 1 ELSE 0 END AS 'PPMComplete',
  26. CASE WHEN PT.fld_int_ID IN (4,5,7,6,99,97,407,408) THEN 1 ELSE 0 END AS 'IncludeInKPI', --Bakery,Catering,HVAC Service,Refrigeration,PPM-Call Point Testing, PPM-Sprinkler Valve Weekly Test, Catering Minor, Bakery Minor
  27. CASE WHEN PT.fld_int_ResourceTypeID IN (1,2,3) THEN 1 ELSE 0 END AS 'CityConfiguredPPMType', --IST,Engineer,Alliance
  28. CASE WHEN PT.fld_int_ResourceID IN (SELECT fld_int_ID FROM Prod.dbo.tblHelpDeskResource (NOLOCK) WHERE fld_str_Name LIKE 'jungh%')
  29. THEN 1 ELSE 0 END AS 'JungheinrichConfiguredPPM',
  30. RD.CalloutEngineerID,
  31. RD.CalloutEngineer AS 'CalloutEngineer',
  32. RD.CalloutDate AS 'CalloutDate',
  33. RD.CompletedBy,
  34. CASE WHEN RD.CompleteDate IS NOT NULL THEN
  35. CASE WHEN ProjComp.ProjComp < RD.CompleteDate THEN
  36. ProjComp.ProjComp
  37. ELSE
  38. RD.CompleteDate END
  39. ELSE
  40. ProjComp.ProjComp
  41. END AS 'CompleteDate',
  42. CASE WHEN RD.CompleteDate IS NOT NULL THEN
  43. CASE WHEN ProjComp.ProjComp < RD.CompleteDate THEN
  44. DATEPART(wk, ProjComp.ProjComp)
  45. ELSE
  46. RD.CompletedWeek
  47. END
  48. ELSE
  49. DATEPART(wk, ProjComp.ProjComp)
  50. END AS 'CompletedWeek',
  51. PT.fld_int_Frequency AS 'PPMFrequency',
  52. CalloutEngineerType,
  53. CalloutEngineerSubType
  54.  
  55. FROM
  56. Prod.dbo.tblPPMScheduleLine SL (NOLOCK) JOIN
  57. Prod.dbo.tblProjectHeader PH (NOLOCK) ON PH.fld_int_PPMScheduleRef = SL.fld_int_ID JOIN
  58. dbo.tblPPMReportWeekNumbers WN (NOLOCK) ON SL.fld_dat_WeekEnding = WN.WeekEnding JOIN
  59. Prod.dbo.tblStore S (NOLOCK) ON SL.fld_int_StoreID = S.fld_int_ID JOIN
  60. Prod.dbo.tblStoreType ST (NOLOCK) ON S.fld_int_StoreTypeID = ST.fld_int_ID JOIN
  61. Prod.dbo.tblPPMType PT (NOLOCK) ON SL.fld_int_PPMTypeID = PT.fld_int_ID JOIN
  62. Prod.dbo.vw_tblStoreCluster_MI SC (NOLOCK) ON S.fld_int_StoreClusterID = SC.fld_int_ID JOIN
  63. Prod.dbo.tblStoreDivision SD (NOLOCK) ON SC.fld_int_StoreDivisionID = SD.fld_int_ID LEFT JOIN
  64. Prod.dbo.tblHelpDeskResource OM (NOLOCK) ON SC.fld_int_AreaManagerID = OM.fld_int_ID LEFT JOIN
  65. Prod.dbo.tblHelpDeskResource DM (NOLOCK) ON SD.fld_int_DivisionalManagerID = DM.fld_int_ID JOIN
  66. Prod.dbo.tblAssetMainType MT (NOLOCK) ON PT.fld_int_MainTypeID = MT.fld_int_ID LEFT JOIN
  67. (
  68. SELECT DISTINCT
  69. SL.fld_int_ID AS 'PPMRef',
  70. ISNULL(lastcompletedcallout.CalloutEngineerID,callout.CalloutEngineerID) AS 'CalloutEngineerID',
  71. ISNULL(lastcompletedcallout.CalloutEngineer,callout.CalloutEngineer) AS 'CalloutEngineer',
  72. ISNULL(lastcompletedcallout.CalloutDate,callout.CalloutDate) AS 'CalloutDate',
  73. ISNULL(lastcompletedcallout.CompletedBy,callout.CompletedBy) AS 'CompletedBy',
  74. ISNULL(lastcompletedcallout.CompleteDate,callout.CompleteDate) AS 'CompleteDate',
  75. ISNULL(lastcompletedcallout.CompletedWeek,callout.CompletedWeek) AS 'CompletedWeek',
  76. ISNULL(lastcompletedcallout.CalloutEngineerType,callout.CalloutEngineerType) AS 'CalloutEngineerType',
  77. ISNULL(lastcompletedcallout.CalloutEngineerSubType,callout.CalloutEngineerSubType) AS 'CalloutEngineerSubType'
  78. FROM
  79. Prod.dbo.tblPPMScheduleLine SL (NOLOCK) JOIN
  80. Prod.dbo.tblProjectHeader PH (NOLOCK) ON SL.fld_int_ID = PH.fld_int_PPMScheduleRef LEFT JOIN
  81. (
  82. SELECT
  83. PH.fld_int_ID AS 'ProjectHeaderID',
  84. R.fld_int_ID AS 'CalloutEngineerID',
  85. R.fld_str_Name AS 'CalloutEngineer',
  86. RT.fld_str_Name AS 'CalloutEngineerType',
  87. RST.fld_str_Name AS 'CalloutEngineerSubType',
  88. CE.fld_dat_CreatedDate AS 'CalloutDate',
  89.  
  90.  
  91. --amended to only show data when it is completed event status SW 2017-02-21 -----------------------------------
  92. CASE WHEN ce.fld_int_StatusID = 3 THEN CE.fld_str_LastEditedBy END AS 'CompletedBy',
  93. CASE WHEN ce.fld_int_StatusID = 3 THEN CE.fld_dat_LastEdited END AS 'CompleteDate',
  94. CASE WHEN ce.fld_int_StatusID = 3 THEN DATEPART(wk,CE.fld_dat_LastEdited) END AS 'CompletedWeek'
  95.  
  96. FROM
  97. (
  98. SELECT
  99. CP.fld_int_ProjectHeaderID,
  100. MIN(CE.fld_int_ID) AS 'FirstCallout'
  101. FROM
  102. Prod.dbo.tblChildProject CP (NOLOCK) JOIN
  103. Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID
  104. WHERE
  105. fld_int_StatusID IN (1,3) --called out, complete
  106. GROUP BY
  107. CP.fld_int_ProjectHeaderID
  108.  
  109. )FC JOIN
  110. Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON FC.FirstCallout = CE.fld_int_ID JOIN
  111. Prod.dbo.tblChildProject CP (NOLOCK) ON CE.fld_int_ChildProjectID = CP.fld_int_ID JOIN
  112. Prod.dbo.tblProjectHeader PH (NOLOCK) ON CP.fld_int_ProjectHeaderID = PH.fld_int_ID LEFT JOIN
  113. Prod.dbo.tblHelpDeskResource R (NOLOCK) ON CE.fld_int_ResourceID = R.fld_int_ID LEFT JOIN
  114. Prod.dbo.tblHelpDeskResourceType RT (NOLOCK) ON R.fld_int_HelpDeskResourceTypeID=RT.fld_int_ID LEFT JOIN
  115. Prod.dbo.tblHelpDeskResourceType RST (NOLOCK) ON R.fld_int_HelpdeskResourceSubTypeID=RST.fld_int_ID
  116.  
  117. ) callout ON PH.fld_int_ID = callout.ProjectHeaderID LEFT JOIN --first callout for any not completed
  118. (
  119. SELECT
  120. PH.fld_int_ID AS 'ProjectHeaderID',
  121. R.fld_int_ID AS 'CalloutEngineerID',
  122. R.fld_str_Name AS 'CalloutEngineer',
  123. RT.fld_str_Name AS 'CalloutEngineerType',
  124. RST.fld_str_Name AS 'CalloutEngineerSubType',
  125. CE.fld_dat_CreatedDate AS 'CalloutDate',
  126. CE.fld_str_LastEditedBy AS 'CompletedBy',
  127. CE.fld_dat_LastEdited AS 'CompleteDate',
  128. DATEPART(wk,CE.fld_dat_LastEdited) AS 'CompletedWeek'
  129.  
  130. FROM
  131. (
  132. SELECT
  133. a.fld_int_ProjectHeaderID,
  134. a.LastEdited,
  135. MAX(b.CalloutEventID) AS 'LastCompletedCallout' --if there are potentially > 1 completed callout events with the same Last Edited Date, this picks the max one.
  136. FROM
  137. (
  138. SELECT
  139. CP.fld_int_ProjectHeaderID,
  140. MAX(ce.fld_dat_LastEdited) AS LastEdited --datetime of the last edited completed callout event
  141. FROM
  142. Prod.dbo.tblChildProject CP (NOLOCK) JOIN
  143. Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID
  144. WHERE
  145. fld_int_StatusID = 3 AND -- complete
  146. ce.fld_dat_DateCancelled IS NULL -- can be complete and cancelled at once...
  147. GROUP BY
  148. CP.fld_int_ProjectHeaderID
  149.  
  150. ) a LEFT JOIN
  151. (
  152. SELECT
  153. CP.fld_int_ProjectHeaderID,
  154. ce.fld_int_ID AS CalloutEventID,
  155. ce.fld_dat_LastEdited AS OtherLastEdited
  156.  
  157. FROM
  158. Prod.dbo.tblChildProject CP (NOLOCK) JOIN
  159. Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID
  160. WHERE
  161. fld_int_StatusID = 3 AND -- complete
  162. ce.fld_dat_DateCancelled IS NULL -- can be complete and cancelled at once...
  163.  
  164. ) b ON a.fld_int_ProjectHeaderID = b.fld_int_ProjectHeaderID AND
  165. a.LastEdited = b.OtherLastEdited
  166. GROUP BY
  167. a.fld_int_ProjectHeaderID,
  168. a.LastEdited
  169.  
  170.  
  171. --SELECT
  172. --CP.fld_int_ProjectHeaderID,
  173. --MAX(CE.fld_dat_LastEdited) AS 'LastCompletedCalloutDate'
  174. --FROM
  175. --Prod.dbo.tblChildProject CP (NOLOCK) JOIN
  176. --Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID
  177. --WHERE
  178. --fld_int_StatusID = 3 -- complete
  179. --GROUP BY
  180. --CP.fld_int_ProjectHeaderID
  181.  
  182. )FC JOIN
  183. Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON FC.LastCompletedCallout = CE.fld_int_ID JOIN
  184. Prod.dbo.tblChildProject CP (NOLOCK) ON CE.fld_int_ChildProjectID = CP.fld_int_ID JOIN
  185. Prod.dbo.tblProjectHeader PH (NOLOCK) ON CP.fld_int_ProjectHeaderID = PH.fld_int_ID LEFT JOIN
  186. Prod.dbo.tblHelpDeskResource R (NOLOCK) ON CE.fld_int_ResourceID = R.fld_int_ID LEFT JOIN
  187. Prod.dbo.tblHelpDeskResourceType RT (NOLOCK) ON R.fld_int_HelpDeskResourceTypeID=RT.fld_int_ID LEFT JOIN
  188. Prod.dbo.tblHelpDeskResourceType RST (NOLOCK) ON R.fld_int_HelpdeskResourceSubTypeID=RST.fld_int_ID
  189.  
  190. )lastcompletedcallout ON PH.fld_int_ID = lastcompletedcallout.ProjectHeaderID
  191. WHERE
  192. SL.fld_int_StatusID <> 3 AND --ppm not cancelled
  193. callout.CalloutEngineer IS NOT NULL
  194.  
  195. ) RD ON SL.fld_int_ID = RD.PPMRef LEFT JOIN
  196. (
  197. SELECT
  198. ph.fld_int_PPMScheduleRef AS 'PPM_Ref',
  199. MIN(ph.fld_dat_CompleteDate) AS 'ProjComp'
  200. FROM
  201. Prod.dbo.tblProjectHeader ph (NOLOCK)
  202. WHERE
  203. ph.fld_dat_CompleteDate IS NOT NULL
  204. GROUP BY
  205. ph.fld_int_PPMScheduleRef
  206.  
  207. ) ProjComp ON SL.fld_int_ID = ProjComp.PPM_Ref
  208. WHERE
  209. SL.fld_int_StatusID <> 3 AND --ppm not cancelled
  210. fld_int_ProjectStatusID NOT IN (6, 7) AND --project header not cancelled too
  211. SL.fld_dat_WeekEnding <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) --less than today
Add Comment
Please, Sign In to add comment
Public Pastes
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!