C#•2y ago

❔ SQL Server | Count How Much Installment Paid and Left

#ask #query # sql server hi i have a question, last week my teacher give a task to select data from table claim and count how much installment already paid and left in SQL Server, My teacher said claim is paid every month until Claim.LastPaymentDate im already done to arrange the data like the first image but im really confuse to count how much installment paid and left, already searching in google but i cant find the correct answer, is anyone know to do that ?

select 
    p.POLICYNO as PolicyNo
    , c.CLAIMNO as ClaimNo
    , c.PARENTID as ParentId
    , p.Name as CustomerName
    , '' as Installemnt
    , '' as RemainingInstallment
        , c.PaymentDate
        , c.LastInstallmentdate
from Claim c
    inner join policy p on c.policyno = p.policyno
order by p.POLICYNO, c.PaymentDate, c.ClaimNo ,c.PARENTID asc

select 
    p.POLICYNO as PolicyNo
    , c.CLAIMNO as ClaimNo
    , c.PARENTID as ParentId
    , p.Name as CustomerName
    , '' as Installemnt
    , '' as RemainingInstallment
        , c.PaymentDate
        , c.LastInstallmentdate
from Claim c
    inner join policy p on c.policyno = p.policyno
order by p.POLICYNO, c.PaymentDate, c.ClaimNo ,c.PARENTID asc

9 Replies

Saber•2y ago

what are you even googling for this? It sounds like a simple math question, use the created/last dates to calculate how many months there are. Then use the payment date to determine how many months are left/paid and with that you can determine how much has been paid and is left

FR•2y ago

sry i didnt make it a clear, so the real issue is how do i count it in SQL Server ? so if im using c#

- im create a array to save the result
- select the policy into array and the looping it
[Loop]
- - select claim based on policyno and looping it 
- - [Loop]
- - - use i and i++ to count how much claim paid
- - - like u say use diff month between Claim.PaymentDate to Claim.LastPaymentDateto count how much claim left
- - - store data to the result
- - [End Loop]
[End Loop]

- im create a array to save the result
- select the policy into array and the looping it
[Loop]
- - select claim based on policyno and looping it 
- - [Loop]
- - - use i and i++ to count how much claim paid
- - - like u say use diff month between Claim.PaymentDate to Claim.LastPaymentDateto count how much claim left
- - - store data to the result
- - [End Loop]
[End Loop]

but in SQL Server im really confuse to do it

Saber•2y ago

it is clear. you just need to do some simple date math to calculate how many installments there are, and how many have been paid

FR•2y ago

yeahhh..... lets change a question a little bit, so u know how to store a selected data into array in SQL Server and Looping it?

Saber•2y ago

why would you need to store anything into an array here and loop it?

FR•2y ago

im just thinking to do the same method in c# to SQL Server and do a bunch of select an inserting my brain cant think of doing it with only a single select query

Saber•2y ago

Installments = months between created/last RemainingInstallments = (months between created/last) - (months between created/payment) seems simple enough

FR•2y ago

just rest my brain a bit and some smol research, by using datediff and subquery i finally understand what u say here my final query

select 
    p.POLICYNO as PolicyNo
    , c.CLAIMNO as ClaimNo
    , c.PARENTID as ParentId
    , p.Name as CustomerName
    , case 
        when c.parentid is null then 1
        else datedif(month, (select paymentdate from claim where claimno = c.parentid), c.paymentdate) + 1
      end as Installemnt
    , datediff(month,c.paymentdate, c.LastPaymentDate) as RemainingInstallment
        , c.PaymentDate
        , c.LastInstallmentdate
from Claim c
    inner join policy p on c.policyno = p.policyno
order by p.POLICYNO, c.PaymentDate, c.ClaimNo ,c.PARENTID asc

select 
    p.POLICYNO as PolicyNo
    , c.CLAIMNO as ClaimNo
    , c.PARENTID as ParentId
    , p.Name as CustomerName
    , case 
        when c.parentid is null then 1
        else datedif(month, (select paymentdate from claim where claimno = c.parentid), c.paymentdate) + 1
      end as Installemnt
    , datediff(month,c.paymentdate, c.LastPaymentDate) as RemainingInstallment
        , c.PaymentDate
        , c.LastInstallmentdate
from Claim c
    inner join policy p on c.policyno = p.policyno
order by p.POLICYNO, c.PaymentDate, c.ClaimNo ,c.PARENTID asc

is there a way to improve this query ?

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ SQL Server | Count How Much Installment Paid and Left