· 4 years ago · May 24, 2021, 02:22 PM
1CREATE TEMPORARY TABLE IF NOT EXISTS tmp_avt_service_modified_punchlog AS
2SELECT
3 `data`.`UserId`,
4 `data`.`Nume`,
5 (
6 SELECT
7 IF (
8 EXISTS (
9 SELECT
10 `modgoin`.`device_datetime`
11 FROM
12 `punchlog` `goin`,
13 `modifiedpunchlog` `modgoin`
14 WHERE
15 (
16 (`data`.`id` = `modgoin`.`oplid`)
17 AND (
18 `data`.`devdt` = `modgoin`.`original_device_datetime`
19 )
20 AND (`data`.`type` = `modgoin`.`type`)
21 AND (`data`.`type` = 'PUNCH_TYPE_CHECK_IN')
22 )
23 LIMIT
24 1
25 ), (
26 SELECT
27 `modgoin`.`device_datetime`
28 FROM
29 `modifiedpunchlog` `modgoin`
30 WHERE
31 (
32 `data`.`id` = `modgoin`.`oplid`
33 AND `data`.`devdt` = `modgoin`.`original_device_datetime`
34 AND `data`.`type` = `modgoin`.`type`
35 AND `data`.`type` = 'PUNCH_TYPE_CHECK_IN'
36 )
37 LIMIT
38 1
39 ), `data`.`Intrare`
40 )
41 ) AS `Intrare`,
42 (
43 SELECT
44 IF (
45 EXISTS (
46 SELECT
47 `modgoin`.`device_datetime`
48 FROM
49 `modifiedpunchlog` `modgoin`
50 WHERE
51 `data`.`id` = `modgoin`.`oplid`
52 AND `data`.`devdt` = `modgoin`.`original_device_datetime`
53 AND `data`.`type` = `modgoin`.`type`
54 AND `data`.`type` = 'PUNCH_TYPE_CHECK_OUT'
55 LIMIT
56 1
57 ), (
58 SELECT
59 `modgoin`.`device_datetime`
60 FROM
61 `punchlog` `goin`,
62 `modifiedpunchlog` `modgoin`
63 WHERE
64 `data`.`id` = `modgoin`.`oplid`
65 AND `data`.`devdt` = `modgoin`.`original_device_datetime`
66 AND `data`.`type` = `modgoin`.`type`
67 AND `data`.`type` = 'PUNCH_TYPE_CHECK_OUT'
68 LIMIT
69 1
70 ), `data`.`Iesire`
71 )
72 ) AS `Iesire`
73FROM
74 `tmp_avt_service_initial_data` `data`;