CASE statement with a NOT IN condition

Question

Post reply

CASE statement with a NOT IN condition

Will DOugherty

Old Hand

Points: 368
More actions
October 22, 2006 at 1:17 pm

#121004

Hi,
I'm doing a Pivot operation in SQL Server 2K (yeah, I know it's built into 2005) and I'd like to catch any future unanticipated values rather than missing them. So I tried using a "NOT IN" as my WHEN expression and was dope slapped by SQL Server for being so foolish. Here's what I would like to have work (high-lited red).
select (e.LastName + ', ' + e.FirstName) As Employee
, SUM( CASE r.packagetransmethodid WHEN 1 THEN ShipmentQty ELSE 0 END) as "UPS"
, SUM( CASE r.packagetransmethodid WHEN 2 THEN ShipmentQty ELSE 0 END) as "CommonCarrier"
, SUM( CASE r.packagetransmethodid WHEN 3 THEN ShipmentQty ELSE 0 END) as "SupplierArrangement"
, SUM( CASE r.packagetransmethodid WHEN 4 THEN ShipmentQty ELSE 0 END) as "WalkIn"
, SUM( CASE r.packagetransmethodid WHEN 5 THEN ShipmentQty ELSE 0 END) as "Mail"
, SUM( CASE r.packagetransmethodid WHEN 6 THEN ShipmentQty ELSE 0 END) as "Fax"
, SUM( CASE r.packagetransmethodid WHEN 7 THEN ShipmentQty ELSE 0 END) as "Email"
, SUM( CASE r.packagetransmethodid WHEN 8 THEN ShipmentQty ELSE 0 END) as "Telephone"
, SUM( CASE r.packagetransmethodid WHEN 9 THEN ShipmentQty ELSE 0 END) as "PickUp"
, SUM( CASE r.packagetransmethodid WHEN NOT IN (1,2,3,4,5,6,7,8,9) THEN ShipmentQty ELSE 0 END) as "Others"
, SUM(ShipmentQty) as "Total"
from tbloerecorddetail as rd
join tbloerecord as r On rd.recordid = r.recordid
join tblEmployee As e On r.pickedbyempid = e.employeeid
where r.shipmentdate between @startdate and @enddate
and rd.productid In ( select p.productid
      from tblproduct as p
      join tblproductgroup as pg on p.productgroupid = pg.productgroupid
      join tblproductcategory as pc on pc.productcategoryid = pg.productcategoryid
      where producttypeid = 1)
group by (e.LastName + ', ' + e.FirstName) With RollUp
Would any kind souls out there be able to suggest the correct solution?
Thanks ,
Will Dougherty

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

What about

, SUM( CASE r.packagetransmethodid WHEN 9 THEN ShipmentQty ELSE 0 END) as "Others"

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Use the other form of case...

, SUM( CASE WHEN r.packagetransmethodid NOT IN (1,2,3,4,5,6,7,8,9) THEN ShipmentQty ELSE 0 END) as "Others"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Will DOugherty Old Hand Points: 368 More actions · Answer 3

"Ahhhhh. I see!", said the blind man.

Thanks ,

Will Dougherty