Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inner Join Based On Conditional Column in Sql

Author  Topic 

pradeepbliss
Starting Member

28 Posts

Posted - 2014-03-05 : 04:08:31
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....

Sales Table:

BillMasterId BillDate CustomerId NetAmount CreditAmount

26 03/03/2014 101 1000 1000

My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101


My Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 0

03/03/2014 1000 500 0

03/03/2014 1000 300 0

03/03/2014 1000 200 0

Exact Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 1000

03/03/2014 1000 500 500

03/03/2014 1000 300 200

03/03/2014 1000 200 0

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-05 : 06:35:57
the fields, billdate, billamount, voucherAmount, creditAmount,CustomerID belongs to which tables? Can you replace the "tableName" in the following query with the table's names to which the field belongs to and check if you're getting desired result ?

SELECT
BillDate = CONVERT(varchar,tableName.BillDate,103)
,BillAmount = isnull(tableName.NetAmount,0)
,AmountReceived = SUM(ISNULL(tableName.VoucherAmount,0))
,DueAmount = SUM(ISNULL(tableName.CreditAmount,0))
FROM Voucher
INNER join Sales on tableName.CustomerId=tableName.CustomerID
and tableName.BillMasterID=tableName.BillMasterID
WHERE CONVERT(varchar,tableName.BillDate,103)='03/03/2014'
AND tableName.CustomerId=101
GROUP BY BillDate, NetAmount

Cheers
MIK
Go to Top of Page

pradeepbliss
Starting Member

28 Posts

Posted - 2014-03-05 : 07:46:34
No I am not able to get desired result,if we take column 'CreditAmount' for calculating "DueAmount" Means it takes last row value of the column'CreditAmount' for entire result of "DueAmount"....
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-05 : 07:54:47
then provide a sample data for (at for a couple of cases) and the desired output for them. That would help us.

Cheers
MIK
Go to Top of Page

pradeepbliss
Starting Member

28 Posts

Posted - 2014-03-05 : 08:49:48
My Incorrect Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 0

03/03/2014 1000 500 0

03/03/2014 1000 300 0

03/03/2014 1000 200 0


Sales Table:

BillMasterId BillDate CustomerId NetAmount CreditAmount

26 03/03/2014 101 1000 0

Though customer have settled all corresponding dueamount 500,300,200 for each transaction i am updating 'CreditAmount' column in sales table 1000,500,300,200 so finally 'CreditAmount' column will be updated to 0

Exact Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 1000

03/03/2014 1000 500 500

03/03/2014 1000 300 200

03/03/2014 1000 200 0
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-05 : 09:06:35
I can't use just one record of a sales table to get the desired ouput.

Provide sample data for both tables Voucher and sales, since the output is based on both. And, provide it as following e.g.

Declare @Voucher table (columnanmes....)
declare @sales Table (columnNames....)
Insert into @voucher values ....
Insert into @voucher values ....
Insert into @voucher values ....

Insert into @Sales values ....
Insert into @Sales values ....
Insert into @Sales values ....
Insert into @Sales values ....

hope that makes sense? thanks!

Cheers
MIK
Go to Top of Page

pradeepbliss
Starting Member

28 Posts

Posted - 2014-03-05 : 09:44:11
declare @sales(BillMasterId varchar(50),BillDate varchar(50) ,CustomerId int ,NetAmount decimal(18,2),CreditAmount decimal(18,2))
declare @voucher(BillDate varchar(50),CustomerId int ,VoucherAmount decimal(18,2), BillMasterId int)

insert into sales(BillMasterId,BillDate ,CustomerId ,NetAmount ,CreditAmount)values(26,03/03/2014,101,1000,1000)

insert into voucher(BillDate CustomerId VoucherAmount BillMasterId)
values(03/03/2014,101, 500, 26)
insert into voucher(BillDate CustomerId VoucherAmount BillMasterId)
values(03/03/2014 101 300 26)
insert into voucher(BillDate CustomerId VoucherAmount BillMasterId)
values(03/03/2014 101 200 26)

Voucher Table :

BillDate CustomerId VoucherAmount BillMasterId

03/03/2014 101 500 26

03/03/2014 101 300 26

03/03/2014 101 200 26

sales table contains only one record,customer have kept credit during sales with sum of 1000 that so "NetAmount" means BillAmount(sales) will be 1000 and "CreditAmount" will also be 1000 once when customer pays DueAmount in 'VoucherAmount'Column(Voucher) then i am updating remaining dueamount for that customer in 'Sales' table column 'CreditAmount' hence the process keep on moving till the customer settles the Dueamount, so finally dueamount will be updated to 0 in 'Sales' table column 'CreditAmount'...
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-06 : 11:48:15
How about this ?

;With BillDetails
as (
SELECT CustomerID,BillDate,NetAmount,0 as VoucherAmount,0 as Seq FROM @sales
UNION
SELECT B.CustomerID,B.BillDate,A.NetAmount,isnull(b.VoucherAmount,0) VoucherAmount,Row_Number () over(Partition by B.CustomerID order by B.BillDate) as Seq
FROM @sales A
INNER JOIN @Voucher B on A.CustomerID=B.CustomerID
)
select A.BillDate,A.NetAmount,a.VoucherAmount,a.NetAmount - (SELECT sum(isnull(VoucherAmount,0)) FROM Billdetails B WHERE B.CustomerID = A.CustomerID and B.Seq<A.Seq+1) As RemainingBalance
from Billdetails A


P.S. I would prefer this sort of calculation is handled on the front end by the application. :)

Cheers
MIK
Go to Top of Page
   

- Advertisement -