Dunno oracle well, but here's a couple of ideas: First, are there nulls in the field you're taking the sum of? This may case erratic results. Try SUM(ISNULL(tbldetailentry.reghrs),0) instead.
Also, be careful with date comparisons. They don't trim off the time portion in sql server. Perhaps there's a time portion in the fields. Try trimming of the time by casting the database field as float, taking the floor, and casting back as date time. Or alternatively you can use tblDetailEntry.recorddate >= '10/01/2001 00:00:00' and tblDetailEntry.recorddate <= '10/01/2001 23:59:59' The equals operator always takes time into account for these fields in sql server. I suspect that it's doing the same in your Oracle database... -----Original Message----- From: Angel Stewart [mailto:[EMAIL PROTECTED]] Sent: Monday, November 19, 2001 10:55 AM To: CF-Talk Subject: SQL To Sum data -------------------------- SELECT SUM(tbldetailentry.reghrs) AS Daystotals FROM tblMainEntry,TblDetailEntry WHERE tbldetailentry.MainID = tblMainEntry.MainID AND tblDetailEntry.recorddate = to_date('#currentdate#','mm/dd/yyyy') AND tblMainEntry.empid = #client.emplid# AND Attendance_Code > 8000 --------------------- Can you folks find anything wrong with this code? The idea is to simply add up all the Hours that a particular employee worked on a day specified by Current Date, where the Attendance code is greater than 8000 indicating a Vacation day. It sounds just perfect in theory, but in practice it totally is not working. It is missing out the first day, and it somehow gets ridiculous values for the SUM. I wonder if there is something fundamentally wrong with the SQL. -Gel ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
