SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inner Join Based On Conditional Column in Sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradeepbliss
Starting Member

5 Posts

Posted - 03/05/2014 :  04:08:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2014 :  06:35:57  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 03/05/2014 06:37:41
Go to Top of Page

pradeepbliss
Starting Member

5 Posts

Posted - 03/05/2014 :  07:46:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2014 :  07:54:47  Show Profile  Reply with Quote
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

5 Posts

Posted - 03/05/2014 :  08:49:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2014 :  09:06:35  Show Profile  Reply with Quote
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

5 Posts

Posted - 03/05/2014 :  09:44:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2014 :  11:48:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000