15,743,609 members
3.00/5 (1 vote)
See more:
SQL
```select distinct(Emp_Status),
count ( Att_Mints/60) as OTHOUR,
COUNT (Att_Totalmints /60 ) as ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode
where year(Att_Date)='2014' and Month (Att_Date)='1'
group by Emp_Status```

in above query Count of
`Att_Mints`
is =150
and count of
`Att_Totalmints`
is 120

i want output of OTHOURS=1:30min
and
`ProductionHours`
=2 hour

Posted
Simon_Whale 13-Feb-14 11:35am
is it really important SQL does this? or could you let your application do the formatting of this?

## Solution 4

Have a look at that

SQL
```declare  @minutes int
set @minutes = 234

select right('00' + cast(@minutes % 60 as varchar(2)),2) as [mins]
select cast((@minutes / 60) as varchar(2)) as [hrs]

select cast((@minutes / 60) as varchar(2)) + ':' + right('00' + cast(@minutes % 60 as varchar(2)),2) + ' mins'
```

BUT: Personally I would do this sort of manipulation outside of the database as generally the front end is more effectient at string manipulation than a database.

v2

## Solution 3

`SELECT ISNULL(NULLIF((RIGHT('000'+CAST(@Minutes/60 AS VARCHAR(32)),3) +':'+ RIGHT('0'+CAST(@Minutes % 60 AS VARCHAR(32)),2)),'000:00'),'')`

## Solution 2

plz check this query.
SQL
```select distinct(Emp_Status),
right('00'+cast((count ( Att_Mints/60) / 60) as varchar(2)),2) +
CASE (cast((count ( Att_Mints/60) % 60) as varchar(2))) when 0 then ' Hour'
else (':' + right('00'+cast((count ( Att_Mints/60) % 60) as varchar(2)),2)+' min') end as OTHOUR,
right('00'+cast((count ( Att_Totalmints/60) / 60) as varchar(2)),2) +
CASE (cast((count ( Att_Totalmints/60) % 60) as varchar(2))) when 0 then ' Hour'
else (':' + right('00'+cast((count ( Att_Totalmints/60) % 60) as varchar(2)),2)+' min')
end as   ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode
where year(Att_Date)='2014' and Month (Att_Date)='1'
group by Emp_Status```

## Solution 1

There are several ways to do that.
if you could handle it in your application it would be better but anyways there you are

SQL
```select distinct(Emp_Status),
count ( Att_Mints/60) as OTHOUR,
cast(count(Att_Mints) / 60 as varchar(3))+
':'+
cast(count(Att_Mints) % 60 as varchar(3))
as OTHOUR2,
COUNT (Att_Totalmints /60 ) as ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode
where year(Att_Date)='2014' and Month (Att_Date)='1'
group by Emp_Status```

in the case that you want to pad it you could use the following :

SQL
```cast(count(Att_Mints) / 60 as varchar(3))+
':'+
right('0' + CAST(count(Att_Mints) % 60 as varchar(2)) ,2)

v2
Member 10562086 14-Feb-14 0:05am
sir what is this CAST(count(MerchantInfo.MerchantId) % 60 as varchar(2)) ,2)
i dint use MercantInfo .MerchantId in my table
mehdijafary 14-Feb-14 9:27am
Sorry sir, it was a mistak. it's updated