https://portal.systemadmin.com/csa_agent_week.asp?testid=51722&date_from=7/1/2015&date_to=7/9/2016&Submit=Submit
please add a table like this http://prntscr.com/byt2ri to the bottom of this page
table title: Rebill Commissions Status
have 4 weeks worth of data on it like in the example table with options to change date range
have the following columns:
Date | Queue | CustomerID | Rebill Date | Rebill Status | Commission

For rebill date you will be displaying the first bill date
for rebill status you will be displaying the status of the first bill
for commission display the $ of commission they made from it
in general here you need to display every customer that the agent sold and can potentially be eligible to earn rebill commissions


User:	Task
Date:	8/3/2016 4:04:46 PM
Status:	Comments Added
User:	ETsoi
Date:	8/11/2016 5:44:28 PM
Status:	In progress
User:	ETsoi
Date:	8/30/2016 5:06:40 PM
Status:	Done
ASP:
portal.systemadmin.com\csa_agent_week.asp

SQL:
ccp.dbo.ccf_calls_agent_total_get
ccp.dbo.callcenterreport_agent_weeks_get_forRebill
User:	Task
Date:	8/31/2016 10:13:07 AM
Status:	Comments Added

<Last Edited: 9/6/2016 10:26:04 AM by Task>
Please update the query to this:

select distinct
cus.email
,cus.contactFname
,cus.contactLname
,addr_bill.phone
,p.insertDT
--,p.dn_cardtoken
,case when authmessage like '%insufficient%' or authmessage like '%floor%' then 'Card Declined - Insufficient Funds'
 when authmessage like '%stolen%'
or authmessage like '%processor decline%'
or authmessage like '%restraint%'
or authmessage like '%fraud%'
or authmessage like '%accterror%' then 'Bad Card'
 when authmessage like '%donothonor%'
or authmessage like '%do not honor%'
or authmessage like '%denied%' then 'Card Declined - check personal information'
 when authmessage like '%expired%' then 'Card Declined - Card expired'
 when isapproved = 0 then 'Card Declined'
 else '-' end as 'Decline Reason'
,case when p.isapproved = 1 and ord.dn_status not in (59,74,79) then '$20'
  when ord.dn_status = 74 then '-$20' else '$0' end as 'Payment_due'
,case when item.number like '%M' then 'Rebill'
 when item.number like '%MT%' and dateadd(wk, datediff(wk, 0, p.insertDT), 0) = dateadd(wk, datediff(wk, 0, ord.insertDT), 0) then 'New First Bill' else 'Old First Bill'
  end as Rebill_status
--,er.cnt
--into #NoShow
--into #noshow2
from  om_key..bo_orders ord (nolock)
left join om_key..bo_customers cus (nolock) on cus.id = ord.bo_customersid
left join om_key..bo_address addr_bill (nolock) on addr_bill.bo_Addressgroupid = ord.bo_addressgroupid and addr_bill.type = 0
left join om_key..bo_orderdetails det (nolock) on det.bo_ordersid = ord.id
left join om_key..bo_items item (nolock) on item.id = det.bo_itemsid
left join om_key..bo_payment p (nolock) on /*p.bo_ordersID = ord.id--*/p.id = (select max(id) FROM om_key..bo_payment (nolock) WHERE bo_ordersID = ord.id and insertDT between '8-21-2016 00:00' and '8-27-2016 23:59')
--left join (select bo_ordersID, count(id) as cnt from om_key..bo_payment (nolock) where isduplicate = 0 and insertDT < '8-20-2016 23:59' group by bo_ordersID ) er on er.bo_ordersID = ord.id
where p.insertDT between '8-21-2016 00:00' and '8-27-2016 23:59'
and item.id in (2575,2576,2573,2572)
and email not in (select email from #NoShow where payment_due != '$20')
and email in (select email from #noshow2 where payment_due = '$20')
order by 7 desc, 1 asc



STEP 1:
Run query for 2 weeks ago
insert into a new table with these exact columns

STEP 2:
Run query for last week
-exclude every customer who we didn't collect from last week and 2 weeks ago either

NOTES:
-make sure to edit the payment date on the payment join and the where clause as well
-make sure to not exclude customers who declined 2 weeks ago but approved last week
for step 1 you can create a reconcile table in our system
and just query against that
so we don't have to make this 2 steps