with rundata(thedate) as
(
 values('??Enter Date (yyyy-mm-dd)??')
)
,
picksByUserByDay (type, pickdate, weekday, pickuser, totalFacing, referredPicks, totalPicks, ratio) as
( 
SELECT 4 AS Type, A.PICK_DATE, DAYNAME(A.PICK_DATE) AS WEEK_DAY, A.PICKER, SUM(CASE WHEN DV.ORDER.FACINGPDC = DV.HOST.WAREHOUSEID THEN A.PICKS ELSE 0 END) AS FACING_PICKS, SUM(CASE WHEN DV.HOST.WAREHOUSEID IS NULL THEN A.PICKS ELSE 0 END) AS REFERRED_PICKS, SUM(A.PICKS) AS TOTAL_PICKS, ((SUM(CASE WHEN DV.HOST.WAREHOUSEID IS NULL THEN A.PICKS ELSE 0 END) * 100.0) / (SUM(A.PICKS) * 100.0) * 100) AS REF_RATIO 
FROM DV.ORDER 
INNER JOIN (SELECT DV.RFPICKTASK.ORDERBK AS ORDERBK, DV.RFPICKTASK.COMPLETEDUSERBK AS PICKER, SUBSTR(DV.RFPICKTASK.ORIGLOCBK,1,3) AS AISLE, DV.RFPICKTASK.PICKAREABK AS PICK_AREA, DATE(DV.RFPICKTASK.COMPLETEDDT + current timezone) AS PICK_DATE, COUNT(DV.RFPICKTASK.RFTASKBC) AS PICKS FROM DV.RFPICKTASK WHERE DV.RFPICKTASK.TASKQTY > 0 GROUP BY DV.RFPICKTASK.ORDERBK, DV.RFPICKTASK.COMPLETEDUSERBK, DV.RFPICKTASK.PICKAREABK, SUBSTR(DV.RFPICKTASK.ORIGLOCBK,1,3), DATE(DV.RFPICKTASK.COMPLETEDDT + current timezone )) AS A 
ON DV.ORDER.BK = A.ORDERBK 
LEFT JOIN DV.HOST ON DV.ORDER.FACINGPDC  = DV.HOST.WAREHOUSEID 
WHERE DATE(A.PICK_DATE) >= DATE('12/14/2009') AND DATE(A.PICK_DATE) < CURRENT DATE AND DATE(A.PICK_DATE) > CURRENT DATE - 7 DAYS  
GROUP BY A.PICKER, A.PICK_DATE
)
,
putsByUserByDay (putuser, putdate, totalPuts) as
(
SELECT DV.RFPUTAWAYTASK.COMPLETEDUSERBK AS USER, Date(DV.RFPUTAWAYTASK.COMPLETEDDT + current Timezone) AS PUTAWAY_DATE, COUNT(DV.RFPUTAWAYTASK.RFTASKBC) AS TOTAL_PUTAWAYS FROM DV.RFPUTAWAYTASK WHERE DV.RFPUTAWAYTASK.TASKTYPE = 10 AND Date(DV.RFPUTAWAYTASK.COMPLETEDDT + current Timezone) > CURRENT DATE - 15 DAYS GROUP BY DV.RFPUTAWAYTASK.COMPLETEDUSERBK, Date(DV.RFPUTAWAYTASK.COMPLETEDDT + current Timezone) ORDER BY DV.RFPUTAWAYTASK.COMPLETEDUSERBK, Date(DV.RFPUTAWAYTASK.COMPLETEDDT + current Timezone) DESC
)
,
allUsers (aDate, userbk) as
(
 select theDate, UCase(LTrim(RTrim(bk )))
 from runData, dv.user
)

select userbk as "User", COALESCE(totalpicks,0) as "Total Picks", COALESCE(totalPuts,0) as "Total Puts"
from allusers 
left join picksByUserByDay on
   LTrim(RTrim(UCase(picksByUserByDay.pickUser))) = userbk and
   picksByUserByDay.pickDate = aDate
left join putsByUserByDay on
   LTrim(RTrim(UCase(putsByUserByDay.putUser))) = userbk and
   putsByUserByDay.putDate = aDate  
where (totalPicks > 0 or totalPuts > 0)
order by userbk