ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 254.8k

How to substract Balance from RequiredAmount in sql query

Sep 16 2018 10:17 PM
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 
  1. select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees  
  2. from  
  3. (  
  4. select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,Convert(varchar,CurrentMeterReading) as CurrentMeterReading,Convert(varchar,LastMeterReading) as LastMeterReading  
  5. ,Convert(varchar,CurrentConsumption) as CurrentConsumption,Convert(varchar,CurrentConsumptionValue) as CurrentConsumptionValue ,Convert(varchar,VATValue) as VATValue,  
  6. Convert(varchar,CleaningFees) as CleaningFees from WAHInvoice invo where 1=1 AND year=2018 AND  BranchCode = 1/* and Month=5  and UnitCode=5639*/  
  7. union  
  8. SELECT VTargetDetail.BranchCode ,VTargetDetail.SubLdgCode as UnitCode ,  
  9. (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  
  10.                                 FROM         dbo.VTargetDetail LEFT OUTER JOIN  
  11.                               dbo.TypeTrxSafe ON dbo.VTargetDetail.BranchCode = dbo.TypeTrxSafe.BranchCode AND dbo.VTargetDetail.TrxTypeCode = dbo.TypeTrxSafe.SafeTrxTypeCode   
  12.                               WHERE     (1 = 1) AND (VTargetDetail.TrxDate <=  '2018/09/17'AND (VTargetDetail.SubLdgCodeType = 28) /*AND (VTargetDetail.SubLdgCode = 5639) */  
  13.                               group by VTargetDetail.SubLdgCode,VTargetDetail.BranchCode  
  14.                               )as INV where Month=5 and UnitCode=5639  

Answers (2)