Page 1 of 1

DB2 Count Report

PostPosted: Fri Sep 09, 2016 10:40 am
by gopurs
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 LN1


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

Re: DB2 Count Report

PostPosted: Fri Sep 09, 2016 1:37 pm
by NicC
Please use the code tags when posting data, code or anything requiring spacing to be maintained. Your column headings did not line up with your column data and your report was all scrunched up. This request is made so often that searching the forum, or even just poking around it, will give you the information needed.