API tools faq
paste
Guest User

Untitled

a guest
May 8th, 2012
37
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.57 KB | None | 0 0
raw download clone embed print report
  1. there is some mistake in this sql query
  2. declare @Age int
  3. declare @Sex varchar(20)
  4.  
  5. set @Age=20
  6. set @Sex='M'
  7.  
  8. select
  9. d.Dep_Name,d.Dep_Code,g.Group_Name,t.Test_Name,t.Test_Unit,(
  10. case
  11. when at1.First_Age>=@Age and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_1yr)
  12. when (at1.First_Age<@Age and at1.Second_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_20yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_20yr)
  13. -- 5 more when statements
  14.  
  15. ),
  16. st.Sub_Test_Name, st.Sub_Test_Unit, p.Result_Type,
  17. p.Numeric_Value, p.Paragraph_Value ,p.Result_Normal,p.Sub_Test_ID,
  18. (
  19. case
  20. when ast.First_Age>=@Age and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_1yr)
  21. --more WHEN's - mirrors the above case
  22. )
  23. FROM
  24. Patient_Test_3SC p LEFT JOIN
  25. ((Tests t INNER JOIN
  26. Advanced_test_detail at1 on t.test_id=at1.test_id) LEFT JOIN
  27. (Sub_Tests st INNER JOIN
  28. Advanced_Sub_tests ast on st.sub_test_id=ast.sub_test_id) on t.Test_Code=st.Sub_Tests_Test_Code)
  29. ON p.Test_ID=t.Test_ID
  30. INNER JOIN Department d on p.Department_Code=d.Dep_Code
  31. LEFT JOIN Groups g on p.Group_Code=g.Group_Code
  32. WHERE p.Patient_ID=@pid
  33.  
  34. declare @Age int
  35. declare @Sex varchar(20)
  36. declare @pid varchar(20)
  37.  
  38. set @Age=20
  39. set @Sex='M'
  40.  
  41. SELECT
  42. d.Dep_Name,d.Dep_Code,g.Group_Name,t.Test_Name,t.Test_Unit,
  43. st.Sub_Test_Name, st.Sub_Test_Unit, p.Result_Type,
  44. p.Numeric_Value, p.Paragraph_Value ,p.Result_Normal,p.Sub_Test_ID,
  45. CASE
  46. when at1.First_Age>=@Age and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_1yr)
  47. when (at1.First_Age<@Age and at1.Second_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_20yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_20yr)
  48. -- 5 more when statements
  49. END as 'var1',
  50. CASE
  51. when ast.First_Age>=@Age and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_1yr)
  52. --more WHEN's - mirrors the above case
  53. END as 'var2'
  54. FROM
  55. Patient_Test_3SC p LEFT JOIN
  56. ((Tests t INNER JOIN Advanced_test_detail at1 on t.test_id=at1.test_id) LEFT JOIN
  57. (Sub_Tests st INNER JOIN Advanced_Sub_tests ast on st.sub_test_id=ast.sub_test_id) on t.Test_Code=st.Sub_Tests_Test_Code)
  58. ON p.Test_ID=t.Test_ID
  59. INNER JOIN Department d on p.Department_Code=d.Dep_Code
  60. LEFT JOIN Groups g on p.Group_Code=g.Group_Code
  61. WHERE p.Patient_ID=@pid
  62.  
  63. declare @Age int
  64. set @Age=20
  65.  
  66. declare @Sex varchar(20)
  67. set @Sex='M'
  68.  
  69. select
  70. d.Dep_Name,d.Dep_Code,g.Group_Name,t.Test_Name,t.Test_Unit,
  71. case
  72. when at1.First_Age>=@Age and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_1yr)
  73. when (at1.First_Age<@Age and at1.Second_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_20yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_20yr)
  74. when (at1.Second_Age<@Age and at1.Third_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_40yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_40yr)
  75. when (at1.Third_Age<@Age and at1.Fourth_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_60yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_60yr)
  76. when (at1.Fourth_Age<@Age and at1.Fifth_Age>=@Age) and @Sex='M' then convert(varchar(10),at1.L_Bound_M_LessThan_125yr)+'-'+convert(varchar(10),at1.U_Bound_M_LessThan_125yr)
  77. when at1.First_Age>=@Age and @Sex='F' then convert(varchar(10),at1.L_Bound_F_LessThan_1yr)+'-'+convert(varchar(10),at1.U_Bound_F_LessThan_1yr)
  78. when (at1.First_Age<@Age and at1.Second_Age>=@Age) and @Sex='F' then convert(varchar(10),at1.L_Bound_F_LessThan_20yr)+'-'+convert(varchar(10),at1.U_Bound_F_LessThan_20yr)
  79. when (at1.Second_Age<@Age and at1.Third_Age>=@Age) and @Sex='F' then convert(varchar(10),at1.L_Bound_F_LessThan_40yr)+'-'+convert(varchar(10),at1.U_Bound_F_LessThan_40yr)
  80. when (at1.Third_Age<@Age and at1.Fourth_Age>=@Age) and @Sex='F' then convert(varchar(10),at1.L_Bound_F_LessThan_60yr)+'-'+convert(varchar(10),at1.U_Bound_F_LessThan_60yr)
  81. when (at1.Fourth_Age<@Age and at1.Fifth_Age>=@Age) and @Sex='F' then convert(varchar(10),at1.L_Bound_F_LessThan_60yr)+'-'+convert(varchar(10),at1.U_Bound_F_LessThan_60yr)
  82. ELSE NULL END AS x ,
  83. st.Sub_Test_Name,st.Sub_Test_Unit,p.Result_Type,p.Numeric_Value,p.Paragraph_Value,p.Result_Normal,p.Sub_Test_ID,
  84. case
  85. when ast.First_Age>=@Age and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_1yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_1yr)
  86. when (ast.First_Age<@Age and ast.Second_Age>=@Age) and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_20yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_20yr)
  87. when (ast.Second_Age<@Age and ast.Third_Age>=@Age) and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_40yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_40yr)
  88. when (ast.Third_Age<@Age and ast.Fourth_Age>=@Age) and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_60yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_60yr)
  89. when (ast.Fourth_Age<@Age and ast.Fifth_Age>=@Age) and @Sex='M' then convert(varchar(10),ast.L_Bound_M_LessThan_125yr)+'-'+convert(varchar(10),ast.U_Bound_M_LessThan_125yr)
  90. when ast.First_Age>=@Age and @Sex='F' then convert(varchar(10),ast.L_Bound_F_LessThan_1yr)+'-'+convert(varchar(10),ast.U_Bound_F_LessThan_1yr)
  91. when (ast.First_Age<@Age and ast.Second_Age>=@Age) and @Sex='F' then convert(varchar(10),ast.L_Bound_F_LessThan_20yr)+'-'+convert(varchar(10),ast.U_Bound_F_LessThan_20yr)
  92. when (ast.Second_Age<@Age and ast.Third_Age>=@Age) and @Sex='F' then convert(varchar(10),ast.L_Bound_F_LessThan_40yr)+'-'+convert(varchar(10),ast.U_Bound_F_LessThan_40yr)
  93. when (ast.Third_Age<@Age and ast.Fourth_Age>=@Age) and @Sex='F' then convert(varchar(10),ast.L_Bound_F_LessThan_60yr)+'-'+convert(varchar(10),ast.U_Bound_F_LessThan_60yr)
  94. when (ast.Fourth_Age<@Age and ast.Fifth_Age>=@Age) and @Sex='F' then convert(varchar(10),ast.L_Bound_F_LessThan_60yr)+'-'+convert(varchar(10),ast.U_Bound_F_LessThan_60yr)
  95. ELSE NULL END AS y
  96.  
  97. from Patient_Test_3SC p
  98. left join((Tests t inner join Advanced_test_detail at1 on t.test_id=at1.test_id )
  99. left join (Sub_Tests st inner join Advanced_Sub_tests ast on st.sub_test_id=ast.sub_test_id) on t.Test_Code=st.Sub_Tests_Test_Code) on p.Test_ID=t.Test_ID
  100. inner join Department d on p.Department_Code=d.Dep_Code
  101. left join Groups g on p.Group_Code=g.Group_Code
  102. where p.Patient_ID=@pid
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!