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.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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?
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.
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
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:
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
/
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 trialIt'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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
Try something like this:
Open in new window