Subtract values from two Tables when no records in SQL Server

alhamd

SOLVED CLOSED ACTIVE ANSWERED
User: alhamd
Posted: on Sep 05, 2023 01:52 AM
Forum: SQL Server
Answer: 1
Views: 564

tblPurchaseTran

Product	P_Price	P_Head
A	100	Fixed Asset
B	200	Fixed Asset
C	200	Expenditure

tblSaleTran

Product	P_Price	P_Head

There is no value in the tblSaleTran. I wrote the following to subtract values from one table to another based on some condition.

DECLARE @Asset INT
SET @Asset = ((select Sum(P_Price) as Amount from tblPurchaseTran where P_Head='Fixed Asset') - (select Sum(P_Price) as Amount from tblSalesTran
where S_Head='Fixed Asset'))
SELECT 'Fixed Asset', @Asset

It is showing me result

Fixed Asset Null

Whereas it should display 300. As tblPurchase has value 300 based on condition defined in the query.

300 – 0 = 300

How to get solution plz.

Fixed Asset 300

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Sep 05, 2023 02:00 AM
Report

Hi alhamd,

use ISNULL function.

Refer below query.

SQL

DECLARE @tblPurchaseTran AS TABLE(Product CHAR(10),P_Price INT,P_Head VARCHAR(50))
INSERT INTO @tblPurchaseTran VALUES('A',100,'Fixed Asset')
INSERT INTO @tblPurchaseTran VALUES('B',200,'Fixed Asset')
INSERT INTO @tblPurchaseTran VALUES('C',200,'Expenditure')

DECLARE @tblSaleTran AS TABLE(Product CHAR(10),P_Price INT,P_Head VARCHAR(50))	

DECLARE @Asset INT
SET @Asset = (ISNULL((select Sum(ISNULL(P_Price,0)) as Amount from @tblPurchaseTran where P_Head='Fixed Asset'),0) - ISNULL((select Sum(ISNULL(P_Price,0)) as Amount from @tblSaleTran where P_Head='Fixed Asset'),0))
SELECT 'Fixed Asset' 'Asset', @Asset 'Price'

Output

Asset	Price
Fixed Asset	300

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Subtract values from two Tables when no records in SQL Server

Company

Explore

Follow us

Contact Us