I have a scenario in which I have table A which has fixed data of 7 rows as below :
Range 0-59
60-119
120-179
180-239
240-299
300-499
500+
Now I have another table B in which I have units.. I have to show all records of Table A and then the units which fall in that range that particular value and for rest range as 0 or null.
So in order to join the two tables I created a column in table B with case statement so that rows have the range as the column :
(case
when v between '0' and '59' then '0 to 59'
when v between '60' and '119' then '60 to 119'
when V between '120' and '179' then '120 to 179'
when v between '180' and '239' then '180 to 239'
when v between '240' and '299' then '240 to 299'
when v between '300' and '499' then '300 to 499'
when v >'500' then '500+'
else 'other'
end)
Then I joined these two tables.... Now when I populate these records I get just the matching row only one.... My requirement is to show all ranges as well.....
Please refer below screenshots for Clarification of scenario :
