0

I am writing this query:

with CTE as
(
SELECT testCategory, testType, compareTestCategory,A.MEDIAN, A.average from 
tInputValue_SamplesRequired

)

Select testCategory,testType,C.Median,C.Average,C.Median + D.Median as change,C.Average + D.average as [%change],C.compareTestCat from CTE C
left outer CTE D
on C.testCategory = D.compareTestCategory
and C.testType = D.testType

I would like to get the recursive record such as below image : enter image description here

However I the result I got is like below : enter image description here

By right, if the compare test category is equal to test category, I would like to use the median and average to the calculation.

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Muhammad Akmal
  • 59
  • 6
  • Which RDBMS are you using? – Alex Dec 13 '17 at 14:28
  • @Alex Microsoft sql server – Muhammad Akmal Dec 13 '17 at 14:29
  • Can you post some DDL and Sample Data please? A SQL query on it's own means little if we don't have any objects or data to use it with. – Thom A Dec 13 '17 at 14:33
  • What is the intended meaning of compare_tTest_Cateogory_id. Your results look identical except for ordering and that column which is not defined in your select statement – SteveB Dec 13 '17 at 14:37
  • @steveb sorry, I don't need the compare_tTest_Category_id. I just want to compare the OP from compareTestCategory and OP from testCategory – Muhammad Akmal Dec 13 '17 at 14:42
  • @MuhammadAkmal, so you want to have a different output in %change if compareTestCategory is equal to testCategory? Have you tried a case statement? You can find an example at https://stackoverflow.com/questions/15265874/case-statement-in-mysql – SteveB Dec 13 '17 at 14:50
  • hi @steveb I didn't try it yet. – Muhammad Akmal Dec 14 '17 at 02:48
  • @MuhammadAkmal, so you want the %change column? What calculation do you want to use when test category = test category? – SteveB Dec 14 '17 at 05:29

0 Answers0