Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2016
    Posts
    2

    Unanswered: DB2 Count Report

    Table 1

    Date Code Key
    2016-08-01 D1234 KEY1
    2016-08-02 D1214 KEY2
    2016-08-01 D1214 KEY3
    2016-08-01 P1234 KEY4
    2016-08-01 P1234 KEY5


    Table 2

    code key line
    D1234 KEY1 LN1
    D1214 KEY2 LN1
    D1214 KEY3 LN1
    D4444 KEY4 LN1
    D1234 KEY4 LN2
    D2222 KEY4 LN3
    D2343 KEY4 LN4
    D2324 KEY4 LN5
    D1234 KEY5

    Above are the two table i have and I need to find the top 5 codes for the day. Will need to count from table 2 only if the code(first byte) in table1 is not 'D'. Join field for the two tables is the key field.


    Count Output

    2016-08-01 D1234 3 (1 from table2) 2016-08-02 D1224 3
    D1214 2 (won't count from table 2) D1334 2
    D4444 1 (only the first one for the key will be taken for that key) D4444 2
    D4434 1
    D9994 1

    The count may be different for different days and the code may be different



    Can I do this using SQL? I don't want to do it using temporary table as our Business objects dont support temporary table and so i was trying to see if there is any way to do this using SQL.

    Any help or guidance is helpful.

    Raj

  2. #2
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    20
    Provided Answers: 1

    quick and dirty

    Hi gopurs77,

    SQL should do the trick.

    But first some little questions:

    Are the key values in table 1 unique ?

    Why not join on code and key ?

    Can't find D4434 and D9994 in your test data ?

    Good luck
    db2dp

  3. #3
    Join Date
    Sep 2016
    Posts
    2

    DB2 Count Report

    Thanks for the response db2dp.

    Yes the key values on table 1 is unique and table 2 have multiple records for the same key. It is like the foreign key

    D4434 and D9994 is also in the table. I just added to the test data output. Please assume that it is there in the input.



    Quote Originally Posted by db2dp View Post
    Hi gopurs77,

    SQL should do the trick.

    But first some little questions:

    Are the key values in table 1 unique ?

    Why not join on code and key ?

    Can't find D4434 and D9994 in your test data ?

    Good luck
    db2dp

  4. #4
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    20
    Provided Answers: 1

    quick and dirty

    Hi gopurs77,

    first try

    DATE1 CODE1 KEY1
    ---------- ----- ----
    2016-08-01 D1234 KEY1
    2016-08-02 D1214 KEY2
    2016-08-01 D1214 KEY3
    2016-08-01 P1234 KEY4
    2016-08-01 P1234 KEY5


    CODE2 KEY2 LINE2
    ----- ---- -----
    D1234 KEY1 LN1
    D1214 KEY2 LN1
    D1214 KEY3 LN1
    D4444 KEY4 LN1
    D1234 KEY4 LN2
    D2222 KEY4 LN3
    D2343 KEY4 LN4
    D2324 KEY4 LN5
    D1234 KEY5

    Join field for the two tables is the key field.

    SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.KEY1=t2.KEY2

    DATE1 CODE1 KEY1 CODE2 KEY2 LINE2
    ---------- ----- ---- ----- ---- -----
    2016-08-01 D1234 KEY1 D1234 KEY1 LN1
    2016-08-02 D1214 KEY2 D1214 KEY2 LN1
    2016-08-01 D1214 KEY3 D1214 KEY3 LN1
    2016-08-01 P1234 KEY4 D4444 KEY4 LN1
    2016-08-01 P1234 KEY4 D1234 KEY4 LN2
    2016-08-01 P1234 KEY4 D2222 KEY4 LN3
    2016-08-01 P1234 KEY4 D2343 KEY4 LN4
    2016-08-01 P1234 KEY4 D2324 KEY4 LN5
    2016-08-01 P1234 KEY5 D1234 KEY5


    Will need to count from table 2 only if the code(first byte) in table1 is not 'D'.

    SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.KEY1=t2.KEY2 WHERE SUBSTR(t1.CODE1,1,1)<>'D'

    DATE1 CODE1 KEY1 CODE2 KEY2 LINE2
    ---------- ----- ---- ----- ---- -----
    2016-08-01 P1234 KEY4 D4444 KEY4 LN1
    2016-08-01 P1234 KEY4 D1234 KEY4 LN2
    2016-08-01 P1234 KEY4 D2222 KEY4 LN3
    2016-08-01 P1234 KEY4 D2343 KEY4 LN4
    2016-08-01 P1234 KEY4 D2324 KEY4 LN5
    2016-08-01 P1234 KEY5 D1234 KEY5


    Above are the two table i have and I need to find the top 5 codes for the day
    SELECT COUNT(*) AS ANZAHL,DATE1,CODE2 FROM table1 t1 INNER JOIN table2 t2 ON t1.KEY1=t2.KEY2 GROUP BY DATE1,CODE2,SUBSTR(t1.CODE1,1,1) HAVING SUBSTR(t1.CODE1,1,1)<>'D' ORDER BY DATE1 DESC,ANZAHL DESC FETCH FIRST 5 ROWS ONLY

    ANZAHL DATE1 CODE2
    ----------- ---------- -----
    2 2016-08-01 D1234
    1 2016-08-01 D2222
    1 2016-08-01 D4444
    1 2016-08-01 D2343
    1 2016-08-01 D2324

    Good luck
    db2dp

Tags for this Thread

Posting Permissions

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