CODEWITHSUNDEEP
sql-serverdatabaseselect
Richard Gale
Richard Gale

Reputation: 1952

SQL Select Data

I have a table set up, it is a customers' repayment schedule for a product.

My table consists of the following fields:

SaleID | PaymentDueDate | AmountDue

What I need for each Sale is:

SaleID | FirstPaymentDate | FirstPaymentValue | RegularPaymentValue | FinalPaymentvalue

Each repayment schedule has the following characteristics:

First payment amount is defined by the customer. Regular and final payments are dictated by the balance remaining.

e.g

Sale = £200 Term = 14 Weeks (14 Payments)

First Payment = £50 (x1)

Regular Payment = £12 (x12)

Final Payment = £6 (x1)

Please can you advise how I can retrieve what I need.

ADDITION: Code So Far:

This is what I have achieved so far, but this only gets me the First Payment and First Payment Date for each Sale:

SELECT      
FP.SaleID, FP.FirstPaymentDate, RS.AmountDue AS FirstPayment 
FROM    
(       
SELECT SaleID, MIN(PaymentDueDate)AS FirstPaymentDate       
FROM RepaymentSchedule          
GROUP BY SaleID     
) AS FP 
LEFT OUTER JOIN
RepaymentSchedule AS RS ON FP.SaleID = RS.SaleID AND FP.FirstPaymentDate =   RS.PaymentDueDate ORDER BY RS.SaleID

ADDITION: Data Sample: £280 over 14 Weeks

SaleID  PaymentDueDate            AmountDue
41      2012-08-29 00:00:00.000   120.00
41      2012-09-05 00:00:00.000   12.30
41      2012-09-12 00:00:00.000   12.30
41      2012-09-19 00:00:00.000   12.30
41      2012-09-26 00:00:00.000   12.30
41      2012-10-03 00:00:00.000   12.30
41      2012-10-10 00:00:00.000   12.30
41      2012-10-17 00:00:00.000   12.30
41      2012-10-24 00:00:00.000   12.30
41      2012-10-31 00:00:00.000   12.30
41      2012-11-07 00:00:00.000   12.30
41      2012-11-14 00:00:00.000   12.30
41      2012-11-21 00:00:00.000   12.30
41      2012-11-28 00:00:00.000   12.40

I guess the FinalPaymentValue is not too crucial.

Really I just need to know how to work out what the regular payment is.

Upvotes: 0

Views: 123

Answers (1)

Kermit
Kermit

Reputation: 34054

This should work:

SELECT pd.saleid,
       pd.firstpaymentdate                               AS FirstPaymentDate,
       (SELECT amountdue
        FROM   repaymentschedule
        WHERE  saleid = pd.saleid
               AND paymentduedate = pd.firstpaymentdate) AS FirstPaymentValue,
       (SELECT TOP 1 amountdue
        FROM   repaymentschedule
        WHERE  saleid = pd.saleid
               AND paymentduedate <> pd.firstpaymentdate
               AND paymentduedate <> pd.lastpaymentdate) AS RegularPaymentValue,
       (SELECT amountdue
        FROM   repaymentschedule
        WHERE  saleid = pd.saleid
               AND paymentduedate = pd.lastpaymentdate)  AS FinalPaymentValue
FROM   (SELECT saleid,
               Min(paymentduedate) AS FirstPaymentDate,
               Max(paymentduedate) AS LastPaymentDate
        FROM   repaymentschedule
        GROUP  BY saleid) pd

See it in action

Sample Result:

| SALEID |              FIRSTPAYMENTDATE | FIRSTPAYMENTVALUE | REGULARPAYMENTVALUE | FINALPAYMENTVALUE |
--------------------------------------------------------------------------------------------------------
|     41 | August, 29 2012 00:00:00+0000 |               120 |                12.3 |              12.4 |

Upvotes: 1

Related Questions