This is my first time submitting a question so please let me know if I break in Forum Rules here. Here is my existing (working) script. I’ve placed a note “INSERT OPTION HERE” as a placeholder for the coding I am asking for help with.
select trn.wh_id
, to_char(trn.end_tran_date, ‘MM/DD/YYYY’) AS DATE_
, count(CASE WHEN trn.tran_type in (‘303’,‘304’,‘305’) then ‘1’ end ) as Priority_pick
– INSERT OPTION HERE.
, count(CASE WHEN trn.tran_type = ‘301’ then ‘3’ end ) as WHSE_Pick
, count(CASE WHEN trn.TRAN_TYPE in (‘151’,‘161’) then ‘4’ end ) as Receipts
, count(CASE WHEN trn.tran_type = ‘151’ then ‘5’ end ) as Stage_PO_REC
, count(CASE WHEN trn.tran_type = ‘161’ then ‘6’ end ) as ASN_REC
, count(CASE WHEN trn.tran_type in (‘202’,‘252’,‘256’) then ‘7’ end ) as Puts
, count(CASE WHEN trn.tran_type = ‘256’ then ‘8’ end ) as Putaway
, count(CASE WHEN trn.tran_type = ‘252’ then ‘9’ end ) as Replen
, count(CASE WHEN trn.tran_type = ‘800’ then ‘10’ end ) as CYCC
, count(CASE WHEN trn.tran_type = ‘885’ then ‘11’ end ) as Audits
, sum(case when trn.tran_type in (‘303’,‘304’,‘305’,‘301’,‘151’,‘161’,‘202’,‘252’,‘256’,‘800’,‘885’) then 1 end) ttl_trans
from t_tran_log trn
where trn.wh_id = :whid
and trn.end_tran_time >sysdate -:days
group by trn.wh_id,to_char(end_tran_date, ‘MM/DD/YYYY’)
order by date_;
Data is pulling from a transaction log. In our world, I have a line count (the number of items on a ticket and a Pick Count (the number of pulls required to fulfill the request line count). I’m looking to get a LINE COUNT total. The table has a LINE_NUMBER COLUMN which could be summed to provide the required data point but I cannot figure out how to write that command.