Selecting payments due in any one month 2

[DoctorJDM]

Hi all

Have situation where a client receives payment for orders as a schedule. The schedule can be up to 6 monthly payments, depending on the order value.

The data has to be extracted from a table that gives

OrderNo, TotalDue, FirstPaymentDate

I can get to a query that calculates the subsequent payments and due dates lik this

OrderNo, TotalDue, Payment1Date, Payment1, Payment2Date, Payment 2 etc

I'd like now to be able to extract all payments due in any one month, and create an output as below

Order No Amount Due
1007 175
1103 225
1155 190

Is this possible?

 

[AlexCuse]

A starting point:

select OrderNo, TotalDue
from myTable
where Month(Payment1Date) = [enter some date]
or Month(Payment2Date) = [enter some date]

add or's to your where clause all the way through payment 6.

I would recommend normalizing this data to contain only 1 payment per row, it would make this query (and probably many others) much more efficeint. Check this out:

http://www.r937.com/relational.html

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

whoops, [enter some date] should be [enter some month]. Lil slow this morning.

Ignorance of certain subjects is a great part of wisdom

 

[DoctorJDM]

Hi Alex, and like me you shouldn't be working on a Sunday. Anyway thanks, and I'm a lil slow too but not just this morning, more a permanent condition.

Your answer may be spot on but I'm still grappling with how, after dissecting the TotalDue for each order into monthly installments the number of which depend on the amount of TotalDue, I could

- tie a month to each installment
- then list the installments due in any selected month

To labour the point, one TotalDue may be £270 with order date of Nov 2006. The payment schedule would be
- £135 in Nov 06
- £45 in Dec 06
- £45 in Jan 07
- £45 in Feb 07

Another may be £520 in Dec 06, with payments
- £260 in Dec 06
- £65 in Jan 07
- £65 in Feb 07
- £65 in Mar 07
- £65 in Apr 07

So in Feb 07 the installments list would include

Order 1, £45
Order 2, £65
etc

 

[AlexCuse]

Ah yes. You will need a union, sorry 'bout that. Like I said, I'm slow.

select OrderNo, Payment1 as PaymentAmt
from myTable
where Month(Payment1Date) = [enter month]
and Year(Payment1Date)= [enter year]
union all
select OrderNo, Payment2
from myTable 
where Month(Payment2Date) = [enter month]
and Year(Payment2Date) = [enter year]

and so on (one query per set of payment column basically)...

A table set up like this:
OrderNo, PaymentNo, PaymentDate, PaymentAmt


could make your life a lot easier, then it would be just:

select OrderNo, PaymentAmt
from myTable
where Month(PaymentDate) = [Month]
and Year(PaymentDate) = [Year]

That's why the normalization document is recommended reading

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[DoctorJDM]

Alex

Looks very promising and will resume tomorrow.

The normalization question has been the bane of this particular project because of the way data arrives,something we have no control over.

Many thanks

 

[lespaul]

just because data arrives in a certain order doesn't mean that you have to keep it that way. There's nothing wrong with taking the incoming data and structuring it correctly before you use that data for your own purposes.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[DoctorJDM]

Okay guys, maybe I've not painted the picture properly.

The simple position is that I have a table with these fields

- OrderNo
- OrderDate
- OrderValue

ie no normalization issue.

The order value has to be split into monthly installments, done with a set of criteria according to OrderValue. The installments start in the month of OrderDate, then at monthly intervals thereafter. Installments may range from 1 to 6.

The question is how to group the payments due in any month from multiple orders (which may have been placed in different month) so I can end up with a report that shows monthly amounts due, with each Order No, for any chosen month.

What I'd started doing was to use a query to calculate payments and payment dates from the original table, leading to an output in the form

OrderNo, Payment1, PaymentDate1, Payment2, PaymentDate2, etc

Then I got stuck.

 

[PHV]

use a query to calculate payments and payment dates from the original table
Any chance you could post the SQL code of this query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DoctorJDM]

Hi PHV, this is the SQL code. There are some differences from my last description of the problem, which I'd simplified for clarity.

Instead of being in a table the data is in a query qryEPSSchedule, which contains

