Hello :)
I need a bit of help with my code; below is my table (LPA_HISTORY).
CLAIM_NO ENT_SEQNO START_DATE END_DATE LPA_AMT
00000123 1 01/04/2008 16/05/2008 84P
00000123 2 17/05/2008 20/08/2008 60P
00000123 3 21/08/2008 31/03/2009 50P
What i need to do is enter two parmeter dates, (end & start date) an example would be
the 01/04/2008 & 14/09/2008, this would then see what Sequence number (ent_seqno) it falls into.
So 01/04/2008 to the 14/09/2008, this starts in ENT_SEQNO 1,2 and finishes half way in ENT_SEQNO 3.
I need to calculate the number of days in that period for each Ent_seqno, divide it by 7 and multiply it by the amount shown in the LPA_AMT.
(ent_seqno 1) 84p/7 * 46 (days in that period) +
(ent_seqno 2) 60p/7* 96 +
(ent_seqno 3) 50p/7 * 35 = Total
the output im after is just the grouped claim number with the total i.e
CLAIM_NO TOTAL
00000123 1624.86
Still following me>?
Heres my code so far
SELECT
lpa_history.claim_no,
SUM ((:p_end_date - :p_start_date +1 ) * (ROUND(LPA_HISTORY.LPA_AMT,2))/7)
FROM LPA_HISTORY
where LPA_HISTORY.END_DATE >= :p_end_date
and LPA_HISTORY.START_DATE <= :p_start_date
group by lpa_history.claim_no
But it has no reference on how to select the different dates in the different ent_seqno's,
any ideas?? Plsae?
Edited by: user633450 on 23-Sep-2008 01:55