0

visitor database on MySQL server looks like as below the id is a primary key integer type, firstname and lastname are text type and visitor id is an integer type

id  firstname       lastname    visitorid
1   userfirstname1      userlastname1   1
2   userfirstname2      userlastname2   2
3   userfirstname3      userlastname3   3
4   userfirstname4      userlastname4   4
5   userfirstname5      userlastname5   5

visits database MySQL server looks like as below the id is a primary key integer type, time is DateTime type and visitorid_id is a foreign key to visitor table (visitor->visitorid ) id time visitorid_id

id  scantime                visitorid_id    
433 2019-12-25 09:00:00.000000      1   
434 2019-12-25 18:00:00.000000      1   
435 2019-12-26 09:00:00.000000      1   
436 2019-12-26 18:00:00.000000      1   
437 2019-12-27 09:00:00.000000      1   
438 2019-12-27 18:00:00.000000      1   
439 2019-12-28 09:00:00.000000      1   
440 2019-12-28 18:00:00.000000      1   
441 2019-12-29 09:00:00.000000      1   
442 2019-12-29 18:00:00.000000      1   
443 2019-12-25 10:00:00.000000      2   
444 2019-12-25 17:00:00.000000      2   
445 2019-12-25 09:30:00.000000      3   
446 2019-12-25 17:30:00.000000      3   
447 2019-12-25 08:00:00.000000      5   
448 2019-12-25 18:00:00.000000      5   
449 2019-12-25 11:30:00.000000      4   
450 2019-12-25 17:30:00.000000      4

I need to generate reports from above my tables that show the following data

Date, visitor first name, visitor last name, 1st scan time, 2nd scan time, the time difference between 1st scan & second scan time in HH:MM:SS format.

I used the following links but could not get the correct SQL query that generates the required report a) How to concatenate text from multiple rows into a single text string in SQL server? b) Calculate the time difference between two timestamps in mysql

what is an optimized query to generate required reports for large no. of records.

Lin Du
  • 88,126
  • 95
  • 281
  • 483
san b
  • 1
  • You need to explain what is "1st scan time, 2nd scan time".. – Guru Dec 30 '19 at 03:28
  • 1st scantime, 2nd scan time mentioned in the description are as follows. as visitor passes through checkpoint its first pass is termed as first scan time, when visitor passes for second time then it is termed as second scan time, when visitor passes for third time it is termed as third scan time and so on. visitor can make multiple passes through checkpoint. first scan time is oldest and later scan times are newer one respectivity. Hope this clarified your query – san b Dec 30 '19 at 08:50

1 Answers1

0

This solution San will give you what you need for my POV. My understanding is that you want the first two visits. For the difference, I expressed it in seconds. You can convert to HH:MM:SS. I don't have the column Date because I don't know what you want there.

;with firstvisit as
(
select visitorid_id, min(scantime) as firstscan from visits a group by visitorid_id
)
select a.visitorid_id, firstname, lastname, a.firstscan, b.scantime as secundscan, TIMESTAMPDIFF(SECOND, b.scantime, a.firstscan) from firstvisit a 
inner join visitors v on v.visitorid = a.visitorid_id
inner join visits b on  a.visitorid_id = b.visitorid_id and b.scantime > a.firstscan 
where not exists(select 1 from visits c where a.visitorid_id = c.visitorid_id and c.scantime > a.firstscan and c.scantime < b.scantime)

Query without CTE:

select a.visitorid_id, firstname, lastname, a.firstscan, b.scantime as secundscan, TIMESTAMPDIFF(SECOND, b.scantime, a.firstscan) 
from (select visitorid_id, min(scantime) as firstscan from visits a group by visitorid_id) a 
inner join visitors v on v.visitorid = a.visitorid_id
inner join visits b on  a.visitorid_id = b.visitorid_id and b.scantime > a.firstscan 
where not exists(select 1 from visits c where a.visitorid_id = c.visitorid_id and c.scantime > a.firstscan and c.scantime < b.scantime)
zip
  • 3,938
  • 2
  • 11
  • 19
  • Thanks for your willingness to provide sql query, http://sqlfiddle.com/ says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';with firstvisit as ( select visitorid_id, min(scantime) as firstscan from visit' at line 1" – san b Dec 30 '19 at 14:29
  • Regarding your query for Date column, For example scantime contains both date and time information, requirement is to get report for every date for which there is scan time information available. consider following data rows in table 437 2019-12-27 09:00:00.000000 1 438 2019-12-27 18:00:00.000000 1 . in this case expected output is2019-12-27 ,userfirstname1 userlastname1, 09:00:00.000000 ,2019-12-27 18:00:00.000000 9:00 – san b Dec 30 '19 at 15:50
  • WITH clause is available with mysql version 8.0 but my hosting service provides mysql server version 5.6.40. So sql query that gives desired outcome for mysql server version 5.6.40 / 5.7 will be useful. – san b Jan 02 '20 at 08:24
  • Hi San B. Edited my answer to remove the CTE. Le me know – zip Jan 02 '20 at 08:30
  • Hi zip, query without CTE provided is run on sample data provided in the question ,it outputs data correctly for scantime on only one date that is 2019-12-25. it does not list data for other scantime dated 2019-12-26,2019-12-27,2019-12-28 and 2019-12-29 it needs to generate results for scantime on all dates so that it can be accepted as answer – san b Jan 02 '20 at 14:19