OrderNo
Code10Date as the equivalent of OrderDate
Total as the equivalent of OrderValue


SELECT qryEPSSchedule.OrderNo, qryEPSSchedule.SumOfTotal AS Total, qryEPSSchedule.Code10Date, IIf(([Total])>100,[Total]/2,[Total]) AS P1, IIf([Total]>1000,[Total]/10,IIf([Total]>499,[Total]/8,IIf([Total]>199,[Total]/6,IIf([Total]>99,[Total]/2,0)))) AS P2, IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,(IIf([Total]>199,[Total]/6,0))))) AS P3, IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,IIf([Total]>200,[Total]/6,0)))) AS P4, IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,0))) AS P5, IIf([Total]>1000,[Total]/10,0) AS P6, DateAdd("m",1,[code10date]) AS Date2, DateAdd("m",2,[code10date]) AS Date3, DateAdd("m",3,[code10date]) AS Date4, DateAdd("m",4,[code10date]) AS Date5, DateAdd("m",5,[code10date]) AS Date6
FROM qryEPSSchedule;

Hope this is half clear. The multiple IIf functions make decisions on the installments and dates due, depending on the order value.

 

[DoctorJDM]

Hi PHV

Sent previous one a bit late. Here's a more digestible version with comments

SELECT qryEPSSchedule.OrderNo, qryEPSSchedule.SumOfTotal AS Total, qryEPSSchedule.Code10Date,

'Based on Total, calculate the installments to be paid

IIf(([Total])>100,[Total]/2,[Total]) AS P1,

IIf([Total]>1000,[Total]/10,IIf([Total]>499,[Total]/8,IIf([Total]>199,[Total]/6,IIf([Total]>99,[Total]/2,0)))) AS P2,

IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,(IIf([Total]>199,[Total]/6,0))))) AS P3,

IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,IIf([Total]>200,[Total]/6,0)))) AS P4,

IIf([Total]>1000,[Total]/10,(IIf([Total]>499,[Total]/8,0))) AS P5,

IIf([Total]>1000,[Total]/10,0) AS P6,

'Using the date the order went live, ie Code10Date, calculate the dates for the later installments

DateAdd("m",1,[code10date]) AS Date2,
DateAdd("m",2,[code10date]) AS Date3,
DateAdd("m",3,[code10date]) AS Date4,
DateAdd("m",4,[code10date]) AS Date5,
DateAdd("m",5,[code10date]) AS Date6

FROM qryEPSSchedule;

 

[PHV]

What about this normalization union query ?

SELECT OrderNo, Code10Date As DueDate, IIf(SumOfTotal>100,SumOfTotal/2,SumOfTotal) AS DueAmount
FROM qryEPSSchedule
UNION SELECT OrderNo, DateAdd('m',1,Code10Date), IIf(SumOfTotal>1000,SumOfTotal/10,IIf(SumOfTotal>500,SumOfTotal/8,IIf(SumOfTotal>200,SumOfTotal/6,SumOfTotal/2)))
FROM qryEPSSchedule WHERE SumOfTotal>100
UNION SELECT OrderNo, DateAdd('m',2,Code10Date), IIf(SumOfTotal>1000,SumOfTotal/10,IIf(SumOfTotal>500,SumOfTotal/8,SumOfTotal/6)) 
FROM qryEPSSchedule WHERE SumOfTotal>200
UNION SELECT OrderNo, DateAdd('m',3,Code10Date), IIf(SumOfTotal>1000,SumOfTotal/10,IIf(SumOfTotal>500,SumOfTotal/8,SumOfTotal/6)) 
FROM qryEPSSchedule WHERE SumOfTotal>200
UNION SELECT OrderNo, DateAdd('m',4,Code10Date), IIf(SumOfTotal>1000,SumOfTotal/10,SumOfTotal/8) 
FROM qryEPSSchedule WHERE SumOfTotal>500
UNION SELECT OrderNo, DateAdd('m',5,Code10Date), SumOfTotal/10
FROM qryEPSSchedule WHERE SumOfTotal>1000

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DoctorJDM]

YES!! That's perfect.

Many thanks PHV, just what I needed. Also AlexCuse for the earlier steer along the same lines.

David