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.
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 CreditAmount26 03/03/2014 101 1000 1000My 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=101My Output:BillDate BillAmount AmountReceived DueAmount03/03/2014 1000 0 003/03/2014 1000 500 003/03/2014 1000 300 003/03/2014 1000 200 0Exact Output:BillDate BillAmount AmountReceived DueAmount03/03/2014 1000 0 100003/03/2014 1000 500 50003/03/2014 1000 300 20003/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=101GROUP BY BillDate, NetAmountCheersMIK |
|
|
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".... |
|
|
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.CheersMIK |
|
|
pradeepbliss
Starting Member
28 Posts |
Posted - 2014-03-05 : 08:49:48
|
My Incorrect Output:BillDate BillAmount AmountReceived DueAmount03/03/2014 1000 0 003/03/2014 1000 500 003/03/2014 1000 300 003/03/2014 1000 200 0Sales 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 DueAmount03/03/2014 1000 0 100003/03/2014 1000 500 500 03/03/2014 1000 300 20003/03/2014 1000 200 0 |
|
|
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!CheersMIK |
|
|
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 BillMasterId03/03/2014 101 500 2603/03/2014 101 300 2603/03/2014 101 200 26sales 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'... |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2014-03-06 : 11:48:15
|
How about this ?;With BillDetailsas ( 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 RemainingBalancefrom Billdetails AP.S. I would prefer this sort of calculation is handled on the front end by the application. :)CheersMIK |
|
|
|
|
|
|
|