Report Project database application record

* Report Center statistics two modes of calculation

/*1. Working mode, use keyword action_date date to query all records */

SELECT * from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] '

and Channel= ' Facebook ' and Location_name= ' Huawei Arabia (UAE) '

and action_date>= ' 2017-8-17 00:00:00 '

and action_date<= ' 2017-8-17 23:59:59 '

and action_taken<> ' read ';

/*1.1 working mode, use the keyword action_date date to query the number of records */

SELECT Count (*) from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] '

and Channel= ' Facebook ' and Location_name= ' Huawei Arabia (UAE) ' and

action_date>= ' 2017-8-17 00:00:00 ' and action_date<= ' 2017-8-17 23:59:59 '

and action_taken= ' reply ';

/*1.2 key work mode, query reply time 1 hours or less of the record * *

Select Id,action_date,availability_date,tt from (select Id,action_date,availability_date,timestampdiff (MINUTE, availability_date,action_date) TT from Db_hw_report. Tb_xa_message_export_data

where user_login_name= ' [email protected] '

and Channel= ' Facebook ' and Location_name= ' Huawei Arabia (UAE) '

and action_date>= ' 2017-8-17 00:00:00 ' and action_date<= ' 2017-8-17 23:59:59 ' and action_taken<> ' read ') as AA where Tt>=0 and tt<60;

/*2. All records in non-operating mode, excluding all records of read type data */(filter out availability_date= action_date two dates same)

Select A.* from (select *,date_format (availability_date, '%y-%m-%d ') as Atime,date_format (action_date, '%y-%m-%d ') as Btime from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] ' and channel= ' Facebook '

and Location_name= ' Huawei Arabia (UAE) '

and availability_date>= ' 2017-8-1 00:00:00 '

and availability_date<= ' 2017-8-16 23:59:59 ') as a where a.atime = A.btime;

TRUNCATE TABLE db_hw_report.cheshi;/* Clear the Tables data */

SELECT * FROM db_hw_report.cheshi;/* creates a new table, primarily inserting data to calculate the adjacent work time difference */

SELECT * from Db_hw_report.tb_xa_message_export_data where user_login_name= ' [email protected] ' and channel= ' Facebook ' and Location_name= ' Huawei Arabia (UAE) ' and action_date>= ' 2017-8-17 00:00:00 ' and action_date<= ' 2017-8-17 23:59:5 9 ' and Action_taken <> ' Read ' ORDER by action_date;

/*1. Insert record in working mode */

Insert into Db_hw_report.cheshi (id,action_date) Select Id,action_date from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] '

and Channel= ' Facebook '

and Location_name= ' Huawei Arabia (UAE) '

and action_date>= ' 2017-8-21 00:00:00 ' and action_date<= ' 2017-8-21 23:59:59 '

and action_taken<> ' read ' ORDER by Action_date;

* Calculate the adjacent working time difference of the filter content */
Select R1.id,
R1.action_date,
R1.ord_num,
R2.ord_num,
R2.id,
R2.action_date,
Timestampdiff (Minute,r1.action_date, r2.action_date) sub_seconds
From (select (@i: = @i + 1) as Ord_num,
Info.id,
Info.action_date
From Db_hw_report.cheshi info, (select @i: = 1) d
Order by Info.action_date) as R1
Left JOIN (SELECT (@j: = @j + 1) as Ord_num,
Info.id,
Info.action_date
From Db_hw_report.cheshi info, (select @j: = 0) C
Order by Info.action_date) as R2
on r1.ord_num = R2.ord_num;

Report Project database application record