analyzing and understanding SQL code

[haymen.roth.3]

i am given task to analyze and modify pre written large queries within small time, so please help me understanding it

with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , t1.RecDate AS [DateVisited]
FROM  AtdRecord t1 
INNER JOIN 
      AtdRecord t2 
ON    t1.EmplID = t2.EmplID 
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join 
      HrEmployee t3 
ON    t3.EmplID = t1.EmplID 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0 
        THEN CAST(hourpart as nvarchar(200))+':00' 
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as   'total time'
        FROM 
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
        from times) source

i understand first part but not exactly 2nd part:

SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0 
        THEN CAST(hourpart as nvarchar(200))+':00' 
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
        FROM 
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
        from times) source

[cereal]

This:

CASE WHEN minpart=0 
    THEN CAST(hourpart as nvarchar(200))+':00' 
    ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'

is a statement, like if-then-else:

• http://technet.microsoft.com/en-us/library/ms181765.aspx

if the minpart (stands for minute) column value is 0 then total time will be hourpart:00, if instead the minpart is not zero, then the total time will be the result of (hourpart-1):(minpart). The CAST() function is used to convert the result from a datetime column type to a string type:

• http://technet.microsoft.com/en-us/library/ms187928.aspx
• http://technet.microsoft.com/en-us/library/ms186939.aspx

The columns used by these statements are generated by the subquery aliased as source:

FROM
(
    SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
    DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
    DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
    from times) source

As you see, in this last part, the query uses the DATEDIFF() function to generate the values that will be used in the case statements: hourpart and minpart.

About DATEDIFF:

• http://technet.microsoft.com/en-us/library/ms189794.aspx