How to substract Balance Of Customer from amount Due for Invoice and collect result on one query
-
- Problem
How to make query Substract AmountDue For Invoice from Customer Balance
Balance Customer query :
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.BranchCodeAmountDue For Invoice query as following :
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 .
How to do that please .
this query above must return one record meaning final result one recordBranchCode UnitCode Difference
1 5639 CustomerBalance - requiredamount
How to do that by union
- Problem
-
Sorry, my psychic powers are off for the weekend. What database are you using? It sounds like a sample MSFT database, but since you don't specify, I'm not sure.
Where/how is the Customer's balance determined? -
can you help me please
query what i do as following
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 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
actually how to get substract between Required amount field in table wahinvoice and Balance alias that represent customer balance -
Oh, come on. What database is this coming from???
It looks like it's one of the Microsoft ones, but which one? Is it the 2014 version of AdventureWorks?Without knowing what database it comes from, or having CREATE TABLE and INSERT statements to recreate your design/problem, it's hard to answer.
What if you do something like multiply one of the sets by -1 and then UNION them and then use an aggregate query?
this query above must return one record meaning final result one recordViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply