select qh.CaseId
,(select sh.CaseId from ServiceQuot.dbo.Header sh where qh.QutoNo = sh.HeaderNo)
,qh.ApplierDate
,qh.BU
,qh.Site
, qh.HeaderNo
, qh.Currency
(select sh.Customer from ServiceQuot.dbo.Header sh where qh.QutoNo = sh.HeaderNo)
, qh.PN
,qh.QutoNo
,(select sum(ql.Num) from QAPriceContrast.dbo.Line ql where ql.CaseId = qh.CaseId)
,( select ( select sdl.SPMCost from ServiceQuot.dbo.Line sdl where qh.QutoLineId = sdl.LineId and shf.CaseId = sdl.CaseId) SpmCost from ServiceQuot.dbo.Header shf where qh.QutoNo = shf.HeaderNo )
,qh.CalQutoHumanCost
,qh.CalQuotMaterilCost
,qh.CalQuotOtherPrice
,qh.CalQutoCostCount
,qh.CalActualHumanCost
,qh.CalActualMaterialCost
,qh.ActualOtherPrice
,qh.CalActualCostCount
,qh.CalQutoHumanCost - qh.CalActualHumanCost
,qh.CalQuotMaterilCost - qh.CalActualMaterialCost
,qh.CalQuotOtherPrice - qh.ActualOtherPrice
,(qh.CalQutoHumanCost - qh.CalActualHumanCost) + (qh.CalQuotMaterilCost - qh.CalActualMaterialCost) + (qh.CalQuotOtherPrice - qh.ActualOtherPrice)
, CONCAT( cast (((qh.CalQutoCostCount - qh.CalActualCostCount) / qh.CalActualCostCount) * 100 as numeric(10, 2)),'%')
,'http://172.xx.xx.xxx/xxx/SPMCSWebEFormEmpty/FrmTempSearch.aspx?CASEID=' +Convert(nvarchar(100),qh.CaseId) AS link_address
from QAPriceContrast.dbo.Header qh where qh.CalActualCostCount is not null
