accounting for multiple codes per day in an oracle attendance query for counting absences

The following query counts how many absences an employee has over the specifies period of time.
My results are incorrect due to multiple codes existing on the same day.
There is a column that can be leveraged called ATT.CALENDARDAYID
How can I change the query below so if only counts one record if there are multiple codes of different values using the same ATT.CALENDARDAYID value?
For example, there may be a record for calendardayid 5000 with a code of ABS and another record with the same calendardayid 5000 with a code of NCNS.
The query below counts both instances, I only need one instance per calendardayid counted
Thanks!!

SELECT
CAST(USERS.EMPLOYEE_NUMBER AS varchar(10)) as EMPLOYEE_NUMBER,
to_char(ATT.BUILDINGID) as BUILDINGID,
to_char(ATT.YEARID) as YEARID,

sum(count(case when ATT_Code.Att_Code in ('ABS','HOSP','NOTE','NCNS') then 1 else 0 end)) over(partition by USERS.EMPLOYEE_NUMBER,ATT.BUILDINGID) as ABSTOTAL

FROM "SCHEMA"."ATT" "ATT"
INNER JOIN ATT_CODE on ATT.ATT_CODEID = ATT_code.id
INNER JOIN USERS on USERS.id = ATT.EMPLOYEEID
INNER JOIN SCHEMA.EMPLOYMENT EMPLOYMENT ON EMPLOYMENT.EMPLOYEEID = USERS.ID

WHERE
(ATT.yearid = '15')
AND (ATT.BUILDINGID NOT IN ('96','97','98','99'))
GROUP BY
USERS.EMPLOYEE_NUMBER,
ATT.BUILDINGID,
ATT.YEARID
BasssqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You might need to tweak the partition by clause of row_number to use different columns.  Without sample data and expected results, I don't know what the correct column to partition by is.

Try something like this:
SELECT
CAST(EMPLOYEE_NUMBER AS varchar(10)) as EMPLOYEE_NUMBER,
to_char(BUILDINGID) as BUILDINGID,
to_char(YEARID) as YEARID,
sum(count(case when rn=1 and Att_Code in ('ABS','HOSP','NOTE','NCNS') then 1 else 0 end)) over(partition by EMPLOYEE_NUMBER,BUILDINGID) as ABSTOTAL
from (
	select users.employee_number, att.buildingid, att.yearid, att.att_code,
		row_number() over(partition by users.id order by users.id) rn
	FROM "SCHEMA"."ATT" "ATT"
	INNER JOIN ATT_CODE on ATT.ATT_CODEID = ATT_code.id
	INNER JOIN USERS on USERS.id = ATT.EMPLOYEEID
	INNER JOIN SCHEMA.EMPLOYMENT EMPLOYMENT ON EMPLOYMENT.EMPLOYEEID = USERS.ID
	WHERE
	(ATT.yearid = '15')
	AND (ATT.BUILDINGID NOT IN ('96','97','98','99'))
)
GROUP BY
EMPLOYEE_NUMBER,
BUILDINGID,
YEARID 

Open in new window

BasssqueAuthor Commented:
The query provided returns the same results as before.  
It doesn't seem to be factoring in multiple ATT_CODE records with the same CALENDARDAYID values

Any idea's?
BasssqueAuthor Commented:
If I run the following query, it groups by CALENDARID too to give a better idea here.
It should return a 1 for every ABSTOTAL in this example... but if there are 2 ATT_CODE records with the same CALENDARDAYID then it returns 2 when I only want 1
I had a typo or two and removed some unnecessary fields for the purpose of this example.
I can post sample data if needed, it just takes a while because I can't post the actual fields or data.

SELECT
CAST(EMPLOYEE_NUMBER AS varchar(10)) as EMPLOYEE_NUMBER,
to_char(BUILDINGID) as BUILDINGID,
CALENDARDAYID,
sum(count(case when rn=1 and Att_Code in ('ABS','HOSP','NOTE','NCNS') then 1 else 0 end)) over(partition by EMPLOYEE_NUMBER,BUILDINGID,CALENDARDAYID) as ABSTOTAL
from (
	select users.employee_number, att.buildingid, att_code.att_code,
		row_number() over(partition by att_code.CALENDARDAYID order by att_code.CALENDARDAYID) rn
	FROM "SCHEMA"."ATT" "ATT"
	INNER JOIN ATT_CODE on ATT.ATT_CODEID = ATT_code.id
	INNER JOIN USERS on USERS.id = ATT.EMPLOYEEID
	WHERE
	(ATT.yearid = '15')
	AND (ATT.BUILDINGID NOT IN ('96','97','98','99'))
)
GROUP BY
EMPLOYEE_NUMBER,
BUILDINGID,
CALENDARDAYID

Open in new window

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

slightwv (䄆 Netminder) Commented:
>>I can post sample data if needed, it just takes a while because I can't post the actual fields or data.

