sql - how to write a nested case query and get correct results -

i have case query , need in re-writing perfect results. current query follows:

    case  when monthsretrieved <= 6 , monthsretrieved > 0      case          when monthssincelastreceipt <= 6 'fast'          when monthssincelastreceipt > 6 , monthssincelastreceipt <= 12 'slow'          when monthssincelastreceipt > 12 , monthssincelastreceipt <= 18 'very slow'          when monthssincelastreceipt > 18 'dead'          when openingqty = 0 'new items'          when qtyissued = 0 'non-moving'      else 'fast'      end  when monthsretrieved > 6 , monthsretrieved <= 12      case          when monthsretrieved <= 12 'slow'          when monthssincelastreceipt > 12 , monthssincelastreceipt <= 18 'very slow'          when monthssincelastreceipt > 18 'dead'          when openingqty = 0 'new items'          when qtyissued = 0 'non-moving'      else 'slow'      end  when monthsretrieved > 12 , monthsretrieved <= 18      case          when monthssincelastreceipt <= 18 'very slow'          when monthssincelastreceipt > 18 'dead'          when openingqty = 0 'new items'          when qtyissued = 0  'non-moving'      else 'very slow'      end  when monthsretrieved = 0      case          when monthssincelastreceipt = 0 'dead'          when openingqty = 0 'new items'          when qtyissued = 0 'non-moving'      else 'dead'      end  when monthsretrieved > 18      case          when monthssincelastreceipt > 18 'dead'          when openingqty = 0 'new items'          when qtyissued = 0 'non-moving'      else 'dead'      end  end 

i getting value in months formula , in monthsretrieved. value in months monthssincelastreceipt, months since last receipt date. openingqty opening quantity @ specific date. qtyissued quantity issued between 2 dates.

the items being classified fast, slow, slow, dead, new items, non-moving. case statement nested each classification. getting results not correct. sure overlooking in statement.

the when's in case evaluated in order.
, once 1 evaluates true, follows gets ignored.

so when example :

case when x >= 1 , x < 3    case     when x = 1 'one'    when x < 3 'two'    when y = 0 'this gets never evaluated'    end end 

the 3th gets never evaluated since 1 of previous checks surely true.

so need move checks on openingqty , qtyissued above checks on monthssincelastreceipt.

and review how check on monthssincelastreceipt. of checks ignored in current query.

i suggest creating small mockup table column names , fill values each case. use table construct query cases till results expect them be.

here's attempt. without knowing expected results should be, it's guess.

delete testtable; insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(0,0,0,0); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,0,0,0); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,0,0); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,6,19); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,6,13); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,6,7); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,6,3); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,12,19); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,12,13); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,12,7); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,18,19); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,18,15); insert testtable (openingqty,qtyissued,monthsretrieved,monthssincelastreceipt) values(1,1,19,0);  select t.*, case when openingqty = 0 'new items' when qtyissued = 0 'non-moving' when monthsretrieved = 0 'dead' when monthsretrieved between 1 , 6      (case      when monthssincelastreceipt > 18 'dead'      when monthssincelastreceipt > 12 'very slow'       when monthssincelastreceipt > 6  'slow'       else 'fast'      end) when monthsretrieved between 7 , 12      (case       when monthssincelastreceipt > 18 'dead'      when monthssincelastreceipt > 12 'very slow'       else 'slow'      end) when monthsretrieved between 13 , 18      (case       when monthssincelastreceipt > 18 'dead'      else 'very slow'       end) when monthsretrieved >= 19 'dead'  end result testtable t;