sql - getting min and max date -

my query:

with    cte (cardid,name,surname,department,currentdate,time_in,time_out) (     select cardid, name, surname, department, convert(char, pocdate, 106) currentdate,            case when gateid in (1,6) min(convert(char,pocdate,108)) else '' end time_in,            case when gateid in (2,5) max(convert(char,pocdate,108)) else '' end time_out            tblemployee      name = 'erdal'      group cardid,name,surname,department,gateid,pocdate ) select cardid,name,surname,department,currentdate,time_in,time_out cte group cardid,name,surname,department,currentdate,time_in,time_out      order cardid asc; 

result:

 cardid    name    surname   department   currentdate    time_in    time_out --------- ------- --------- ------------ ------------- ----------- ----------  6672983   erdal   huzmeli              11-jan-16     12:41:32  6672983   erdal   huzmeli              11-jan-16     17:38:21  6672983   erdal   huzmeli              11-jan-16     08:01:53  6672983   erdal   huzmeli              11-jan-16     08:03:24  6672983   erdal   huzmeli              11-jan-16     13:22:22  6672983   erdal   huzmeli              11-jan-16     13:26:47  6672983   erdal   huzmeli              11-jan-16     17:36:46 

is there way find minimum time_in , maximum time_out below?

 cardid    name    surname   department   currentdate    time_in    time_out --------- ------- --------- ------------ ------------- ----------- ----------  6672983   erdal   huzmeli              11-jan-16     08:01:53   17:38:21 

your query doesn't seem need cte. can aggregation in 1 step. want conditional aggregation:

select cardid, name, surname, department,        convert(varchar(32), pocdate, 106) currentdate,        min(case when gateid in (1, 6) convert(varchar(32), pocdate, 108))                 end) time_in,        max(case when gateid in (2, 5) convert(varchar(32), pocdate, 108))                 end) time_out tblemployee name = 'erdal' group cardid, name, surname ,department, kgateid,          convert(varchar(32), pocdate, 106); 

note: in sql server, should always include length when using varchar() , char(). default value varies context , depending on default might introduce hard-to-debug errors.