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