سؤال مهم

ما لدى هو كالآتى

select e.[name] , i.dateIn,i.dateOut ,dateDiff(n,datein,dateout) as ss

from inout i

join employee e on i.username=e.username

ما أريده هو ان أقوم بتحديد عدد الساعات و الدقائق بالضبط

You could use simple calculation to get the hours and minutes and then convert them to a string and store in another field

Select TimeInMinutesAndHours = CAST(SUM(DATEDIFF(n, datein, dateout))/60 as varchar) + '.' + CAST(SUM(DATEDIFF(n, datein, dateout)) - (SUM(DATEDIFF(n, datein, dateout))/60)*60 AS varchar) FROM Table group by id

hope this helps

Madani

شكرا يا أخى على تعاونك معى :)

و لكن كيف لى ان أقوم بعمل Sum على مجموع عددالساعات فى الشهر بالنسبة لشخص معين لاانى حاولت ان أقوم بعمل ذلك الا انه ظهرت لى رسالة تقول

The sum or average aggregate operation cannot take a varchar data type as an argument.

use cast to get a decimal from the varchar table.also ,you need to understand that 1 hour and 20 minutes is not 1.20 ,so we need to get the representation of the hours in decimal .for example 1 hour and 20 minutes is equal to 1.33 since a 20 minute is a third of an hour.this is quite important when calculating salaries .

assuming that you put the varchar in a filed called g

select 
sum(cast(cast(g as decimal(5,2)) as int)  + (cast(cast(g as decimal(5,2)) - cast(cast(g as decimal(5,2)) as int)as decimal(5,2))*100/60)
)
From Table

Madani

أخى أنا الأن مشوش تماما لأن فى ما يخص أول Statement وضعتها هنا كانت تأتى بعدد الساعات VarChar و الان اعطيتنى Statement لل Cast الأن كيف لى أن أحدد عدد الساعات مرة أخرى بأن تكون Decimal بحيث تعطى الساعات بالضبط

و شكرا أخى على مجهودك معى جزاك الله كل خير

the varcvhar values will have the exact time .for example 4.12 is 4 hours and 12 minutes.this is in my first reply.

just sum the values of this varchar field after casting it as shown in my second repy (change g with your varch field name) and you will get the exact number of hours and minutes

شكرا لك أخى الكريم الأن المسألة دى ماشيه تمام الحمد لله