Hi,
Check this...
select *,
CASE WHEN listedprice=0 THEN round(((OverListedPrice)-1)*100,3) ELSE round(((OverListedPrice/listedprice)-1)*100,3) END as overlistedpercentage,
CASE WHEN closingprice=0 THEN Round(((NetGainorLoss)-1*100),3) ELSE Round(((NetGainorLoss/closingprice)-1*100),3) as netgainpercentage
from
(
select c.BranchName as brname,
c.BranchCode as brcode,
c.listedprice as listedprice,
c.ClosingPrice as closingprice,
d.CurrentPrice,
cast(d.CurrentPrice as decimal)-cast(c.ListedPrice as decimal) as OverListedPrice,
cast(d.CurrentPrice as decimal)-cast(c.ClosingPrice as decimal) as NetGainorLoss
from
(
select a.branchname,
a.branchcode,
a.listedprice,
b.closingprice
from
(
select (BREMPRONAME)as BranchName,
(BREMPROCODE)as BranchCode,
(price) as ListedPrice
from Dataupload
where Date='05/Jul/2015' and REGIONCODE='025'
)a
left join
(
select
bremprocode,
(price)as ClosingPrice
from
Dataupload
where
REGIONCODE='025'
and
MONTH(date)=MONTH(GETDATE()-1)
and day(date)=day(getdate()-1)
and time='7 PM'
)b
on a.branchcode=b.bremprocode)
c left join
(
select bremprocode,
(PRICE)as CurrentPrice
from
Dataupload
where
id in(select id from( select MAX(D.ID)as id,D.BREMPROCODE from dataupload D
where D.BREMPROCODE IN(SELECT BREMPROCODE FROM Dataupload
WHERE REGIONCODE='025')
GROUP BY D.BREMPROCODE)x))d
on c.branchcode=d.bremprocode) p
Hope this will help you.
Cheers