2 Replies - 238 Views - Last Post: 16 September 2018 - 09:24 PM

#1 ahmedba   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 220
  • Joined: 24-January 14

How to substract Balance from RequiredAmount in sql query

Posted 16 September 2018 - 08:46 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
meaning select Balance - requiredamount from ()
i need it on first select

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  


Is This A Good Question/Topic? 0
  • +

Replies To: How to substract Balance from RequiredAmount in sql query

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15360
  • View blog
  • Posts: 61,603
  • Joined: 12-June 08

Re: How to substract Balance from RequiredAmount in sql query

Posted 16 September 2018 - 08:50 PM

Quote

i need it on first select

What does this mean?
Was This Post Helpful? 0
  • +
  • -

#3 ahmedba   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 220
  • Joined: 24-January 14

Re: How to substract Balance from RequiredAmount in sql query

Posted 16 September 2018 - 09:24 PM

How to write query as below
select RequireAmount-Balance from 
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 


This post has been edited by ahmedba: 16 September 2018 - 09:26 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1