How to Identify collection amount is against which due amount

Question

How to Identify collection amount is against which due amount

avdhut.k

SSCrazy

Points: 2834
More actions
March 4, 2014 at 11:46 pm

#284187

Hi
Below is the table related to my requirement,
For a lnno, I had Due and collection amount,
For eg, first due is on '2013-01-07' of 100 rupees,and collection of 10 Rs on same date,
so collection is adjusted against that due in collection_adj column,
In same way, 30 Rs '2013-01-12' ,so 30 Rs is adjusted against that due in collection_adj column,
Similarly for Rs 40.
But On '2013-02-07' Due of 100 Rs is generated,and collection is of 50 Rs,out which 20 Rs is adjusted against the first remaining (20) due
in collection_adj column,
and 30 Rs is adjusted against the next Due.
My Requirement is, I want to update the Identifier Column,to identify the collection is against which due.
create table #Temp
(
lnno varchar(15),
Due Int,
Duedate Date,
Collections Int,
Collections_Adj Int,
coll_date date,
identifier Int
)
Insert Into #Temp
values (1,100,'2013-01-07',10,10,'2013-01-08',1),
(1,0,NULL,30,30,'2013-01-12',1),
(1,0,NULL,40,40,'2013-01-20',1),
(1,100,'2013-02-07',50,20,'2013-02-10',1),
(1,100,'2013-02-07',0,30,'2013-02-10',2)
Select * from #Temp

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Nice job posting ddl and sample data.

The reason that nobody has offered any advice yet is because your question is totally unclear. This to me looks like a denormalized table that contains some sort of amount owed and the collections made against that amount. There is no primary key that I can see. Last but not least you mentioned something about updating the "identifier" column. What is that column and what are you trying to do?

You need to provide a lot more details about the process and what you want to do here before anybody can offer much advice.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I'd agree with Sean - the denormalization is making life complicated. From what I can ascertain from the data, you have 3 separate entities, all being tracked in the one table. In the same table you seem to be tracking invoices, payments, and how the payments are applied to said invoices.

Create table #Invoice -- what is due when

(

invID int primary key,

lnno varchar(15),

Due Int,

Duedate Date

)

Create table #Payments --what you are calling collections

(

CollectionID int primary key,

Collections Int,

coll_date date

)

Create table #PaymentsApplied -- how the payments are applied to the invoices

(

InvID int,

CollectionID int,

CollectionApplied int --this is what you called collections_adj

)

If you don't separate them in some way - it is going to be difficult to accurate track receivables vs payments. Keeping them in a single table is frankly going to lead to bloating of the DB, given how much data needs to continually be duplicated. You're also missing at least one column to accurately show how payments apply (since there is no such thing as physical order in a DB, you need to explicitly ID each of the above entities).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

avdhut.k SSCrazy Points: 2834 More actions · Answer 3

Hi,

Thanks for the suggestions,

I just wanted to understand,how to identify the payment recieved is against which payment Due.

create table #Temp

(

lnno varchar(15),

Due Int,

Duedate Date,

Collections Int,

Collections_Adj Int,

coll_date date,

identifier Int

)

Insert Into #Temp

values (1,100,'2013-01-07',10,10,'2013-01-08',1),

(1,0,NULL,30,30,'2013-01-12',1),

(1,0,NULL,40,40,'2013-01-20',1),

(1,100,'2013-02-07',50,20,'2013-02-10',1),

(1,100,'2013-02-07',0,30,'2013-02-10',2)

Select * from #Temp

1) In the above example,on 07 Jan Due of 100 is hundered is generated,

then on 8 Jan Rs 10 is collected against 100 on 07 Jan

then on 12 Jan Rs 30 is collected against 100 on 07 Jan

then on 20 Jan Rs 40 is collected against 100 on 07 Jan

2)And Then ,on 10 Feb, Due of 100 RS is hundered is generated,

then on 10 Feb Rs 50 is collected,

Now,at this point,Rs 20 is collected against first 100 Due hence 1 is updated in Identifir Column

and remaining 30 Rs is collected against next due,hence 2 is updated.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Here is where this type of architecture really starts to break down. What happens if you have a row where the Collections would overpay the Due? Which one do you assign it to and why? To be honest, you will never keep this straight in this design. You have two types of rows in the same table. The other issue is you have nothing like a primary key. You have a column named Inno but all the values are the same. You say you want to update identifier but what are you going to update to?

In your sample the last row has 2 as the identifier. What does the 2 represent? There is no 2 anywhere else in your sample data.

My recommendation would be to split this into two tables (Loans and Payments).

Something like this.

create table #Loans

(

LoanID int primary key clustered,

AmountBorrowed numeric(9,2) not null,

DueDate datetime not null

)

create table #Payments

(

PaymentID int primary key clustered,

LoanID int not null, --in your real table this would be a foreign key to Loans

PaymentAmount numeric(9,2) not null,

PaymentDate datetime not null

)