c#多表连接查询配合case和then使用

select  s.OrderNo,

               h.Fabric,

               h.MaterialUnit,

             case s.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent ,

              h.Technology,

              h.Quantity

from Orders s join OrdersCoats h on h.OrderID =s.OrderID

where s.OrderDate between '2015-10-01 14:33:29.802'and '2015-11-07 14:33:29.802' and s.OrderStatus ='90' and s.DeliveryDate <='2015-11-07 14:33:29.802'  and s.OrderNo <>N'' and h.Technology <>N''

order by s.DeliveryDate desc