Sum the Counts
-
Hi All, Iam new to database. Here iam using sql server 2000. i want to sum the counts from a table. I need this to calculate the employees who have come late in a week/month. Example: I have table such as Table1:tbl_attandence(it contains the employee id and the intimings and outtime) Fields:empcode,intime,outime Eg: EMP001,11/1/2011 09:30:45 AM,11/30/2011 06:30:15 PM I am attaching my code such that it return the count but iam unable to sum the values because it is showing the result as shown below DECLARE @actualTime DATETIME DECLARE @lateTime DATETIME SELECT @actualTime = '9:41' Select @lateTime ='10:00' select count(convert(varchar,intime,105)) as dates,CONVERT(VARCHAR(5),intime,108) AS Hours from tbl_attendance where empcode='EMP001'; and intime between '2011-11-01' and '2011-11-30' and CONVERT(VARCHAR(5),intime,108) >=@actualTime and CONVERT(VARCHAR(5),intime,108) <= @lateTime group by CONVERT(VARCHAR(5),intime,108) OytPut: days intime 1 09:41 1 09:44 1 09:46 2 09:53 1 09:54 Now i need to sum all the days and get the sum as 7 Please suggest that how can i do this task any help will be appreciated
-
Hi All, Iam new to database. Here iam using sql server 2000. i want to sum the counts from a table. I need this to calculate the employees who have come late in a week/month. Example: I have table such as Table1:tbl_attandence(it contains the employee id and the intimings and outtime) Fields:empcode,intime,outime Eg: EMP001,11/1/2011 09:30:45 AM,11/30/2011 06:30:15 PM I am attaching my code such that it return the count but iam unable to sum the values because it is showing the result as shown below DECLARE @actualTime DATETIME DECLARE @lateTime DATETIME SELECT @actualTime = '9:41' Select @lateTime ='10:00' select count(convert(varchar,intime,105)) as dates,CONVERT(VARCHAR(5),intime,108) AS Hours from tbl_attendance where empcode='EMP001'; and intime between '2011-11-01' and '2011-11-30' and CONVERT(VARCHAR(5),intime,108) >=@actualTime and CONVERT(VARCHAR(5),intime,108) <= @lateTime group by CONVERT(VARCHAR(5),intime,108) OytPut: days intime 1 09:41 1 09:44 1 09:46 2 09:53 1 09:54 Now i need to sum all the days and get the sum as 7 Please suggest that how can i do this task any help will be appreciated
Elizabeth Rani wrote:
Now i need to sum all the days and get the sum as 7
Seems to add up to 6! You can used compute after you query in the proc
compute sum(Days)
or put the existing query into another outer query
Select sum(days) from (put your query here)
Never underestimate the power of human stupidity RAH