sql merge rows in the same table-CodePudding

i need help to join colum on the same table, i have one table like this :

mat   id      name            date          time
4   138644  YACINE          20160201    130938 entry
4   138645  YACINE          20160201    204033 leave
4   149540  YACINE          20160223    80614
4   149541  YACINE          20160223    160224
31  131905  BENHAOUA        20160118    80459  entry
31  131906  BENHAOUA        20160118    154738 leave
31  485939  BENHAOUA        20160406    54113
31  486091  BENHAOUA        20160406    132152

if you can see for each name (mat) i have two line for the same date, the first date reprent entry time and the second leave time, i want to have only one ligne for same name and same date with entry and leave time like this

mat  name      date        entry   leave
4   YACINE     20160201   130938   204033
4   YACINE     20160223    80614   160224
31  BENHAOUA   20160118    80459   154738
31  BENHAOUA   20160406    54113   132152

i try with inner join in the same table but results was false, i try this :

select a.id, a.matricule, a.nom,a.edate, a.etime as entree, b.etime as sortie  
from shift1 a
inner join shift2016 b on a.matricule=b.matricule and a.EDATE=b.EDATE and a.etime<>b.etime 
order by  matricule

CodePudding user response：

Please try this :-

SELECT MAT
    ,NAME
    ,DATE
    ,MIN(TIME) AS entry
    ,MAX(TIME) AS leave
FROM Persons
GROUP BY mat
    ,NAME
    ,DATE

CodePudding user response：

Below sample should produce the output you require. This is achieved using pivot operator.

declare @tbl table(mat int,id int,name varchar(20)
,date int,time int)

insert into @tbl
values(4,138644,'YACINE',20160201,130938)
,(4,138645,'YACINE',20160201,204033)
,(4,149540,'YACINE',20160223,80614)
,(4,149541,'YACINE',20160223,160224)
,(31,131905,'BENHAOUA',20160118,80459)
,(31,131906,'BENHAOUA',20160118,154738)
,(31,485939,'BENHAOUA',20160406,54113)
,(31,486091,'BENHAOUA',20160406,132152)


select *, case when ROW_NUMBER()
over(partition by mat,date order by id) % 2 <> 0 then 'Entry' else 'Leave' 
end as [status] 
into #temp
from
@tbl

select mat,name,date,Entry,Leave 
from
(select mat,name,date,time,status 
from 
#temp)t
pivot
(max(time) for status in([Entry],[Leave]))p

drop table #temp

CodePudding user response：

Based on my comment your query would be:

SELECT mat
    ,name
    ,DATE
    ,MIN(TIME) AS entry
    ,MAX(TIME) AS leave
FROM shift1
GROUP BY mat
    ,name
    ,DATE