How to Post Previous Days transactions on next day
- From: BN <bnsarma@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 6 Sep 2006 08:04:39 -0400
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
Other related posts:
- » How to Post Previous Days transactions on next day