It shouldn't take that much time.  Just set up the minimum that represents what you need.  We don't need all the individual tables for the joins.  We just need a single table that represents the result set from all the joins.

This took less than a minute:
create table tab1(EMPLOYEE_NUMBER number, BUILDINGID number, YEARID number, Att_Code varchar2(10));

insert into tab1 values(1,1,1,'ABS');
insert into tab1 values(1,1,1,'ABS');
insert into tab1 values(1,1,1,'ABS');
insert into tab1 values(1,1,1,'ABS');
insert into tab1 values(2,1,1,'ABS');
commit;

Open in new window


Then post the expected results based on the simple test data.

Do you understand what the row_number window function is doing and what I was attempting to do with it?

For all the repeating rows in the group, number them.  Then in the outer query, only grab the first one.

Here is a simple test showing what it does:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','a');
insert into tab1 values('a','a');
insert into tab1 values('a','b');
insert into tab1 values('a','c');
insert into tab1 values('a','c');
insert into tab1 values('a','c');
insert into tab1 values('a','c');
insert into tab1 values('b','a');
insert into tab1 values('b','b');
commit;

select col1, col2, row_number() over(order by col2) rn from tab1;
select col1, col2, row_number() over(partition by col1 order by col1, col2) rn from tab1;

Open in new window


If you want a copy/paste solution, we need raw data to set up a test case and test our code.
BasssqueAuthor Commented:
Let's try this, Let me know if you need more.

Users table
Employee_number,id
111123,123
111456,456
111789,789

ATT table
Employeeid,buildingid
123,11
456,12
789,12

ATT_CODE table
Employeeid,Att_code,calendardayid
123,abs,5000
123,ncns,5000
123,abs,5001
123,note,5002
123,ncns,5003
123,abs,5004
123,ncns,5005
456,abs,5000
456,abs,5001
456,note,5002
456,note,5003
456,ncns,5004
456,ncns,5005
789,abs,5000
789,note,5000
789,abs,5001
789,note,5001
789,abs,5002
789,note,5003
789,abs,5004
789,abs,5005

Expected output
Employee_number,buildingid,abstotal
111123,11,6
111456,12,6
111789,12,6
slightwv (䄆 Netminder) Commented:
I can work with that but it really isn't that close to your original post.

I had to remove some columns and joins from your original query but based on what you provided me, this returns the expected results.

Note: I changed the table names because of the drop statements in my setup:
/*
drop table myusers purge;
create table myusers(Employee_number number,id number);
insert into myusers values(111123,123);
insert into myusers values(111456,456);
insert into myusers values(111789,789);

drop table myATT purge;
create table myatt (Employeeid number,buildingid number);
insert into myatt values(123,11);
insert into myatt values(456,12);
insert into myatt values(789,12);

drop table myATT_CODE purge;
create table myATT_CODE (Employeeid number,Att_code varchar2(10),calendardayid number);
insert into myatt_code values(123,'ABS',5000);
insert into myatt_code values(123,'NCNS',5000);
insert into myatt_code values(123,'ABS',5001);
insert into myatt_code values(123,'NOTE',5002);
insert into myatt_code values(123,'NCNS',5003);
insert into myatt_code values(123,'ABS',5004);
insert into myatt_code values(123,'NCNS',5005);
insert into myatt_code values(456,'ABS',5000);
insert into myatt_code values(456,'ABS',5001);
insert into myatt_code values(456,'NOTE',5002);
insert into myatt_code values(456,'NOTE',5003);
insert into myatt_code values(456,'NCNS',5004);
insert into myatt_code values(456,'NCNS',5005);
insert into myatt_code values(789,'ABS',5000);
insert into myatt_code values(789,'NOTE',5000);
insert into myatt_code values(789,'ABS',5001);
insert into myatt_code values(789,'NOTE',5001);
insert into myatt_code values(789,'ABS',5002);
insert into myatt_code values(789,'NOTE',5003);
insert into myatt_code values(789,'ABS',5004);
insert into myatt_code values(789,'ABS',5005);
commit;
*/

--Expected output
--Employee_number,buildingid,abstotal
--111123,11,6
--111456,12,6
--111789,12,6

SELECT
EMPLOYEE_NUMBER,
BUILDINGID,
sum(case when rn=1 and Att_Code in ('ABS','HOSP','NOTE','NCNS') then 1 else 0 end)  as ABSTOTAL
from (
	select myusers.employee_number, myatt.buildingid, myatt_code.att_code,
		row_number() over(partition by myusers.employee_number, myatt.buildingid, myatt_code.calendardayid  order by myusers.employee_number, myatt.buildingid) rn
	FROM myusers
	join myatt on myusers.id=myatt.employeeid
	join myatt_code on myusers.id = myatt_code.employeeid
	WHERE
	(myATT.BUILDINGID NOT IN ('96','97','98','99'))
)
GROUP BY
EMPLOYEE_NUMBER,
BUILDINGID
/

                                          

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BasssqueAuthor Commented:
I see now, thank you very much!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.