# how to set sum of "HH:mm" to "00:00" if is null ? [Answered]RSS

## 3 replies

Last post Feb 02, 2015 04:06 PM by a.amin

Member

51 Points

436 Posts

### how to set sum of "HH:mm" to "00:00" if is null ?

Hi

i have create an query that get sum of overtime hours the Employee works - it works very good but i need to value as "00:00" if result is null

```SELECT        day_data.Emplo_ID,

Employee_Data.Emplo_Name,

-- Over Time
RTRIM(SUM(DATEDIFF(MINUTE, '00:00', over_time)) / 60) + ':' + RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', over_time)) % 60), 2) AS [Total Over Time],

-- Ezn Time
RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) / 60) + ':' + RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) % 60), 2) AS [Total Ezn Time]

FROM          Employee_Data INNER JOIN day_data ON Employee_Data.Emplo_ID = day_data.Emplo_ID

WHERE         day_data.day_date BETWEEN '2014-12-01' AND '2014-12-21' AND over_time != '00:00'

GROUP BY      Employee_Data.Emplo_Name,

day_data.Emplo_ID

ORDER BY      day_data.Emplo_ID```

so how can i do that ?

__________________
Best Regards
Eng.Ahmed Amin
ahmed.mo.amin@gmail.com

All-Star

50540 Points

14840 Posts

### Re: how to set sum of "HH:mm" to "00:00" if is null ?

Try like this

`ISNULL(RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) / 60) + ':' + RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) % 60), 2), '00:00') AS [Total Ezn Time]`
`ISNULL(<calculation>, '00:00') as ...`

Programming to simplify, don't look for hardway ...

Contributor

5450 Points

1123 Posts

### Re: how to set sum of "HH:mm" to "00:00" if is null ?

Hi,

Try:

```         COALESCE
(RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) / 60) +
':' +
RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) % 60), 2),
'00:00') AS [Total Ezn Time]```

Hope this helps.

Member

51 Points

436 Posts

### Re: how to set sum of "HH:mm" to "00:00" if is null ?

#### oned_gk

Try like this

`ISNULL(RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) / 60) + ':' + RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', eznFromOver)) % 60), 2), '00:00') AS [Total Ezn Time]`
`ISNULL(<calculation>, '00:00') as ...`

Thanks a lot it work great now :) ...

__________________
Best Regards
Eng.Ahmed Amin
ahmed.mo.amin@gmail.com