SQL:Server How do I consolidate case when statements that have (SUM)

Hello I want to consolidate this query….

[select substr(prtmst.typcod, 1, 3) Article_Type,
    case when substr(prtmst.typcod, 1, 3) like 'A%' then 'Accessories'
         else
         case when substr(prtmst.typcod, 1, 3) = 'L02' then 'Spirit'
              else
              case when substr(prtmst.typcod, 1, 3) = 'L03' then 'Wine'
                   else
                   case when substr(prtmst.typcod, 1, 3) = 'L04' then 'Beer & Soft Drinks'
                        else
                        case when substr(prtmst.typcod, 1, 3) = 'L05' then 'Confectionary'
                             else
                             case when substr(prtmst.typcod, 1, 3) = 'L06' then 'Food'
                                  else
                                  case when substr(prtmst.typcod, 1, 3) like 'P%' then 'Parfum/Cosmetics'
                                       else
                                       case when substr(prtmst.typcod, 1, 3) like 'T%' then 'Tester'
                                            else
                                            case when substr(prtmst.typcod, 1, 3) like 'X%' then 'GWP'
                                                 else
                                                 case when substr(prtmst.typcod, 1, 3) like 'Z%' then 'Procurement'
                                                      else ''
                                                 end
                                            end
                                       end
                                  end
                             end
                        end
                   end
              end
         end
    end as "Translation",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) < 90 then sum(invdtl.untqty)
         else ''
    end as "Less than 90",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) between 91
     and 120 then sum(invdtl.untqty)
         else ''
    end as "91-120",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) between 121
     and 180 then sum(invdtl.untqty)
         else ''
    end as "121-180",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) between 181
     and 360 then sum(invdtl.untqty)
         else ''
    end as "181-360",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) between 361
     and 500 then sum(invdtl.untqty)
         else ''
    end as "361-500",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) between 501
     and 900 then sum(invdtl.untqty)
         else ''
    end as "501-900",
    case when datediff(day, to_char(invdtl.fifdte, 'MM/DD/YYYY'), sysdate) > 900 then sum(invdtl.untqty)
         else ''
    end as "900+"
   from prtmst,
    invdtl
  where invdtl.prtnum = prtmst.prtnum
and prtmst.prt_client_id = 'HUS'
and prtmst.wh_id_tmpl = 'MFTZ'
and prtmst.typcod is not null
and invdtl.prt_client_id = 'HUS'
and invdtl.ship_line_id is null
and invdtl.wrkref is null
and invdtl.lst_arecod not in ('ADJS', 'CADJ', 'SADJ')
  group by substr(prtmst.typcod, 1, 3),
    to_char(invdtl.fifdte, 'MM/DD/YYYY')
  order by substr(prtmst.typcod, 1, 3) asc]

The problem that I have is that the result shows like this: (413 rows)

article_type translation less than 90 91-120 121-180 181-360 361-500 501-900 900+
A71 Accessories 481
L02 Spirit 1296
L02 Spirit 6
L02 Spirit 96
L02 Spirit 60
L02 Spirit 2100
L02 Spirit 1014
L02 Spirit 252
L02 Spirit 318
L02 Spirit 36
L02 Spirit 192
L02 Spirit 1848
L02 Spirit 2124
L02 Spirit 1550
L02 Spirit 7547
L02 Spirit 4206
L03 Wine 96
L03 Wine 417
L03 Wine 258
L03 Wine 492
L03 Wine 348
L03 Wine 448
L03 Wine 552
L03 Wine 60
L04 Beer & Soft Drinks 1416
L05 Confectionary 19
L05 Confectionary 45
L05 Confectionary 108
L05 Confectionary 546
L05 Confectionary 1112

I want the results to look like this: (11 Rows)

translation less than 90    91-120  121-180 181-360 361-500 501-900 900+

A71 481
L02 19147 36 3462
L03 1452 448 771
L04 1416
L05 3666 2153 3630 2691 387 1689
P81 11460 7056 13581 19070 27626 12141
P83 525 344 253
T85 880 802 2888 3811 4732 2539
T90 20 786 36
X 5082 6
Z10 250 130 549

Please help me….basically the query is not grouping the case when statements because they have the sum function….therefore, is showing multiple results to be consolidated in the same column…


Source: sql

Leave a Reply