How to substract Balance from RequiredAmount in sql query

ahmed elbarbary

8y882

Proplem

How to substract Balance from RequiredAmount in sql query .

when write balance on first select it not defined

actually i need in first select Balance(alias)inside union - RequiredAmout in WahInvoice Table

select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees
from
(
select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,Convert(varchar,CurrentMeterReading) as CurrentMeterReading,Convert(varchar,LastMeterReading) as LastMeterReading
,Convert(varchar,CurrentConsumption) as CurrentConsumption,Convert(varchar,CurrentConsumptionValue) as CurrentConsumptionValue ,Convert(varchar,VATValue) as VATValue,
Convert(varchar,CleaningFees) as CleaningFees from WAHInvoice invo where 1=1 AND year=2018 AND BranchCode = 1/* and Month=5 and UnitCode=5639*/
union
SELECT VTargetDetail.BranchCode ,VTargetDetail.SubLdgCode as UnitCode ,
(SUM(dbo.VTargetDetail.Debit * dbo.VTargetDetail.CurrencyRate) - SUM(dbo.VTargetDetail.Credit * dbo.VTargetDetail.CurrencyRate) ) as Balance ,'' AS Serial ,'' as YEAR,'' AS Month,'' as CurrentReadingDate,'' as CurrentMeterReading,'' as LastMeterReading,'' as CurrentConsumption,'' as CurrentConsumptionValue,'' as VATValue,'' as CleaningFees
FROM dbo.VTargetDetail LEFT OUTER JOIN
dbo.TypeTrxSafe ON dbo.VTargetDetail.BranchCode = dbo.TypeTrxSafe.BranchCode AND dbo.VTargetDetail.TrxTypeCode = dbo.TypeTrxSafe.SafeTrxTypeCode
WHERE (1 = 1) AND (VTargetDetail.TrxDate <= '2018/09/17') AND (VTargetDetail.SubLdgCodeType = 28) /*AND (VTargetDetail.SubLdgCode = 5639) */
group by VTargetDetail.SubLdgCode,VTargetDetail.BranchCode
)as INV where Month=5 and UnitCode=5639

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
hi,
when you use union, all the select statement has to be same nomber of fields and data types, if you do not have any field in the select statement then give dummy data as
@Piyush Pansuriya has mentioned
Piyush Pansuriya8y ago
Hi Ahmed,

You have to make same columns in both select query in Union.
select BranchCode,UnitCode, Balance,RequiredAmount, Balance-RequiredAmount) As Diff, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees
from
(
select BranchCode,UnitCode,0 as Balance,RequiredAmount, Serial,Year,Month,CurrentReadingDate,Convert(varchar,CurrentMeterReading) as CurrentMeterReading,Convert(varchar,LastMeterReading) as LastMeterReading
,Convert(varchar,CurrentConsumption) as CurrentConsumption,Convert(varchar,CurrentConsumptionValue) as CurrentConsumptionValue ,Convert(varchar,VATValue) as VATValue,
Convert(varchar,CleaningFees) as CleaningFees from WAHInvoice invo where 1=1 AND year=2018 AND BranchCode = 1/* and Month=5 and UnitCode=5639*/
union
SELECT VTargetDetail.BranchCode ,VTargetDetail.SubLdgCode as UnitCode ,
(SUM(dbo.VTargetDetail.Debit * dbo.VTargetDetail.CurrencyRate) - SUM(dbo.VTargetDetail.Credit * dbo.VTargetDetail.CurrencyRate) ) as Balance, 0 as RequiredAmount ,'' AS Serial ,'' as YEAR,'' AS Month,'' as CurrentReadingDate,'' as CurrentMeterReading,'' as LastMeterReading,'' as CurrentConsumption,'' as CurrentConsumptionValue,'' as VATValue,'' as CleaningFees
FROM dbo.VTargetDetail LEFT OUTER JOIN
dbo.TypeTrxSafe ON dbo.VTargetDetail.BranchCode = dbo.TypeTrxSafe.BranchCode AND dbo.VTargetDetail.TrxTypeCode = dbo.TypeTrxSafe.SafeTrxTypeCode
WHERE (1 = 1) AND (VTargetDetail.TrxDate <= '2018/09/17') AND (VTargetDetail.SubLdgCodeType = 28) /*AND (VTargetDetail.SubLdgCode = 5639) */
group by VTargetDetail.SubLdgCode,VTargetDetail.BranchCode
)as INV where Month=5 and UnitCode=5639

How to substract Balance from RequiredAmount in sql query

@Piyush Pansuriya has mentioned