mercredi 4 mai 2016

Subtacting two coloums within the sql query

I have been trying to subtract two coloums in sql server to form a third one.Below is my query

select  AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate 
from AdvantageDetails AD 
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id

What I tried is below but it is not working. :

select  AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate , (TotalDue-AllocatedToDate) as NewColumn
from AdvantageDetails AD 
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id

At last I tried it using a CTE which worked fine. But I want to do it without creating CTE. Can there be any other way for performing the same functionality. I do not want to use CTE because it is forcasted that there can be other columns which will be calculated in future.

with CTE as(select  AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate , (TotalDue-AllocatedToDate) as NewColumn
from AdvantageDetails AD 
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id) select * , (CTE.TotalDue-CTE.AllocatedToDate)As Newcolumn from CTE

Aucun commentaire:

Enregistrer un commentaire