CODEWITHSUNDEEP
sqlsql-servert-sql
user3283187
user3283187

Reputation: 25

getting min and max date

My query:

with 
  CTE (CardID,Name,Surname,Department,CurrentDate,Time_In,Time_Out) as (
    select CardID, Name, Surname, Department, convert(char, pocdate, 106) as CurrentDate,
           case when GateID in (1,6) then min(convert(char,pocdate,108)) else '' end as Time_In,
           case when GateID in (2,5) then max(convert(char,pocdate,108)) else '' end as Time_Out     
      from tblEmployee
     where Name = 'erdal'
     group by CardID,Name,Surname,Department,GateID,pocdate
)
select CardID,Name,Surname,Department,CurrentDate,Time_In,Time_Out
from CTE
group by CardID,Name,Surname,Department,CurrentDate,Time_In,Time_Out     
order by CardID asc;

Result:

 CardID    Name    Surname   Department   CurrentDate    Time_In    Time_Out
--------- ------- --------- ------------ ------------- ----------- ----------
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     12:41:32
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     17:38:21
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     08:01:53
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     08:03:24
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     13:22:22
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     13:26:47
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     17:36:46

Is there a way to find only minimum Time_In and maximum Time_Out as below?

 CardID    Name    Surname   Department   CurrentDate    Time_In    Time_Out
--------- ------- --------- ------------ ------------- ----------- ----------
 6672983   ERDAL   HUZMELI   IT            11-Jan-16     08:01:53   17:38:21

Upvotes: 1

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Your query doesn't seem to need a CTE. You can just do the aggregation in one step. What you want is conditional aggregation:

Select CardID, Name, Surname, Department,
       convert(varchar(32), pocdate, 106) as CurrentDate,
       min(case when GateID in (1, 6) then convert(varchar(32), pocdate, 108))     
           end) as Time_In,
       max(case when GateID in (2, 5) then convert(varchar(32), pocdate, 108))     
           end) as Time_Out
from tblEmployee
where Name = 'erdal'
group by CardID, Name, Surname ,Department, kGateID,
         convert(varchar(32), pocdate, 106);

Note: In SQL Server, you should always include a length when using varchar() and char(). The default value varies by context and depending on the default might introduce hard-to-debug errors.

Upvotes: 1

Related Questions