-- Query 1. TO calculate the All distinct Source Codes and their account numbers in db

 SELECT s_code,
   COUNT(*) AS
 s_codecount
 FROM tbl_acc
 WHERE users_no IN('1')
  AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
  AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 GROUP BY s_code
ORDER BY s_codecount DESC










-- Query 2. To get the account numbers

SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'X'
 AND rownum <= '47'
UNION ALL
SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'B'
 AND rownum <= '33'
UNION ALL
SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'W'
 AND rownum <= '33'
UNION ALL
SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'N'
 AND rownum <= '33'
UNION ALL
SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'G'
 AND rownum <= '33'
UNION ALL
SELECT acc_no
FROM tbl_acc
WHERE users_no IN('1')
 AND to_date(tran_date,   'yyyymmdd') >= to_date('03/01/2006',   'mm/dd/yyyy')
 AND to_date(tran_date,   'yyyymmdd') <= to_date('03/02/2006',   'mm/dd/yyyy')
 AND s_code = 'J'
 AND rownum <= '21'