how to store "count of minute' into hours

Question

3.00/5 (1 vote)

See more:

SQL-Server-2008

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

please help me

Posted 13-Feb-14 1:57am

Member 10562086

Add a Solution

Comments

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?

4 solutions

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)
       as OTHOURPad,

Posted 13-Feb-14 11:28am

mehdijafary

Updated 14-Feb-14 3:26am

v2

Comments

Member 10562086 14-Feb-14 0:05am

sir what is this CAST(count(MerchantInfo.MerchantId) % 60 as varchar(2)) ,2)
as OTHOURPad,
i dint use MercantInfo .MerchantId in my table

mehdijafary 14-Feb-14 9:27am

Sorry sir, it was a mistak. it's updated

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'),'')

Posted 13-Feb-14 23:30pm

VeeraAnnapureddy

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.

Posted 14-Feb-14 1:51am

Simon_Whale

Updated 14-Feb-14 1:52am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

george4986 · Accepted Answer · 2014-02-13T23:22:00

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