TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
278.3k
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
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
Reply
Answers (
2
)
contained database authentication failure in sql server 2014
Primary Key with 3 field By Query