Search

Thursday, March 19, 2009

Case Statement in Ms-SQL Server

SELECT Partys.PartyId, Partys.PartyName, GRN.ChallanNo, GRNDetail.GrnId, GRNDetail.ChallanQty,
GRNDetail.AcceptQty,
CASE
WHEN challanqty = Acceptqty THEN '55%'
WHEN ((Acceptqty / challanqty) * 100) > 90 THEN '50%'
WHEN ((Acceptqty / challanqty) * 100) > 90 AND ((Acceptqty / challanqty) * 100) < 70 THEN '40%'
WHEN ((Acceptqty / challanqty) * 100) > 70 AND ((Acceptqty / challanqty) * 100) < 50 THEN '25%'
ELSE '0%'
END AS 'Quality',

GRN.PoNo,

Convert(nvarchar,PurchaseOrders.DeliverbyDate,103) as DeliverbyDate,convert(nvarchar,grn.GrnDate,103)AS GrnDate,
Case

when (datediff(day,DeliverbyDate,GrnDate))<= 3 then '45%'
when (datediff(day,DeliverbyDate,GrnDate))> 3 and (datediff(day,DeliverbyDate,GrnDate)) <= 7 then '35%'
when (datediff(day,DeliverbyDate,GrnDate))> 7 and (datediff(day,DeliverbyDate,GrnDate)) <= 10 then '20%'
else
'0%'

end as 'delivery'
FROM GRNDetail

No comments:

Blog Archive

Contributors