How to substract balance of customer from amount due for invoice and collect result on one query

Question

1.00/5 (1 vote)

See more:

Problem

How to make query Substract AmountDue For Invoice from Customer Balance .

Balance Customer query :

SQL

SELECT VTargetDetail.BranchCode ,VTargetDetail.SubLdgCode as UnitCode ,(SUM(dbo.VTargetDetail.Debit * dbo.VTargetDetail.CurrencyRate)  -  SUM(dbo.VTargetDetail.Credit * dbo.VTargetDetail.CurrencyRate)  ) as Balance
                                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

AmountDue For Invoice query as following :

SQL

select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees from WAHInvoice where 1=1 AND year=2018 AND  BranchCode = 1 and Month=5 and UnitCode=5639

How to substract Balance from First Query represent Balance Customer query

From

RequiredAmount on second Query AmountDue For Invoice query

Meaning Balance - RequiredAmount

and I need result display on one query .

SQL

this query above must return one record meaning final result  one record

BranchCode  UnitCode  Difference

1                   5639      CustomerBalance - requiredamount

How To Do That by union

What I have tried:

select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees 
from
(
select BranchCode,UnitCode,RequiredAmount, Serial,Year,Month,CurrentReadingDate,CurrentMeterReading,LastMeterReading,CurrentConsumption,CurrentConsumptionValue ,VATValue,CleaningFees from WAHInvoice 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

I face more problem

Error converting data type varchar to numeric.

also how to get substract balance field from second query - reuired amount from first query

Posted 16-Sep-18 14:52pm

ahmed_sa

Add a Solution

Comments

ahmed_sa 16-Sep-18 22:32pm

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
my question how to select Balance alias from inside union

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)