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
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
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
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
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
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
Oracle DatabaseSQL
Last Comment
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?
It doesn't seem to be factoring in multiple ATT_CODE records with the same CALENDARDAYID values
Any idea's?
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.
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
>>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:
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:
If you want a copy/paste solution, we need raw data to set up a test case and test our code.
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;
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;
If you want a copy/paste solution, we need raw data to set up a test case and test our code.
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,calend
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
111123,11,6
111456,12,6
111789,12,6
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,calend
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
111123,11,6
111456,12,6
111789,12,6
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I see now, thank you very much!!
Try something like this:
Open in new window