Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi All,

I have a table abc having column and data as :
Employee,Dhours,Dmins,MHours,MMins

Mohd Wasif,6,12,0,27

here Dhours,Dmins,MHours and MMins are int values.

now my problem is this I have to subtract MHours from Dhours and MMins from Dmins

but I am getting in negative but i have to get data as per hours and minutes
means if Dmins is less than MMins like in this case answer should be 45 instead of -15 and hours should be subtracted means 5 .

i used Sql query as

SQL
select Employee,Dhours,Dmins,MHours,MMins,(Dhours-MHours)as netHours,(Dmins-MMins) as netMins from abc


Please help me

[edit]Codeblock added - OriginalGriff[/edit]
Posted
Updated 27-Apr-11 20:23pm
v4

1 solution

I think this is a bit more fundamental that you think: Your query as given does not do what you describe.
In your description, you subtract MHours from DHours; in your query you subtract MMins from DHours.

It looks like you are trying to work out elapsed time: from a log in time and a log out time for example. Storing the hour and minute of log in/out is not a good way to do that.

I think what you really need to do is change the way you store things: Instead of storing Hours and Minutes for later math, store Date and time instead. Then when you do maths on them, you will get sensible results.

If you keep working with just the hour and the minute, then your math becomes more complex: you have to cope with day change, with "in at 2:45, out at 3:15" and "in at 2:15, out at 2:45" both generating 30 minutes, and so forth.
   
Comments
Mohd Wasif 28-Apr-11 2:38am
   
Actually that's not a problem .The thing is this I have two tables in one table there are multiples record for a date and MHours and MMins are values from first table and calculated on the basis of date time function and DHours and SMins from other table having only one record and saved data in ABC table
OriginalGriff 28-Apr-11 2:55am
   
Would you care to try explaining that and how it fits your question in English?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900