asked on

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

slightwv (䄆 Netminder)

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

Basssque

ASKER

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?

Basssque

ASKER

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

slightwv (䄆 Netminder)

>>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.

Basssque

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Basssque

ASKER

I see now, thank you very much!!