remymumoh

Untitled

Sep 22nd, 2020
1,046
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [BAISV_DI]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[dailyreports]    Script Date: 9/22/2020 6:59:53 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE  [dbo].[dailyreports] AS
  9. SELECT
  10. TTEAM_ID,
  11. VDATE,
  12. dbo.Days_in_Field(TTEAM_ID,VDATE) AS Days_in_Field,
  13. dbo.Days_in_Field(TTEAM_ID,VDATE) *4 AS "Expected cummilative Number of HH",
  14. dbo.HH_reached(TTEAM_ID,VDATE) AS 'Cummlative HH Reached',
  15. [Completed]
  16. +[Entire household absent FOR extended period OF TIME]
  17. +[Refused]
  18. +[Dwelling vacant]
  19. +[Address NOT a dwelling]
  20. +[Dwelling destroyed]
  21. +[Dwelling NOT found]
  22. +[Inaccessible due TO flooding OR security]
  23. +[Stop survey]
  24. +[Withdrawn]
  25. +[No competent head OF household]
  26. +[No household member at home]
  27. +[Postponed]
  28. + [At home, reschedule visit]
  29. +[Other] AS 'HH Reached for day',
  30.  
  31.  
  32. [Completed]
  33. +[Entire household absent FOR extended period OF TIME]
  34. +[Refused]
  35. +[Dwelling vacant]
  36. +[Address NOT a dwelling]
  37. +[Dwelling destroyed]
  38. +[Dwelling NOT found]
  39. +[Inaccessible due TO flooding OR security]
  40. +[Stop survey]
  41. +[Withdrawn]
  42. +[No competent head OF household] AS 'HHs Finalised for day',
  43.  
  44. [No household member at home]
  45. +[Postponed]
  46. + [At home, reschedule visit]
  47. +[Other] AS 'Number of HH in process for day',
  48.  
  49. [Refused] AS 'Number of HH Refused for day',
  50.  
  51.  
  52. [No household member at home]
  53. + [Postponed]
  54. + [At home, reschedule visit] AS 'Number of Re-visits Scheduled for day',
  55.  
  56. [Entire household absent FOR extended period OF TIME]
  57. +[Refused]
  58. +[Stop survey]
  59. +[Withdrawn]
  60. AS 'HH loss (3rd visit attempt+ refusals) for day'
  61.  
  62. -- , *
  63.  
  64. FROM (
  65. SELECT HEA,HRESULT,ct.TTEAM_ID, DATEFROMPARTS([HVYEAR],[HVMONTH],[HVDAY]) AS VDATE
  66. FROM [HSECOVER] hh
  67. LEFT JOIN [dbo].[CLUSTERTEAM_REC] ct ON ct.TCLUSTER = hh.HEA
  68. LEFT JOIN [dbo].[HRESULT_code] hhc ON hhc.[HH_ID] = hh.[HRESULT]
  69. ) t
  70.  
  71. PIVOT(
  72. COUNT(HEA)
  73. FOR [HRESULT] IN (
  74. [Completed],
  75. [No household member at home],
  76. [Entire household absent FOR extended period OF TIME],
  77. [Postponed],
  78. [Refused],
  79. [Dwelling vacant],
  80. [Address NOT a dwelling],
  81. [Dwelling destroyed],
  82. [Dwelling NOT found],
  83. [Inaccessible due TO flooding OR security],
  84. [Withdrawn],
  85. [Stop survey],
  86. [No competent head OF household],
  87. [At home, reschedule visit],
  88. [Other]
  89. )
  90. ) AS pivot_table
  91.  
  92.  
RAW Paste Data