Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 8th, 2012  |  syntax: None  |  size: 6.57 KB  |  hits: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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
create a new version of this paste RAW Paste Data