Count and Add Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count and Add Query

Author

Topic

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 12:53:39

Hi, I am new here and would like to ask about getting some values I need.
I am also not very familiar with sql and have some problems.
I have 2 tables that are related to each other.
The 1st table is my Staff table which has the PK staffID
The 2nd table is like a attendance record checking who is present, represented by 1 and absent represented by 0. It has 2 similar entries per day per staffId. Morning and afternoon shift.

What I am trying to do is to add up the total number of the 1s each staff has for a month.
The data looks like this according to the columns I have:
staffDate, staffID, staffAttendance, staffWorkType
2000-01-01, s12345678, 1, Normal
2000-01-01, s12345678, 1, Normal

I got advice on how to start work on it and have this query.
select * from staffRecords
where attendance = 1
Join staff
On attendance.staffID = staff.staffID
Sum(attendance)
group by staffID, (???)

(???) is suppose to be month of staffDate but I do not know what to put for it.
Even putting that aside, when I tried to run w/o it, I will have error with my Join.
And after getting the sum, how can I turn it to percentage for attendance rate(like 90% turn up for current month)?
Can someone please tell me what's wrong and how can I fix it?
Thanks.

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:06:03

What I am trying to do is to add up the total number of the 1s each staff has for a month.



select 
staffID,
datepart(MM,staffDate),
sum(staffAttendance)over(order by staffID,datepart(MM,staffDate))
from yourtable
group by staffID,datepart(MM,staffDate)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 13:41:21

Thanks for the reply.
I tried the query posted but it says
Incorrect syntax near 'order'.

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:48:08

quote:
Originally posted by Kumoka

Thanks for the reply.
I tried the query posted but it says
Incorrect syntax near 'order'.

Sorry try this


select 
staffID,
datepart(MM,staffDate),
sum(staffAttendance)
from yourtable
group by staffID,datepart(MM,staffDate)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 13:54:09

quote:
Originally posted by Idera

quote:
Originally posted by Kumoka

Thanks for the reply.
I tried the query posted but it says
Incorrect syntax near 'order'.

Sorry try this

select
staffID,
datepart(MM,staffDate),
sum(staffAttendance)
from yourtable
group by staffID,datepart(MM,staffDate)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

You can also try this...if the data spans over different years. Datepart(MM,datetime) will then sum all years together.

select staffID,convert(varchar(6),staffDate,112),SUM(staffAttendance)
from yourtable
group by staffID,convert(varchar(6),staffDate,112)

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 14:03:34

Cool.
Thanks, it works!
Thank you both ^_^

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 14:53:21

Right now I am using this:

select stffID,
CONVERT(varchar(6),aDate,112),
SUM(attendance),
COUNT(staffId),
((SUM(attendance) / COUNT(staffId)) * 100)
from attendance
group by staffId,
CONVERT(varchar(6), aDate,112)

Now I have a question regarding this line
((SUM(attendance) / COUNT(staffId)) * 100)
If I changed the / to +,- or *, it works so I am guessing it does not take decimal values as it returns all as 0 instead.
Let's say it is (30/36)*100 I will get 83.333(inf)
How can I get the value as 83?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 15:09:28

[code]select staffID,
CONVERT(varchar(6),aDate,112),
SUM(attendance),
COUNT(staffId),
ROUND(((SUM(attendance) * 1.0) /COUNT(staffId) * 100),0)
from attendance
group by staffId,
CONVERT(varchar(6), aDate,112)[/code]

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 15:27:09

Thank you very much

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 15:28:57

Welcome.

Kumoka
Starting Member

7 Posts

Posted - 2010-07-28 : 16:48:02

Actually, I have another problem to solve again >.<
Did not thought about it until now.

My staff's name are at another table call staff, is it possible to call it out together with the query provided above so that it will appear beside the staffid(the staffid in attendance is FK and it's PK in staff)? I tried Join a few times and failed miserably @_@
And I read up on union and they say it won't work unless it has same number of rows.

Sachin.Nand

2937 Posts

Posted - 2010-07-29 : 01:16:07

select staffID,staffname
CONVERT(varchar(6),aDate,112),
SUM(attendance),
COUNT(staffId),
ROUND(((SUM(attendance) * 1.0) /COUNT(staffId) * 100),0)
from attendance
inner join staff on staff.staffid=attendance.staffid
group by staffId,staffname
CONVERT(varchar(6), aDate,112)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kumoka
Starting Member

7 Posts

Posted - 2010-07-29 : 06:40:41

Oic. Thank you!
For some reasons my Join seems to always result in error, haha.

My friend had also helped me out and we managed to get this to work.

select s.staffID, s.name,
CONVERT(varchar(6),aDate,112),
SUM(a.attendance),
COUNT(a.staffId),
ROUND(((SUM(attendance)*1.0 / COUNT(a.staffId)) * 100),0)
from staffrecord a, staff s
where a.staffId = s.staffId
group by s.staffId, s.name,
CONVERT(varchar(6), aDate,112)

^_^

Celko
Starting Member

23 Posts

Posted - 2010-07-31 : 21:41:14

Here is another (untested) approach. It will really elpo if you would post DDL, so we don't have to guess.

CREATE TABLE Attendance
(staff_id INTEGER NOT NULL
REFERENCES Staff (staff_id),
work_date DATE NOT NULL,
PRIMARY KEY (staff_id, work_date)
morning_shift SMALLINT DEFAULT 0 NOT NULL,
afternoon_shift SMALLINT DEFAULT 0 NOT NULL,
etc);

CREATE TABLE Staff
staff_id INTEGER NOT NULL PRIMARY KEY,
etc);

SELECT X.staff_id, cal_year, cal_month,
SUM(shift_cnt) OVER PARTITION BY staff_id, cal_year, cal_month)
AS attendance_cnt
FROM (SELECT S.staff_id, -– other staff columns if needed here
DATEPART (YEAR, A.work_date) AS cal_year,
DATEPART (MONTH, A.work_date) AS cal_month,
SIGN (COALESCE (A.morning_shift, 0)
+ (COALESCE (A.afternoon_shift, 0)) AS shift_cnt
FROM Staff AS S
LEFT OUTER JOIN
Attendance AS A
ON S.staff_id = A.staff_id)
AS X;

The inner query splits out the month and year from the attendance date, then gives the staff member a point for either or both shifts on that day. If he did not fill out a attendance form for a particular date, he gets a zero.

The outer query totals the shift counts by year and month.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources