Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2009
    Posts
    19

    Unanswered: Rollups on Table

    Hi... first post so don't slate me too bad!! I am trying to create rollups in Oracle on a table that I have and am a little stumped on where to go.

    Basicallly I have transactional data in a table for a two month period. I need to roll it up to get the average spend on each account in past 7 days for each day in a one month period.

    I originally tried a self join like this:

    select
    a.Account_Number,
    a.Day,
    count(*) as vol,
    sum(b.amount) as val

    from TXN_Table a, TXN_Table b
    where a.Account_Number= b.Account_Number (+)
    and b.Day> DateADD('dd' , -7, a.Day)
    and b.Day< a.Day
    group by a.Account_Number, a.Day;

    N.B. I created the DATEADD function in my schema and the fields 'Day' are in the format: DD-Mon-YY.

    This took forever (5m Rows in table) and the values outputted are not correct!

    The output I require is like this:

    Account, Day, 7DayTotalVolume, 7DayTotalValue

    1 1-Apr-09 32 $12
    1 2-Apr-09 42 $10
    1 3-Apr-09 52 $177
    1 4-Apr-09 32 $18
    1 5-Apr-09 33 $8
    1 6-Apr-09 56 $16

    2 1-Apr-09 21 $10
    2 2-Apr-09 66 $20
    2 3-Apr-09 52 $27
    2 4-Apr-09 35 $90
    2 5-Apr-09 30 $82
    2 6-Apr-09 55 $320

    I imagine this can be done with CUBE or ROLLUP but I am not very sure about what way to do it.

    If you could make some suggestions that would be great?

    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Leer el fino manual

    Not to "slate" you, but you actually need to learn the basics and read the fine Oracle® Database SQL Language Reference to learn about dates and date functions.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2009
    Posts
    19
    Quote Originally Posted by LKBrwn_DBA
    Not to "slate" you, but you actually need to learn the basics and read the fine Oracle® Database SQL Language Reference to learn about dates and date functions.

    "N.B I created the DATEADD stored function in my schema and the fields 'Day' are in the format: DD-Mon-YY"

    Thanks for the advice. Could you expand on my problem per chance?

  4. #4
    Join Date
    Sep 2009
    Posts
    19
    DATEADD being:

    CREATE OR REPLACE Function DateADD(vchDatePart varchar , intOP in int,
    dt date)
    return date
    as
    dd int;
    mm int;
    yyyy int;
    hh int;
    NN int;
    SS int;
    v date;
    lintOP int;
    Begin
    lintOP := intOP;

    if upper(vchDatePart) like 'D%' then
    return dt + intOP;
    end if;
    dd := to_number(to_Char(dt,'dd'));
    mm := to_number(to_Char(dt,'MM'));
    yyyy:= to_number(to_Char(dt,'yyyy'));
    HH := to_number(to_Char(dt, 'HH'));
    NN := to_number(to_Char(dt, 'MI'));
    SS := to_number(to_Char(dt, 'SS'));

    if upper(vchDatePart) like 'Y%' then
    yyyy:= yyyy+ lintOP;
    end if;

    if upper(vchDatePart) like 'M%' then
    yyyy:= yyyy+round(lintOP/12);
    mm := mm+mod(lintOP,12);
    end if;-->MM
    if upper(vchDatePart) like 'H%' then
    dd := dd + round(lintOP/24);
    hh := hh + mod(lintOP,24);
    end if;--> hh
    if upper(vchDatePart) like 'N%' then
    dd := dd + round(lintOP/(24*60));
    hh := hh + round(lintOP/60);
    NN := NN + mod(lintOP , 60);
    end if;--> MInutes
    if upper(vchDatePart) like 'S%' then
    dd := dd + round(lintOP/(24*60*60));
    hh := hh + round(lintOP/60*60);
    NN := NN + round(lintOP/60);
    NN := NN + MOD(lintOP,60);
    end if;--> SS
    v := LAST_DAY(to_date('01/'||to_char(mm,'09')||'/'|| to_char(yyyy,
    '0009'),'dd/mm/yyyy'));
    if dd > to_number(to_Char(v,'DD')) then
    dd := to_number(to_Char(v,'DD'));
    end if;
    return to_date(lpad(dd,2,'0')||to_char(mm,'09')||'/'|| to_char(yyyy,
    '0009')||' '||lpad(hh,2,'0')||':'||lpad(NN,2,'0')||':'||lpad( SS,2,'0'),
    'dd/mm/yyyy HH24:MIS') ;
    exception when others then return null ;
    End;
    /

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Apples to apples and Oranges to oranges

    1) You should allways compare dates to dates and not convert dates to character and then compare.
    2) You can subtract days (or other intervals) from a date (or SYSDATE) and it remains a date.
    3) Check this out:
    Code:
    SELECT   a.account_number, TRUNC(a.DAY) DAY, COUNT (*) AS vol, SUM (b.amount) AS val
        FROM txn_table a, txn_table b
       WHERE a.account_number = b.account_number(+)
             AND b.DAY > TRUNC(a.DAY) - 7
             AND b.DAY < TRUNC(a.DAY)
    GROUP BY a.account_number, TRUNC(a.DAY);

    PS: You do not need a "DateADD" function, Oracle provides the functionality.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Sep 2009
    Posts
    19

    Thanks

    Thanks that was helpful. I come from a MSQL background so I'm a bit stumped in Oracle.

    Do you think the query that I wrote is achieveing what I want and I don't need a cube or rollup?

    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Result shoud be ok

    Query should produce the correct result.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2009
    Posts
    19
    Thanks, I should work I guess. It is has taken 16 hours so far!

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Wrong track?

    Quote Originally Posted by zedordead
    Thanks, I should work I guess. It is has taken 16 hours so far!
    16 hours? how many rows?
    I took a closer look at your original post and noticed some issues:

    1) The quote: "I need to roll it up to get the average spend on each account in past 7 days for each day in a one month period" makes no sense; how can it be the "past 7 days" in a "one month period"?

    2) I noticed you also are joining the transacrtion table (TXN_Table) to itself.

    What you need to do is clarify your requirement and provide some sample data and the expected result of your query.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Sep 2009
    Posts
    19
    OK... thanks for your help! Appreciate it!

    The data at the moment is each transaction per account so:

    Account_Number.......Date.........Value

    1.........................1-Apr-09.......$2
    1.........................2-Apr-09.......$1
    1.........................3-Apr-09.......$7
    1.........................3-Apr-09.......$2
    1.........................4-Apr-09.......$2
    1.........................5-Apr-09.......$1
    1.........................5-Apr-09.......$1
    1.........................6-Apr-09.......$7
    1.........................7-Apr-09.......$2
    1.........................8-Apr-09.......$1
    1.........................9-Apr-09.......$7

    I need it to rollup so I have the total volume and total value of transactions that account has made in the previous 7 days. So the transaction on the 9-APR-09 would look like:

    Account Number.......Date.......Value....Acc_7day_Vol..... .Acc_7day_Val

    1.........................8-Apr-09.....$1.............9........................$25
    1.........................9-Apr-09.....$7.............9........................$24

    i.e.

    Acc_7day_Val (8th)= 2+7+1+1+2+2+7+1+2 =$25
    Acc_7day_Val (9th)= 1+2+7+1+1+2+2+7+1 =$24

    From there, working out the averages is easy enough.

    Does this make sence?

    Thanks

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Wouldn't
    Code:
    SELECT   a.account_number, TRUNC(a.DAY) DAY, COUNT (*) AS vol, SUM (a.amount) AS val
        FROM txn_table a
       WHERE a.DAY > TRUNC(sysdate) - 7
    GROUP BY a.account_number, TRUNC(a.DAY);
    give you what you want?
    Please don't tell us that you are going to select these last 7 days then sum up the rows you fetched to determine an avg to then display. You can use AVG function in your SQL. You could even get your totals per account and the AVG at the same time.
    Code:
    SELECT   a.account_number, TRUNC(a.DAY) DAY, COUNT (*) AS vol, SUM (a.amount) AS val, avg(a.amount) AS avg_amt
        FROM txn_table a
       WHERE a.DAY > TRUNC(sysdate) - 7
    GROUP BY a.account_number, TRUNC(a.DAY);
    Dave Nance

  12. #12
    Join Date
    Sep 2009
    Posts
    19
    Thanks for your input...

    No I'm afraid that won't work as this is being done retrospectively. Using sysdate would bring the last 7 days from now. Hence using a self join to vary the date in the select statement and in the where clause!

    Did that make sense?

  13. #13
    Join Date
    Sep 2009
    Posts
    19
    I realised that about the averages too... I just would like to see the volumes and value too!

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •