Retrieve start and last created date from a duplicated record

I have a table called mov_entrys and they have multiple historic records, in other words, duplicated records this is my main SQL statement

SELECT me.mov_entry_id, me.mov_entry_ean13, me.plate, mv.name vehiclename,
                ma.name areaname, ml.name locationame, me.value, mov_pay.name paytype ,me.validated, me_usu.name operatorName,
                to_char(me.created_at, 'DD/MM/YYYY HH24:MI:SS') created_at, 
                to_char(me.updated_at, 'DD/MM/YYYY HH24:MI:SS') updated_at
                FROM mov_entrys me
                LEFT OUTER JOIN mov_payment_type mov_pay on mov_pay.mov_payment_type_id = me.mov_payment_type_id
                JOIN mov_vehicles mv ON me.mov_vehicle_id = mv.mov_vehicle_id            
                JOIN mov_areas ma ON me.mov_area_id = ma.mov_area_id        
                JOIN mov_locations ml ON ma.mov_location_id = ml.mov_location_id
                JOIN mov_users me_usu ON me.mov_user_id = me_usu.mov_user_id
                WHERE me.value > 0
                AND me.validated <> 'O'
                AND me.validated <> 'V'
                AND me.validated <> 'I'
                AND me.created_at >= date_trunc('month', NOW()) - '1 month'::interval
                order by mov_entry_id desc

But the records are like this example:

id ean13         value validated created_at            updated_at
1  7800003378198 0     N         2019-10-04 09:00:31   2019-10-04 09:00:31
2  7800003378198 8     S         2019-10-04 13:01:11   2019-10-04 13:01:11
3  7800003378198 10.5  AD        2019-10-04 13:02:13   2019-10-04 13:02:13
3  7800003378198 10.5  I         2019-10-04 13:05:13   2019-10-04 13:05:13

In Laravel when a data is created the created_at and updated_at is the same data, but I want the start date and last date from this record that has the same ean13 data 7800003378198

The main query returns the record that I want but with the same data and I want the real created data and last data

if you think the record in the top, I want the return like this, if I use group I cant have like this

id ean13         value validated created_at            updated_at
2  7800003378198 8     S         2019-10-04 09:00:31   2019-10-04 13:05:13
3  7800003378198 10.5  AD        2019-10-04 09:00:31   2019-10-04 13:05:13

I try to make a subquery but I don’t know why always make an INFINITE load to return a data I think I’m doing something wrong, this is my query with subquery

SELECT me.mov_entry_id, me.mov_entry_ean13, me.plate, mv.name vehiclename,
                ma.name areaname, ml.name locationame, me.value, mov_pay.name paytype ,me.validated, me_usu.name operatorName,
                to_char(me.created_at, 'DD/MM/YYYY HH24:MI:SS') created_at, 
                to_char(me.updated_at, 'DD/MM/YYYY HH24:MI:SS') updated_at,             
                (               
                    SELECT me_v2.created_at FROM mov_entrys me_v2 WHERE me.mov_entry_ean13 = me_v2.mov_entry_ean13
                    ORDER BY me_v2.created_at DESC
                    LIMIT 1                 
                ) date_test             
                FROM mov_entrys me
                LEFT OUTER JOIN mov_payment_type mov_pay on mov_pay.mov_payment_type_id = me.mov_payment_type_id
                JOIN mov_vehicles mv ON me.mov_vehicle_id = mv.mov_vehicle_id            
                JOIN mov_areas ma ON me.mov_area_id = ma.mov_area_id        
                JOIN mov_locations ml ON ma.mov_location_id = ml.mov_location_id
                JOIN mov_users me_usu ON me.mov_user_id = me_usu.mov_user_id
                WHERE me.value > 0
                AND me.validated <> 'O'
                AND me.validated <> 'V'
                AND me.validated <> 'I'
                AND me.created_at >= date_trunc('month', NOW()) - '1 month'::interval
                order by mov_entry_id desc

When I execute this query alone it is working fine giving me the last record, what I’m doing wrong? This is my test query :

SELECT me.created_at FROM mov_entrys me WHERE me.mov_entry_ean13 = '7800003378198'
                ORDER BY me.created_at DESC
                LIMIT 1

I mana

Answer

Consider aggregating on dates with MIN and MAX, grouping by all other SELECT columns. Below uses shorter aliases for readability:

SELECT e.mov_entry_id, e.mov_entry_ean13, e.plate, v.name AS vehiclename,
       a.name AS areaname, l.name AS locationame, e.value, p.name AS paytype, 
       e.validated, u.name AS operatorName,
       to_char(MIN(e.created_at), 'DD/MM/YYYY HH24:MI:SS') AS created_at, 
       to_char(MAX(e.updated_at), 'DD/MM/YYYY HH24:MI:SS') AS updated_at
FROM mov_entrys e
LEFT OUTER JOIN mov_payment_type p ON p.mov_payment_type_id = e.mov_payment_type_id
INNER JOIN mov_vehicles v ON e.mov_vehicle_id = v.mov_vehicle_id            
INNER JOIN mov_areas a ON e.mov_area_id = a.mov_area_id        
INNER JOIN mov_locations l ON a.mov_location_id = l.mov_location_id
INNER JOIN mov_users u ON e.mov_user_id = u.mov_user_id
WHERE e.value > 0
  AND e.validated <> 'O'
  AND e.validated <> 'V'
  AND e.validated <> 'I'
  AND e.created_at >= date_trunc('month', NOW()) - '1 month'::interval
GROUP BY e.mov_entry_id, e.mov_entry_ean13, e.plate, v.name,
         a.name,l.name, e.value, p.name, e.validated, u.name
ORDER BY e.mov_entry_id desc

Advertisement

Answer