SQL query to cross-reference the same table

Question

I have a below query where I'm using left-outer join to cross-reference the same table. Basically I would like to cross-reference Min_Size_Code field to reflect Min_Actual_size and Max_Size_Code field to reflect Max_Actual_Size. However, I'm not getting the correct answer I'm looking for.

Below is my answer based on the SQL query

Ideal answer should be as below

Below is my code

select A.week,A.style,A.Color, A.location, A.Actual_size, A.size_code, A.Min_size_code, A.Max_size_code,
min(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Min_Actual_Size,
max(B.Actual_size) over(partition by A.week,A.style,A.Color,A.location) Max_Actual_Size

from
(
select week, style, Color, location, Actual_size, size_code,
min(size_code)over(partition by week,style,Color,location) min_size_code,
max(size_code)over(partition by week,style,Color,location) max_size_code

from TestSizeTable where Style = 'AB123' and color = 'WY4567'
and week = '202002'  and location in ( '111')
) A
Left outer Join 
TestSizeTable B
on ( A.Style = B.Style and A.Color = B.Color and A.Week = B.Week
and A.Location = B.Location)
where (B.size_code = A.min_size_code or B.size_code = A.max_size_code)

Any help would be great!!

Answer 1

I think the problem lies in the data type of the column actual_size. I suspect it's varcahr and in that case 14 is less than 2.

Your code works fine here.

Please check the updated solution for the varchar column (actual_size) HERE.