How to use CASE in this situation? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to use CASE in this situation?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2011-03-24 : 03:59:59

Hi,

I am using MS SQL Server R2. I m trying to make a division in my select statement and I would like to check it with CASE. Here is the sample SELECT statment below and the part I would like to add CASE is starting with ROUND.
(ROUND(d.deliveryamount/(select SUM(consumedAmount)
from dailyconsumption where VendorID = vr.VendorID and ConsumedItemID = vr.ItemID and consumeddate >= d.deliverydate),5) as LastDeliveryWasteRatio)

Thanks in advance.

Best Regards.

select vr.RequestID,vr.VendorID,vr.RequestDate,vr.ItemID,vr.PackageID,vr.Quantity,vr.Total,ra.MonthlyConsumption,d.deliverydate,
(select SUM(consumedAmount)
from dailyconsumption
where VendorID = vr.VendorID
and ConsumedItemID = vr.ItemID
and consumeddate >= d.deliverydate) as ConsumptionSinceLastDelivery,d.deliveryamount,
ROUND(d.deliveryamount/(select SUM(consumedAmount)
from dailyconsumption where VendorID = vr.VendorID and ConsumedItemID = vr.ItemID and consumeddate >= d.deliverydate),5) as LastDeliveryWasteRatio,ra.alltimedelivered,ra.alltimeconsumed,
CASE WHEN ra.alltimeconsumed = 0 THEN 0 WHEN ra.alltimeconsumed > 0 THEN ra.alltimedelivered/ra.alltimeconsumed END as WasteRatio,
CASE WHEN ra.lastyearconsumed = 0 THEN 0 WHEN ra.lastyearconsumed > 0 THEN ra.lastyeardelivered/ra.lastyearconsumed END as LastYearWasteRatio,
CASE WHEN ra.currentyearconsumed = 0 THEN 0 WHEN ra.currentyearconsumed > 0 THEN ra.currentyeardelivered/ra.currentyearconsumed END as CurrentYearWasteRatio
from VendorRequest vr,Delivery d,RequestAnalysis ra
where vr.DeliveryID = d.ID
and vr.VendorID = ra.vendorID
and vr.ItemID = ra.ItemID
and vr.Approved = 0
and vr.StatusID = 0
order by vr.requestdate,vr.vendorID,vr.ItemID asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-27 : 01:22:01

for division expressions use like ra.alltimedelivered/NULLIF(ra.alltimeconsumed,0) to avoid divide by zero errors

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources