API tools faq
paste
moshonk

Untitled

moshonk
Jun 21st, 2019
112
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.43 KB | None | 0 0
raw download clone embed print report
  1. SELECT Count(DISTINCT a.PatientPK) Tx_New,
  2. (SELECT Count(DISTINCT a.PatientPK) Tx_CURR
  3. FROM (SELECT DISTINCT a.FacilityName,
  4. a.SatelliteName,
  5. a.PatientPK,
  6. a.Gender,
  7. dbo.fn_GetAgeGroup(dbo.fn_DateDiff('mm', a.DOB, Max(p.DispenseDate)) / 12,
  8. 'DATIM') ageGroup,
  9. CAST(a.LastARTDate AS date) LastARTDate,
  10. e.ExpectedReturn,
  11. CASE
  12. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) > 90 AND
  13. c.ExitReason IS NULL THEN 'Lost'
  14. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) BETWEEN 14
  15. AND 90 AND c.ExitReason IS NULL THEN 'Defaulted'
  16. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) < 14 AND
  17. c.ExitReason IS NULL THEN 'Active' ELSE c.ExitReason END AS ARTStatus
  18. FROM tmp_ARTPatients a
  19. INNER JOIN (SELECT *
  20. FROM (SELECT CAST(Row_Number() OVER (PARTITION BY tmp_Pharmacy.PatientPK
  21. ORDER BY tmp_Pharmacy.DispenseDate DESC) AS Varchar) AS RowID,
  22. tmp_Pharmacy.PatientPK,
  23. tmp_Pharmacy.DispenseDate,
  24. tmp_Pharmacy.Duration,
  25. tmp_Pharmacy.ExpectedReturn
  26. FROM tmp_Pharmacy
  27. WHERE tmp_Pharmacy.DispenseDate <= CAST(@ToDate AS DateTime) AND
  28. tmp_Pharmacy.TreatmentType IN ('ART', 'PMTCT')) AS RP
  29. WHERE RP.RowID = 1) p ON a.PatientPK = p.PatientPK
  30. LEFT JOIN (SELECT * FROM tmp_LastStatus c WHERE c.ExitDate <= @fromdate) c ON a.PatientPK = c.PatientPK
  31. INNER JOIN (
  32. SELECT p.ptn_pk AS PatientPK, CAST(MAX(e.AppointmentDate) AS DATE) as ExpectedReturn FROM IQCare_CPAD.dbo.PatientAppointment e
  33. INNER JOIN IQCare_CPAD.dbo.PatientMasterVisit v ON e.PatientMasterVisitId = v.Id
  34. INNER JOIN IQCare_CPAD.dbo.Patient p ON p.id = v.PatientId
  35. WHERE v.VisitDate <=@todate AND e.AppointmentDate IS NOT NULL GROUP BY p.ptn_pk
  36. ) e ON e.PatientPK = a.PatientPK
  37. WHERE (CASE
  38. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) >
  39. 90 THEN 'Lost'
  40. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) BETWEEN 31
  41. AND 90 THEN 'ULTFU'
  42. WHEN DateDiff(dd, e.ExpectedReturn, CAST(@todate AS datetime)) <=
  43. 30 THEN 'Defaulted' ELSE c.ExitReason END IN ('Active')) OR
  44. (a.RegistrationDate IS NOT NULL AND a.RegistrationDate <= CAST(@toDate AS
  45. datetime) AND a.StartARTDate <= CAST(@toDate AS datetime) AND
  46. DateAdd(day, 30, e.ExpectedReturn) >= CAST(@toDate AS datetime) AND
  47. (c.ExitReason IS NULL OR c.ExitReason <> 'Death') AND (a.PatientType <>
  48. 'Transit' OR a.PatientType IS NULL))
  49. GROUP BY a.FacilityName,
  50. a.SatelliteName,
  51. a.PatientPK,
  52. a.Gender,
  53. e.ExpectedReturn,
  54. a.DOB,
  55. a.LastARTDate,
  56. c.ExitReason) a)Tx_CURR,
  57. (SELECT count(DISTINCT a.PatientID)LTFU_Recent FROM tmp_ARTPatients a INNER JOIN (SELECT * FROM (SELECT CAST(Row_Number() OVER (PARTITION BY tmp_Pharmacy.PatientPK ORDER
  58. BY tmp_Pharmacy.DispenseDate DESC) AS Varchar) AS RowID, tmp_Pharmacy.PatientPK, tmp_Pharmacy.DispenseDate,
  59. tmp_Pharmacy.Duration, tmp_Pharmacy.ExpectedReturn FROM tmp_Pharmacy
  60. WHERE tmp_Pharmacy.DispenseDate <= CAST(@ToDate AS DateTime) AND
  61. tmp_Pharmacy.TreatmentType IN ('ART', 'PMTCT')) AS RP
  62. WHERE RP.RowID = 1) p ON a.PatientPK = p.PatientPK
  63. LEFT JOIN (SELECT * FROM tmp_LastStatus c WHERE c.ExitDate <= @fromdate) c ON c.PatientPK = a.PatientPK
  64. WHERE dateadd(dd, 31 ,p.ExpectedReturn) between CAST(@fromDate AS datetime) and CAST(@toDate AS datetime)
  65. AND a.RegistrationDate IS NOT NULL AND a.RegistrationDate <= CAST(@toDate AS
  66. datetime) AND a.StartARTDate <= CAST(@toDate AS datetime) AND (c.ExitReason IS NULL or c.exitDate >CAST(@todate AS datetime))
  67. AND (a.PatientType <> 'Transit' OR a.PatientType IS NULL))LTFU_Recent,
  68. (SELECT count(DISTINCT a.PatientPk)Tx_RTC
  69. FROM tmp_ARTPatients a
  70. INNER JOIN (SELECT * FROM (SELECT CAST(Row_Number() OVER (PARTITION BY tmp_Pharmacy.PatientPK ORDER BY tmp_Pharmacy.DispenseDate asc) AS Varchar) AS RowID,
  71. tmp_Pharmacy.PatientPK,tmp_Pharmacy.DispenseDate, tmp_Pharmacy.Duration,tmp_Pharmacy.ExpectedReturn,ltfu.ExpectedReturn LastLTFUDate
  72. FROM tmp_Pharmacy left join (
  73. Select Distinct p.PatientPK, a.PatientID, Upper(a.PatientName) [Patient Name], a.Gender, a.StartARTDate, a.PreviousARTStartDate,
  74. dbo.fn_DateDiff('mm', a.DOB, Max(p.DispenseDate)) / 12 AgeAtVisit, Cast(Max(p.DispenseDate) As Date) DispensedDate,
  75. a.LastRegimen [Current Regimen], DBO.fn_DateDiff('dd', Max(p.DispenseDate), Max(p.ExpectedReturn)) DurationOfDrugs,
  76. Cast(Max(p.ExpectedReturn) As Date) ExpectedReturn From tmp_ARTPatients a
  77. Inner Join (Select * From (Select Cast(Row_Number() Over (Partition By tmp_Pharmacy.PatientPK Order By tmp_Pharmacy.DispenseDate Desc) As Varchar) As RowID,
  78. tmp_Pharmacy.PatientPK, tmp_Pharmacy.DispenseDate, tmp_Pharmacy.Duration, tmp_Pharmacy.ExpectedReturn From tmp_Pharmacy
  79. Where tmp_Pharmacy.DispenseDate <= Cast(@fromDate As DateTime) And tmp_Pharmacy.TreatmentType In ('ART', 'PMTCT')) As RP Where RP.RowID = 1) p On a.PatientPK = p.PatientPK
  80. Left Join (SELECT * FROM tmp_LastStatus c WHERE c.ExitDate <= @fromdate) c On c.PatientPK = a.PatientPK Where a.RegistrationDate Is Not Null And
  81. a.RegistrationDate <= Cast(@fromDate As datetime) And a.StartARTDate <= Cast(@fromDate As datetime)
  82. And DateDiff(DAY, p.ExpectedReturn, Cast(@fromdate As datetime)) >30 And (c.ExitReason Is Null Or c.ExitReason <> 'Death') And
  83. (a.PatientType <> 'Transit' Or a.PatientType Is Null) Group By p.PatientPK, a.PatientID, a.Gender, a.StartARTDate, a.PreviousARTStartDate, a.LastRegimen, a.DOB, a.PatientName) ltfu on ltfu.PatientPK=tmp_Pharmacy.Patientpk
  84. WHERE tmp_Pharmacy.DispenseDate between cast(ltfu.ExpectedReturn as datetime) and CAST(@ToDate AS DateTime) AND
  85. tmp_Pharmacy.TreatmentType IN ('ART', 'PMTCT')) AS RP WHERE RP.RowID = 1) p ON a.PatientPK = p.PatientPK
  86. LEFT JOIN (SELECT * FROM tmp_LastStatus c WHERE c.ExitDate <= @fromdate) c ON c.PatientPK = a.PatientPK
  87. WHERE a.RegistrationDate IS NOT NULL AND a.RegistrationDate <= CAST(@toDate AS
  88. datetime) AND a.StartARTDate <= CAST(@toDate AS datetime) AND
  89. p.DispenseDate between CAST(@fromDate AS datetime) and CAST(@todate AS datetime) )TX_RTC,
  90. (SELECT Count(DISTINCT a.PatientPK) HTS_TST FROM dbo.tmp_HTS_LAB_register a
  91. INNER JOIN tmp_PatientMaster b ON b.PatientPK = a.PatientPK
  92. WHERE a.VisitDate BETWEEN CAST(@fromdate AS datetime) AND CAST(@todate AS datetime) )HTS_TST,
  93. (SELECT Count(DISTINCT a.PatientPK) HTS_POS_Overall FROM dbo.tmp_HTS_LAB_register a INNER JOIN tmp_PatientMaster b ON b.PatientPK = a.PatientPK
  94. WHERE a.VisitDate BETWEEN CAST(@fromdate AS datetime) AND CAST(@todate
  95. AS datetime) AND a.finalResultHTS = 'Positive')HTS_POS_Overall
  96. FROM (SELECT DISTINCT a.FacilityName,
  97. a.PatientPK,
  98. a.Gender,
  99. a.AgeLastVisit,
  100. dbo.fn_GetAgeGroup(Round(a.AgeARTStart, 0), 'DATIM') ageGroup,
  101. CAST(a.RegistrationDate AS date) RegistrationDate,
  102. CAST(a.StartARTDate AS date) StartARTDate,
  103. CAST(a.LastARTDate AS date) LastARTDate
  104. FROM tmp_ARTPatients a
  105. LEFT JOIN (SELECT * FROM tmp_LastStatus c WHERE c.ExitDate <= @fromdate) c ON a.PatientPK = c.PatientPK
  106. WHERE a.StartARTDate BETWEEN CAST(@fromdate AS datetime) AND CAST(@todate AS
  107. datetime) AND a.RegistrationDate <= CAST(@todate AS datetime) AND
  108. (a.PreviousARTStartDate IS NULL OR a.PreviousARTStartDate BETWEEN
  109. CAST(@fromdate AS datetime) AND CAST(@todate AS datetime)) AND
  110. (a.PatientType <> 'Transit' OR a.PatientType IS NULL) AND
  111. (a.PatientSource <> 'Transfer In' OR a.PatientSource IS NULL)) a
Advertisement
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!