Results 1 to 6 of 6

Thread: Ordercounter

  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: Ordercounter

    Hi,

    I've got 3 tables out of SAP
    JHAK (Ordertable)
    JHAP (Positions)
    JHAGA (Invoicepositions)

    When creating a warehouse I join this 3 tables over
    OrderId, PositionID and InvoicePositionID.

    For MS OLAP purpose I want a ordercounter field which
    is '1' the first time
    OrderID + '_' + PositionID + '_' + InvoicePositionID occurs.
    So '1' only happens once per ordernumber in the joined table. (Else 0)
    Then when summing this ordercounter up within MS OLAP I can easily see e.g.
    how many orders did a salesrep per month, per itemgroup, branch etc.
    (I know it is possible to solve that issue with MDX in MS OLAP, I solved it already but because of the huge amount of data 12 Mio records, the MDX slows the cube down to 2 minutes and more answering time)

    My workaround was:
    When PositionID and InvoicePositionID = 1 then counter '1' else '0'.
    But when orderpositions starts with 2 or higher it will give me wrong results.

    I searched the forum and found the thread
    "Help in joining three tables for COUNT"
    tried it out, but that won't give me
    '0' and '1'
    but
    number of invoicepositions per orderposition and number of orderpositions per order.

    Thanks for any help!

    Michael

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the DDL for your tables and a few lines of sample data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Demodata added

    Here are the demodata I created:
    Orders:
    OrderID Text
    1 Order 1
    2 Order 2
    3 Order 3
    4 Order 4
    5 Order 5
    6 Order 6
    7 Order 7
    8 Order 8
    9 Order 9
    11 Order 11
    Positions:
    OrderID PosId PosText
    1 1 Order 1 Position 1
    1 2 Order 1 Position 2
    1 3 Order 1 Position 3
    1 4 Order 1 Position 4
    2 1 Order 2 Position 1
    2 3 Order 2 Position 3
    2 4 Order 2 Position 4
    2 4 Order 2 Position 4
    3 2 Order 3 Position 2
    3 2 Order 3 Position 2
    3 2 Order 3 Position 2
    3 2 Order 3 Position 2
    4 1 Order 4 Position 1
    4 2 Order 4 Position 2
    4 2 Order 4 Position 2
    4 3 Order 4 Position 3
    5 1 Order 5 Position 1
    5 2 Order 5 Position 2
    5 2 Order 5 Position 2
    5 3 Order 5 Position 3
    5 3 Order 5 Position 3
    5 3 Order 5 Position 3
    6 10 Order 6 Position 10
    6 20 Order 6 Position 20
    6 30 Order 6 Position 30
    6 40 Order 6 Position 40
    6 50 Order 6 Position 50
    7 1 Order 7 Position 1
    7 2 Order 7 Position 2
    7 2 Order 7 Position 2
    7 3 Order 7 Position 3
    7 4 Order 7 Position 4
    8 1 Order 8 Position 1
    8 2 Order 8 Position 2
    8 3 Order 8 Position 3
    8 4 Order 8 Position 4
    9 1 Order 9 Position 1
    9 2 Order 9 Position 2
    9 3 Order 9 Position 3
    9 5 Order 9 Position 5
    11 1 Order 11 Position 1
    11 1 Order 11 Position 1
    11 1 Order 11 Position 1
    11 1 Order 11 Position 1

    Invoiceposition:
    OrderID PosId FacID PosText
    1 1 1 Order 1 Position 1 Faktura 1
    1 2 1 Order 2 Position 1 Faktura 1
    1 3 1 Order 3 Position 1 Faktura 1
    1 4 1 Order 4 Position 1 Faktura 1
    2 1 1 Order 1 Position 1 Faktura 1
    2 3 1 Order 3 Position 1 Faktura 1
    2 4 1 Order 4 Position 1 Faktura 1
    2 4 2 Order 4 Position 2 Faktura 2
    3 2 1 Order 2 Position 1 Faktura 1
    3 2 2 Order 2 Position 2 Faktura 2
    3 2 3 Order 2 Position 3 Faktura 3
    3 2 4 Order 2 Position 4 Faktura 4
    4 1 2 Order 1 Position 2 Faktura 2
    4 2 1 Order 2 Position 1 Faktura 1
    4 2 1 Order 2 Position 1 Faktura 1
    4 3 1 Order 3 Position 1 Faktura 1
    5 1 1 Order 1 Position 1 Faktura 1
    5 2 1 Order 2 Position 1 Faktura 1
    5 2 1 Order 2 Position 1 Faktura 1
    5 3 1 Order 3 Position 1 Faktura 1
    5 3 1 Order 3 Position 1 Faktura 1
    5 3 1 Order 3 Position 1 Faktura 1
    6 10 1 Order 10 Position 1 Faktura 1
    6 20 1 Order 20 Position 1 Faktura 1
    6 30 1 Order 30 Position 1 Faktura 1
    6 40 1 Order 40 Position 1 Faktura 1
    6 50 1 Order 50 Position 1 Faktura 1
    7 1 1 Order 1 Position 1 Faktura 1
    7 2 1 Order 2 Position 1 Faktura 1
    7 2 1 Order 2 Position 1 Faktura 1
    7 3 1 Order 3 Position 1 Faktura 1
    7 4 1 Order 4 Position 1 Faktura 1
    8 1 1 Order 1 Position 1 Faktura 1
    8 2 1 Order 2 Position 1 Faktura 1
    8 3 1 Order 3 Position 1 Faktura 1
    8 4 1 Order 4 Position 1 Faktura 1
    9 1 1 Order 1 Position 1 Faktura 1
    9 2 2 Order 2 Position 2 Faktura 2
    9 3 1 Order 3 Position 1 Faktura 1
    9 5 1 Order 5 Position 1 Faktura 1
    11 1 1 Order 1 Position 1 Faktura 1
    11 1 2 Order 1 Position 2 Faktura 2
    11 1 3 Order 1 Position 3 Faktura 3
    11 1 4 Order 1 Position 4 Faktura 4

    I'm not shure but I think that the invoiceposition starts with '1' but better if something like min(facid) could be
    used.

    There could be any combinations of
    orderid, posid and facid.

    I tried different SQL codes today but didn't succeded :-(

    thanks for any more help

    Michael

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Still unclear what you want.

    Do you want the ordercounter value to increment for each Invoicepositions, but to reset for each Ordertable?

    Also, are you only doing this so you can "...easily see e.g.
    how many orders did a salesrep per month, per itemgroup, branch etc."??? If so, then you should be using aggregate queries to perform this analysis.

    Post the SQL you use to join the tables, along with some of the code you tried that didn't work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mike, Please read the sticky's at the top of the thread and post what that asks for....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Got a solution

    Hi blindman, hi brett (nice to hear from you again),

    I got a solution:

    select
    orderid + '_' + posid + '_' + facid as uniquekey,
    case
    when posid + '_' + facid =
    (select min(fp.posid + '_' + fp.facid)
    from extr_counterbeispiel_facpos as fp
    where fp.orderid = o.orderid
    )
    then '1'
    else '0'
    end as ordercounter
    from extr_counterbeispiel_facpos as o


    gives me:

    1_1_1 1
    1_2_1 0
    1_3_1 0
    1_4_1 0
    2_1_1 1
    2_3_1 0
    2_4_1 0
    2_4_2 0
    3_2_1 1
    3_2_2 0
    3_2_3 0
    3_2_4 0
    4_1_2 1
    4_2_1 0
    4_2_1 0
    4_3_1 0
    5_1_1 1
    5_2_1 0
    5_2_1 0
    5_3_1 0
    5_3_1 0
    5_3_1 0
    6_10_ 1
    6_20_1 0
    6_30_1 0
    6_40_1 0
    6_50_1 0
    7_1_1 1
    7_2_1 0
    7_2_1 0
    7_3_1 0
    7_4_1 0
    8_1_1 1
    8_2_1 0
    8_3_1 0
    8_4_1 0
    9_1_1 1
    9_2_2 0
    9_3_1 0
    9_5_1 0
    11_1_1 1
    11_1_2 0
    11_1_3 0
    11_1_4 0

    Blindman, all this stuff is used to create a huge warehouse
    for my biggest customer here in germany.
    They are making telefonbooks which are free for the normal household. All the money is coming from the companies (and sometimes private) advertising.
    My customer do have round about 600.000 customers in south germany and round about 200 salesreps running
    literally from house to house.
    The orders are mainly created automatically from old orders out of the last year.
    It was lot of work but very much fun, because they like our solution (Q4bis Analysis) and came up with more and more ideas what they want to see.

    So back to the "ordercounter".
    When having only one '1' per orderid in the 2 to 5 records concerning one order gives me an artificial measure for MS OLAP in the sales cube.
    When filtering - say - a salesrep, a branch, a zipcode region, a priceclass (they can now ask: what turnover I did with advertisement worth 500 to 1000 Euros?)
    The result is mainly turnover but now also the numbers of orders this salesrep placed.
    So you can easily compare him with a colleage or with an other zipcode region and so on.
    (Also you can calculate the average turnover per order and
    compare it to other salesrep).

    Brett, I cannot make head or tail out of your remark.
    Sorry. Perhaps I have to visit dbforums more often :-)

    Nevertheless, thanks a lot for your support.

    Mike

Posting Permissions

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