 select *
 from analytics_subscription_transitions bst
 left outer join subscription_events se on bst.subscription_event_record_id = se.record_id
-where coalesce(se.requested_date, '') != coalesce(bst.requested_timestamp, '')
-or coalesce(se.effective_date, '') != coalesce(bst.next_start_date, '')
+/* TODO Look at entilement rows only, ignore in-memory events and blocking states */
+where (coalesce(bst.prev_service, 'entitlement-service') = 'entitlement-service'
+and coalesce(bst.next_service, 'entitlement-service') = 'entitlement-service'
+and bst.subscription_event_record_id is not null)
+/* Tricky... Need to look at the account timezone */
+and ((coalesce(date(se.requested_date), '') != coalesce(date(bst.requested_timestamp), '')
+and coalesce(date(se.requested_date), '') != coalesce(date_add(date(bst.requested_timestamp), INTERVAL 1 DAY), '')
+and coalesce(date(se.requested_date), '') != coalesce(date_sub(date(bst.requested_timestamp), INTERVAL 1 DAY), ''))
+or (coalesce(date(se.effective_date), '') != coalesce(date(bst.next_start_date), '')
+and coalesce(date(se.effective_date), '') != coalesce(date_add(date(bst.next_start_date), INTERVAL 1 DAY), '')
+and coalesce(date(se.effective_date), '') != coalesce(date_sub(date(bst.next_start_date), INTERVAL 1 DAY), ''))
 or coalesce(se.subscription_id, '') != coalesce(bst.subscription_id, '')
 or coalesce(se.phase_name, '') != coalesce(bst.next_slug, '')
-or coalesce(se.price_list_name, '') != coalesce(bst.next_price_list, '')
-or coalesce(se.created_date, '') != coalesce(bst.created_date, '')
+/* See https://github.com/killbill/killbill/issues/65: subscription_events won't have the pricelist but the SubscriptionEvent object will at runtime */
+or (se.price_list_name is not null and se.price_list_name != coalesce(bst.next_price_list, ''))
+/* TODO SubscriptionEvent is not an entity, we don't have that info yet (we currently look at audit logs but this doesn't work for in-memory events)
+or coalesce(se.created_date, '') != coalesce(bst.created_date, '') */
 or coalesce(se.account_record_id, '') != coalesce(bst.account_record_id, '')
-or coalesce(se.tenant_record_id, '') != coalesce(bst.tenant_record_id, '')
+or coalesce(se.tenant_record_id, '') != coalesce(bst.tenant_record_id, ''))
 ;
 
 -- J2

select *
from analytics_subscription_transitions b
join audit_log al on b.subscription_event_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'SUBSCRIPTION_EVENTS'
where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code, 'NULL')
or coalesce(b.created_comments, 'NULL') != coalesce(al.comments, 'NULL')
or coalesce(b.created_by, '') != coalesce(al.created_by, '')
;



from analytics_account_transitions bos
left outer join blocking_states bs on bs.record_id = bos.blocking_state_record_id
where coalesce(bs.record_id, 'NULL') != coalesce(bos.blocking_state_record_id, 'NULL')
or coalesce(bs.type, 'NULL') != 'ACCOUNT'
or coalesce(bs.state, 'NULL') != coalesce(bos.state, 'NULL')
/* TODO SubscriptionEvent is not an entity, we don't have that info yet
or coalesce(bs.created_date, 'NULL') != coalesce(bos.created_date, 'NULL') */
/* Tricky... Need to look at the account timezone */
or (coalesce(date(bs.effective_date), 'NULL') != coalesce(date(bos.start_date), 'NULL')
and coalesce(date(bs.effective_date), 'NULL') != coalesce(date_add(date(bos.start_date), INTERVAL 1 DAY), 'NULL')
and coalesce(date(bs.effective_date), 'NULL') != coalesce(date_sub(date(bos.start_date), INTERVAL 1 DAY), 'NULL'))
or coalesce(bs.account_record_id, 'NULL') != coalesce(bos.account_record_id, 'NULL')
or coalesce(bs.tenant_record_id, 'NULL') != coalesce(bos.tenant_record_id, 'NULL')
;
