remymumoh

Untitled

Sep 22nd, 2020
1,242
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×