Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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
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
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial