0

Hi, I am new to oracle. I have this following query :

SELECT 
        TYP_CD, SUM( INVC_AMT),
        trunc(ALLOC_RUN_DT) as "Alloc_Date", 
        MIN(ALLOC_RUN_DT)  as "Minimum_Time",
        MAX(ALLOC_RUN_DT) as "Maximum_Time"
    FROM  allocation_header 
group by TYP_CD, ALLOC_RUN_DT 
order by TYP_CD, "Alloc_Date" 

and the sample output I get is :

TYP_CD     INVC_AMT    ALLOC_RUN_DT  Minimum_Time            Maximum_Time
01      20520232.31    2/27/2012    2/27/2012 5:04:31 PM    2/27/2012 5:04:31 PM
01      22335264.55    4/2/2012     4/2/2012 2:46:12 PM     4/2/2012 2:46:12 PM

But the output here is wrong.

Basically what I am doing is, I sum-up all the transaction for entire day (exa- 2/27/2012), and then I want to find the first and the last transaction time for that particular day. Here in the output minimum time is wrong because it is same as maximum time which is the last transaction for that day.

What can be done here??

Any help appreciated... Thank YOu.

2
Contributors
1
Reply
17
Views
4 Years
Discussion Span
Last Post by LastMitch
0

find minimum and maximum date based on time after group by

Maybe like this (not really tested because I don't have database):

SELECT TYP_CD, SUM(INVC_AMT), MAX(ALLOC_RUN_DT), MAX(ALLOC_RUN_DT) FROM allocation_header  GROUP BY ALLOC_RUN_DT;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.