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.
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
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.
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