select ma.fspro_userId as userId, trim( ma.UserName ) as username, ma.eventcd, ma.CompanyName, trim( coalesce(ec.eventDescription, ma.eventcd) ) as eventDescription, ma.user_agent, '' as deviceType, '' as deviceOS, fsp.title, fsp.ownerid, fsp.fsl_tablecode, cast(ma.eventtime as date) eventdate, left(DATENAME (dw, ma.eventtime),2) + ' ' + cast(month(ma.eventtime) as varchar) + '/' + cast(day(ma.eventtime) as varchar) as shortDate, ma.EventDetail, trim(cho.cho_name) as chainOperatorName from tbl_TRK_MemberActivity ma with (nolock) left outer join tbl_trk_eventcodes ec with (nolock) on ec.eventcd = ma.eventcd and ec.siteContext like '%mobile-crm%' left outer join tbl_fspro_members fsp with (nolock) on fsp.fspro_userid = ma.fspro_userid left outer join tbl_cho_operators cho with (nolock) on cho.cho_id = fsp.ownerid and fsp.fsl_tablecode = 'cho' where ma.Site = 'mobile-crm' and ma.IP_Address not like '192.168%' and ma.EventDetail not like '%demo%' and ma.EventDetail like '%demo%' and ma.EventCD <> 'LI' and ma.CompanyName <> '' and ( ma.eventcd not in ('PRDS','ACTS','CONS','OPPS') or ma.eventdetail like '%"pg":"1"%' ) and ( ma.eventtime >= '#client.usr_lst_srchon_startdate#' and ma.eventtime <= '#client.usr_lst_srchon_enddate#' ) and DATEDIFF( d, ma.eventtime,getdate() ) #iif( client.usr_lst_srchon_timePeriod lt 2, de('='), de('<=') )# and fsp.fsl_tablecode = select count(*) as transactions, userId, UserName, deviceType, deviceOS, eventcd, CompanyName, eventcd, eventDescription, title from qryRawUserActivity where 1 = 1 and deviceOS = '#client.usr_lst_srchon_platform#' and deviceType = '#client.usr_lst_srchon_device#' and eventcd = '#client.usr_lst_srchon_eventcd#' and companyname = '#client.usr_lst_srchon_company#' and chainOperatorName = '#client.usr_lst_srchon_chainOperator#' and ( UPPER(eventdetail) like '%VARIANT:#ucase(client.usr_lst_srchon_appVariant)#%' or ( UPPER(eventdetail) not like '%VARIANT:%' and UPPER(user_agent) like '%FSE:VARIANT=#ucase(_userAgentVariantString)#%' ) ) group by userId, UserName, deviceType, deviceOS, eventcd, CompanyName, eventDescription, title select distinct companyName from qryRawUserActivity order by companyName select distinct chainOperatorName from qryRawUserActivity where chainOperatorName is not null order by chainOperatorName select distinct eventcd, eventdescription from qryRawUserActivity order by eventdescription select distinct deviceOS from qryRawUserActivity order by deviceOS select distinct deviceType from qryRawUserActivity order by deviceType select eventdate, shortdate, count( distinct userName ) as users from qryRawUserActivity where companyname = '#client.usr_lst_srchon_company#' where chainOperatorName = '#client.usr_lst_srchon_chainOperator#' group by eventdate, shortdate select sum(transactions) as ttl, userId, username, companyname, title from qryUserActivity group by userId, username, companyname, title order by ttl desc, username select sum(transactions) as transactions, eventcd, eventDescription from qryUserActivity group by eventcd, eventDescription select 0 as transactions, eventcd, eventdescription from tbl_trk_eventcodes where siteContext like '%mobile-crm%' and eventcd <> 'LI' and eventcd not in () select cast(transactions as integer) as transactions, eventcd, eventDescription from qryTransactionLeaderboard union select cast(transactions as integer) as transactions, eventcd, eventDescription from qryIdleTransactions order by transactions desc, eventDescription select deviceOS, userName from qryUserActivity group by deviceOS, userName select count(*) as ttl, deviceOS from qryPlatform group by deviceOS order by ttl desc, deviceOS select deviceType, userName from qryUserActivity group by deviceType, userName order by username select count(*) as ttl, deviceType from qryDevice group by deviceType order by ttl desc select distinct ownerid from qryRawUserActivity where fsl_tablecode = 'BRO' select distinct ownerid from qryRawUserActivity where fsl_tablecode = 'MFR' select 0 as ttl, 0 as userttl, case when memberownertype = 'bro' then broker_name else mfr_name end as companyname from fsenablers..tbl_CUS_PortalClientMembers pcm left outer join tblbrokers bro on bro.broker_id = pcm.memberownerid and pcm.memberownertype = 'bro' left outer join tblmanufacturers mfr on mfr.mfr_id = pcm.memberownerid and pcm.memberownertype = 'mfr' where pcm.websiteid = 718 and ( ( memberownerid not in () and memberownertype = 'bro' ) or ( memberownerid not in () and memberownertype = 'mfr' ) ) order by companyname select sum(transactions) as ttl, count(distinct username) as userttl, companyname from qryUserActivity group by companyname union select count(ttl) as ttl, count( userttl ) as userttl, companyname from qryInactiveClients group by companyname order by userttl desc, companyname select *, username + ' (' + deviceOS + ', ' + devicetype + ')' as grouping from qryUserActivity where userId = order by CompanyName, UserName, deviceOS, deviceType, transactions desc select count(distinct UserName) as userCount from qryUserActivity select sum(transactions) as transactions from qryUserActivity Totals (no activity) (#qryUserCount.userCount# Users, #qryTransactionTotal.transactions# Transactions)