Sum fields

[gracehskuo]

hi please help.how write the query abs((attendmatch.timediff)-(employee.Workhours)and <>(employee.category='Restday') AS Diffhours

i want to get the Diffhours result abs((attendmatch.timediff)-(employee.Workhours)) AS Diffhours ,if overtime.categoryday =restday then diffours is =timediff ,if overtime.category is not = restday then Diffhours

this is current code:

SELECT DISTINCT attendmatch.employeeno, attendmatch.overtimedate, employee.Workhours, attendmatch.timediff, abs((attendmatch.timediff)-(employee.Workhours)and <>(employee.category='Restday') AS Diffhours, overtime.othours,overtime.categoryday,overtime.department
FROM attendmatch, employee, overtime
WHERE attendmatch.employeeno=employee.employeeno and attendmatch.employeeno=overtime.employeeno and attendmatch.overtimedate=overtime.overtimedate
GROUP BY attendmatch.employeeno, employee.employeeno, attendmatch.overtimedate, employee.Workhours, attendmatch.timediff, overtime.othours, overtime.categoryday,
overtime.department

thanks for help.

[Hack]

Moved To Database Development

[szlamany]

You need to always tell us what version of SQL you use - MS SQL, mySql, Access - they all have different syntax for this.

If it's MS SQL

Sum(Case When overtime.categoryday='restday' Then SomeField Else 0 End)

This SUM() will aggregate based on another fields value.

CASE isn't in ACCESS - instead you use an IIF() function that has three parameters.

[gracehskuo]

szlamany:
hi,this database is using ms.access.

[szlamany]

Then the syntax changes to (I believe - I don't do ACCESS)

Sum(IIF(overtime.categoryday='restday',SomeField,0))

Does this make sense?