How to Post Previous Days transactions on next day

From: BN <bnsarma_at_gmail.com>
Date: Wed, 6 Sep 2006 08:04:39 -0400
Message-ID: <61292a9d0609060504j3cd908acp1d8090f7265bea8b@mail.gmail.com>

Hi,

Oracle 9i Rel2

 I have an objective to show last working days transaction (Friday) on Saturday and Sunday,
AND
 if the next day is also a Holiday then i have to post previous days transactions on Holiday too.

Example:I need to show Friday Sep 1,2006 Transactions on Sep 2,2006
Sept 3,2006
Sep 4,2006 (Labor Day -- Holdiay)

Here is the Query that i have written which takes care of Sat and Sundays.Iam having problem with Posting the data on Holidays.

select TRANS_DATE,
Case when MY_ID = 'XXXEX5' then SUM(PRINCIPAL) End as XXXDirect,
Case when MY_ID = 'XXXEX5' then trunc(AVG(RATE),2) End as XXXDirect_Avg,
Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then SUM(PRINCIPAL) End as

XXXBroker,

Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then trunc(AVG(RATE),2) End
as XXXBroker_Avg,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then SUM(PRINCIPAL)

End as Euro3,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then trunc(AVG(RATE),2) End as Euro3_Avg,0 AS FLAG FROM MY_TRANS_HISTORY
Group by my_id,Trans_date
Union
-- Accumulates Saturdays

select (TRANS_DATE+1) as Trans_Date,

Case when MY_ID = 'XXXEX5' then SUM(PRINCIPAL) End as XXXDirect,
Case when MY_ID = 'XXXEX5' then trunc(AVG(RATE),2) End as XXXDirect_Avg,
Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then SUM(PRINCIPAL) End as

XXXBroker,

Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then trunc(AVG(RATE),2) End
as XXXBroker_Avg,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then SUM(PRINCIPAL)

End as Euro3,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then trunc(AVG(RATE),2) End as Euro3_Avg,0 AS FLAG FROM CV_TRANS_HIST Where to_char(trade_date,'dy')='fri' Group by my_id,Trans_date
Union
-- Accumulates Sundays

select (TRANS_DATE+2) as Trans_Date,

Case when MY_ID = 'XXXEX5' then SUM(PRINCIPAL) End as XXXDirect,
Case when MY_ID = 'XXXEX5' then trunc(AVG(RATE),2) End as XXXDirect_Avg,
Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then SUM(PRINCIPAL) End as

XXXBroker,

Case when MY_ID in ('XXXEX3', 'XXXEX2', 'CLS') then trunc(AVG(RATE),2) End
as XXXBroker_Avg,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then SUM(PRINCIPAL)

End as Euro3,
Case when MY_ID in ('EPIEX3', 'ACK', 'ETCEX3', 'CKD') then trunc(AVG(RATE),2) End as Euro3_Avg,0 AS FLAG FROM MY_TRANS_HISTORY Where to_char(trade_date,'dy')='fri' Group by my_id,Trans_date
UNION
-- TO ADD HOLIDAYS TO THE RECORD SET

SELECT HOLI_DT AS TRANS_DATE,
0 AS XXXDirect,
0 AS XXXDirect_Avg,
0 AS XXXBroker,
0 AS XXXBroker_Avg,
0 as Euro3,
0 AS Euro3_Avg,
0 AS Euro4,
1 AS FLAG
FROM HOLIDAY_SOURCE
WHERE TO_NUMBER(TO_CHAR(HOLI_DT,'YYYY'))=2006 and TO_CHAR(HOLI_DT,'DY') NOT IN ('SAT','SUN')
ORDER BY 1 DESC Logic i was trying (I NEED TO POST THE PREVIOUS DAYS TRANSACTIONS WHERE FLAG=1 WHICH IS A HOLIDAY.using Lag())

*****My constraint is no PL/SQL and no DML is allowed.*

Please let me know is there anyway i can achieve this.*I tried using Lag(), it did not work for me,Any suggestions?*

--

Regards & Thanks
Jayanth

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 06 2006 - 07:04:39 CDT