Click here to Skip to main content
15,846,899 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have number of loan related data in sql database and I want to process each records based on some logic. Logic and sample is as follows. I want to split EMI amount in multiple days/months slab

I have One record like.
Ac Balance     EMI     CurrentDate CloseDate 
1  488568.00   5512    08-04-2022  08-04-2026


OutPut required

1-30Days 30-90Days 90-180days 6-12Months 13-36Months  36-60Months  Total
5512     11024     16536      33072      137800       284626       488568


I have around 8000 records.

What I have tried:

Dont have much clue to start with store procedure or function
Posted
Updated 19-Aug-22 3:42am
v2

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]
 
Share this answer
 
In addition to Solution 1 ...
You have not described your logic and it is not clear what your logic is to get to those figures.

You seem to
- switch between a 30 day month and a calendar month (I am assuming that EMI is the amount to accumulate each month / 30 days)
- switch between applying the figures to the beginning of the range then to the middle (by the methods I tried 137800 is reached in month 24 (or 25 depending on my first point) and not month 13 nor 36
- be inconsistent in your ranges 6-12 months is followed by 13-36 months, but then that is followed by 36-60 months

Quote:
Dont have much clue to start with store procedure or function
Don't start with either!

Start by working out how you are going to get those numbers (or the correct numbers)

Now some suggestions on how you might start to code it up
- You will need a lists of Dates (often called a Dimension Table - here is an example Creating a Date Dimension Table - Overview, Importance, Methods[^] (Warning - pop up offering free courses, ignore and dismiss it)
- You will probably want to have a cross join between that table and your data table (see Cross Join Introduction – Create Row Combinations[^] )
- To get the data in the format you need you will need to know about Pivot Simple Way To Use Pivot In SQL Query[^] and GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
v2
Comments
Hemil Gandhi 19-Aug-22 8:50am    
I have tried the following , any other / faster way to do it??
create table #alm
(
acno varchar(10)
,emi numeric(10)
,balance numeric(10)
,cdate date
,ldate date
,dy numeric(10)
,"2D-7D" numeric(10)
,"8D-14D" numeric(10)
,"15D-28D" numeric(10)
,"29D-89D" numeric(10)
,"90D-6M" numeric(10)
,"6M-12M" numeric(10)
,"12M-36M" numeric(10)
,"36M-60M" numeric(10)
,"Abv60M" numeric(10))

insert into #alm(acno,emi,balance,cdate,ldate) values (1,5512,488568.00,GETDATE(),'2022-08-04')

update #alm set dy =
case when (balance/emi)<datediff(month, getdate(),="" '2025-08-04')="" then="" (balance="" emi)="" else="" datediff(day,="" '2025-10-30')="" end


update="" #alm="" set="" "15d-28d"="balance,"29D-89D"=0,"90D-6M"=0,"6M-12M"=0,"12M-36M"=0,"36M-60M"=0,"Abv60M"=0" where="" dy<="28
update" dy="">=29 and dy<=89
update #alm set "15D-28D"=emi,"29D-89D"=emi*2,"90D-6M"=balance-(emi*3),"6M-12M"=0,"12M-36M"=0,"36M-60M"=0,"Abv60M"=0 where dy>=90 and dy<=180
update #alm set "15D-28D"=emi,"29D-89D"=emi*2,"90D-6M"=emi*3,"6M-12M"=balance-(emi*6),"12M-36M"=0,"36M-60M"=0,"Abv60M"=0 where dy>=181 and dy<=365
update #alm set "15D-28D"=emi,"29D-89D"=emi*2,"90D-6M"=emi*3,"6M-12M"=emi*6,"12M-36M"=balance-(emi*12),"36M-60M"=0,"Abv60M"=0 where dy>=366 and dy<=1095
update #alm set "15D-28D"=emi,"29D-89D"=emi*2,"90D-6M"=emi*3,"6M-12M"=emi*6,"12M-36M"=emi*24,"36M-60M"=balance-(emi*36),"Abv60M"=0 where dy>=1096 and dy<=1825
update #alm set "15D-28D"=emi,"29D-89D"=emi*2,"90D-6M"=emi*3,"6M-12M"=emi*6,"12M-36M"=emi*24,"36M-60M"=emi*24,"Abv60M"=balance-(emi*60) where dy>=1826
CHill60 19-Aug-22 10:08am    
That query has spurious double-quotes and equals signs. It doesn't compile.
From first glance it won't work after that either. What results do you get from that?
Hemil Gandhi 21-Aug-22 1:08am    
Actual query is working , some how after pasting doble quote is added by system.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900