SQL: Summing By Date Continuously?

[(PeteCresswell)]

Two tables:

tblPaymentAccrualDaily
SecID PayDate PayAmount
003 01/01/2007 123.97
004 01/01/2007 4250.63
003 01/02/2007 123.43
003 01/03 2007 120.45
003 01/04/2007 119.20
003 01/05/2007 130.44
...and so on for each and every day

tblPaymentSchedule
SecID PayDate
003 01/03/2007
003 02/03/2007
003 03/05/2007
003 04/03/2007


What I want to do is present, for a given SecID, the total daily
accruals from tblPaymentSchedule.PayDate to the next (actually,
the day after one PayDate to the next).

At any time, the user can alter the dates within PaymentSchdule.

The easy path would be to denormalize the totals into
tblPaymentSchedule, populate them once, put in some code
to re-populate them if/when a date is changed, and be done with
it.

I'd prefer, however, to do it all on-the-fly keeping things
normalized. But I also need for it to happen quickly enough to
not slow down the loading of a "Security Detail" screen.

Bottom Line: is this something I could do via SQL without abusing
SQL? i.e. would an SQL solution scale to where a security
might have a year or two worth of accruals behind it or would it
start bogging down?

 

[John W. Vinson]

Bottom Line: is this something I could do via SQL without abusing
SQL? i.e. would an SQL solution scale to where a security
might have a year or two worth of accruals behind it or would it
start bogging down?

You'll have to try with your database and your indexes, but unless it's
summing many hundreds of records I don't expect that you'll have any real
trouble. You certainly should *NOT* be storing the running totals unless
you're absolutely forced to do so; keeping them updated will be *slower* than
calculating them in the first place (an extra expensive disk write step).

John W. Vinson [MVP]

 

[Dale Fye]

Pete,

Let me make sure I've got this right.

You want to select a particular SecID (I assume you are either entering this
as text or in a combo or listbox), and then you want to compute the sum of
all of the tblPaymentAccrualDaily.PayAmount values where
tblPaymentAccrualDaily.PayDate is less than or equal to the "next"
tblPaymentSchedule.PayDate? By "next" PayDate, do you mean the next PayDate
greater than todays date?

How about something like:

SELECT PAD.SecID, SUM(PayAmount) as AccruedPayments
FROM tblPaymentAccrualDaily as PAD
INNER JOIN tbl_PaymentSchedule PS
ON PAD.SecID = PS.SecID
WHERE PS.SecID = [Sec ID]
AND PAD.PayDate <= (SELECT MIN(PayDate)
FROM tblPaymentSchedule
WHERE tblPaymentSchedule.SecID = [Sec
ID]
AND tblPaymentSchedule.PayDate >
[Some Date])

I've plugged two parameters into this query, one for your [Sec ID] value and
another for [Some Date]. These values might refer to controls on your form
or as in the [Some Date], may actually rely on the Date( ) function to
return a value.

HTH
Dale