0

I'm using Oracle SQL.

I'm attempting to write a query that queries a table that has employee timecard approval data. The problem I'm having is on one pay period, an employee has 2 timecard approvals on different dates by different managers (DATASOURCEID). This is what the table looks like for this person:

DATASOURCEID EMPLOYEEID WFCAUDITTYPEID ENTEREDONDTM FROMDTM   TODTM
    2013325  110654     3              4/25/2021    4/11/2021 4/24/2021
    2013618  110654     3              4/26/2021    4/11/2021 4/24/2021

I'm using Oracle SQL.

I want to pull the record with the earliest date only.

My current query looks like this:

SELECT pe.PERSONFULLNAME AS EmployeeNm, 
       p.personnum AS Employee, 
       l.laborlev2nm AS CCWorked, 
       l.laborlev2dsc AS CCWorkedDesc,  
       pc.PERSONCSTMDATATXT AS Agency,  
       trunc(p.STARTDTM) AS DateIn, 
       to_char(p.STARTDTM,'hh24:mi') AS StartTimeOfDay, 
       trunc(p.ENDDTM) AS DateOut, 
       to_char(p.ENDDTM,'hh24:mi') AS EndTimeOfDay,  
       p.eventdate AS DateWorked, 
       round(sum(t.durationsecsqty/3600.0),2) AS Hours, 
       CC.USERNAME AS SupervisorId,  
       ps.FULLNM AS SupervisorNm, 
       n.abbreviationchar AS PayCode 
FROM tkcsowner.vp_timeshtpunchv42 p 
INNER JOIN tkcsowner.wfctotal t ON t.timesheetitemid = p.timesheetitemid INNER JOIN WFCAUDIT WA on WA.EMPLOYEEID = p.EMPLOYEEID 
      AND t.APPLYDTM between WA.FROMDTM and WA.TODTM 
INNER JOIN DATASOURCE DS ON (WA.DATASOURCEID = DS.DATASOURCEID) 
INNER JOIN CLIENTCONTEXT CC ON (CC.CLIENTCONTEXTID = DS.CLIENTCONTEXTID) 
      AND (CC.USERNAME <> 'Import') 
INNER JOIN tkcsowner.vp_allpersonv42 pe ON pe.EMPLOYEEID = p.EMPLOYEEID 
      AND pe.HOMELABORLEVELNM2 in ('9913', '9914')
LEFT OUTER JOIN tkcsowner.paycode n ON n.paycodeid = t.paycodeid
LEFT OUTER JOIN tkcsowner.laboracct l ON l.laboracctid = t.laboracctid 
LEFT OUTER JOIN tkcsowner.PERSON ps ON ps.PERSONNUM = CC.USERNAME 
LEFT OUTER JOIN tkcsowner.personcstmdata pc ON pc.PERSONID = p.PERSONID 
     AND pc.customdatadefid = 6 
WHERE 1 = 1 
AND p.employeeid = 110654 
AND n.NAME in ('ONC-Oncall SBP', 'ONC-Oncall CBP') 
AND (p.startdtm <> p.enddtm) 
AND p.eventdate between '01-FEB-2021' and '31-MAY-2021'
group by pe.PERSONFULLNAME, p.PERSONNUM, l.LABORLEV2NM, l.LABORLEV2DSC, pc.PERSONCSTMDATATXT, p.STARTDTM, p.ENDDTM, p.ENDDTM, p.EVENTDATE, CC.USERNAME, ps.FULLNM, n.ABBREVIATIONCHAR

But it's still pulling both records:

EMPLOYEENM  EMPLOYEE  DATEIN     DATEOUT   SUPERVISOR  PUNCH          
Bill Dance  110654    4/12/2021  4/13/2021 2013325     ONC
Bill Dance  110654    4/12/2021  4/13/2021 2013618     ONC

I left off a few of the columns from the SELECT to make output more readable.

It's pulling the 2 records for same day because they had 2 SUPERVISOR approvals for same pay period.

Any help greatly appreciated! Thanks, Rufus

5
  • Which database system are you using, Microsoft SQL Server or Oracle SQL?
    – J.D.
    Commented Nov 15, 2023 at 17:14
  • J.D. thanks for responding. I'm using Oracle SQL.
    – RBERTRAND
    Commented Nov 15, 2023 at 17:29
  • @RBERTRAND Can you please post the schema for the wfcaudit table?
    – Caleb Carl
    Commented Nov 15, 2023 at 17:45
  • Caleb thanks for responding. I'm not sure how to upload a screen shot of WFCAUDIT table in Schema Viewer.
    – RBERTRAND
    Commented Nov 15, 2023 at 17:48
  • ColumnName ID PK IndexPos NULL DataType DATASOURCEID 1 1, 4 Y Number (12) EMPLOYEEID 2 1, 1 Y Number (12)
    – RBERTRAND
    Commented Nov 15, 2023 at 17:54

1 Answer 1

1

There are a number of issues with your SQL statement:

  1. You're applying your aggregate function to the 'employeeid' column, but it's the 'enteredondtm' column that you're trying to find the lowest value one.
  2. You're grouping by the datasourceid and enteredondtm columns - surely you want to find the earliest value in the enteredondtm column, and the corresponding datasourceid value for that row. If so, you do not want to have these columns in the group by section! (Think of it like: if you had a bucket of balls with numbers on them, and I asked you to find the ball with the lowest number for each colour, you wouldn't separate the balls into groups of colour and number, would you? You would group by colour and then find the lowest number in each group of balls.)
  3. I'm assuming that your date columns are of DATE or TIMESTAMP datatypes, and therefore when you compare a date-as-a-string to those columns, you should always explicitly cast that string into a DATE (or TIMESTAMP). If you rely on implicit conversion, as it looks like you're doing with your 12-APRIL-21 (really? 2 digit years? *shudder* - we didn't go through Y2K for nothing, you know!), you're opening yourself up to bugs - what if someone tries running your query on a client that has a different nls_date_format setting? It will most likely fail, whereas a correctly converted string-to-date will ensure the query will run on any client.

So, your query could be written like:

with wfcaudit as (select 2395711 datasourceid, 110654 employeeid, 3 wfcaudittypeid, to_date('25/04/2021', 'dd/mm/yyyy') enteredondtm, null  auditdtm, to_date('11/04/2021', 'dd/mm/yyyy') fromdtm, to_date('24/04/2021', 'dd/mm/yyyy') todtm from dual union all
                  select 2391965 datasourceid, 110654 employeeid, 3 wfcaudittypeid, to_date('26/04/2021', 'dd/mm/yyyy') enteredondtm, null  auditdtm, to_date('11/04/2021', 'dd/mm/yyyy') fromdtm, to_date('24/04/2021', 'dd/mm/yyyy') todtm from dual)
select wa.employeeid,
       min(wa.datasourceid) keep (dense_rank first order by enteredondtm) datasourceid,
       min(wa.enteredondtm) enteredondtm
from   wfcaudit wa
where  wa.employeeid = 110654
and    to_date('12/04/2021', 'dd/mm/yyyy') between wa.fromdtm and wa.todtm
group  by wa.employeeid;

EMPLOYEEID DATASOURCEID ENTEREDONDTM
---------- ------------ ------------
    110654      2395711 25/04/2021

Note that this query works, but if you wanted to display more than two or three columns, you'd have to apply the min() keep (dense_rank ...) function to every column, which gets a bit cumbersome.

Instead, another method is to assign a row number to each row and then filter out just the initial row, e.g.:

with wfcaudit as (select 2395711 datasourceid, 110654 employeeid, 3 wfcaudittypeid, to_date('25/04/2021', 'dd/mm/yyyy') enteredondtm, null  auditdtm, to_date('11/04/2021', 'dd/mm/yyyy') fromdtm, to_date('24/04/2021', 'dd/mm/yyyy') todtm from dual union all
                  select 2391965 datasourceid, 110654 employeeid, 3 wfcaudittypeid, to_date('26/04/2021', 'dd/mm/yyyy') enteredondtm, null  auditdtm, to_date('11/04/2021', 'dd/mm/yyyy') fromdtm, to_date('24/04/2021', 'dd/mm/yyyy') todtm from dual),
          res AS (SELECT employeeid,
                         datasourceid,
                         enteredondtm,
                         row_number() OVER (ORDER BY enteredondtm) rn
                  FROM   wfcaudit
                  WHERE  employeeid = 110654
                  and    to_date('12/04/2021', 'dd/mm/yyyy') between fromdtm and todtm)
SELECT employeeid,
       datasourceid,
       enteredondtm
FROM   res
WHERE  rn = 1;

EMPLOYEEID DATASOURCEID ENTEREDONDTM
---------- ------------ ------------
    110654      2395711 25/04/2021

It is arguably easier to understand the intent behind this query, although the first query is more compact.


As you have updated your query, but the input data doesn't appear to match the expected output, I'll give you an idea of how you can take your query and apply the logic of either of the queries in my above answer.

You can see that in both of my queries, instead of creating a wfcaudit table and populating it with your data, I used a subquery to mimic a table with the data in it. If your data resided in a single table, you wouldn't need that subquery because you would just query the table directly.

It now seems like you have a big, fairly complicated query that generates the data you want to pick the earliest row from, so all you need to do is take that query and put it into a subquery, i.e.:

with base_data as (<your query here>)...

then you can use that in whichever of the two queries in my answer best suits. I'd go with the second solution myself, so your query would end up looking something like:

with base_data as (<your query here>),
  ordered_data as (select <relevant columns>,
                          row_number() over (partition by <column(s) identifying the group, e.g. unique column(s)> -- you can remove the partition by clause if the whole resultset is a single group
                                             order by <whatever column(s) determine the order of the rows in each group>) rn
                   from   base_data)
select <columns>
from   ordered_data
where  rn = 1;

Hopefully that is enough for you to generate your own query.

12
  • Thanks for taking the time to explain how it all works together! I will definitely use on of the examples you've shared.
    – RBERTRAND
    Commented Nov 15, 2023 at 18:52
  • Boneist, I should have put the entire query in my original question. I only put the code I was working on to get it to work. I edited my original question and have the entire code in it now. Any help would be appreciated!
    – RBERTRAND
    Commented Nov 16, 2023 at 14:10
  • I've edited my answer; hopefully you can use that to write your own query that does what you want.
    – Boneist
    Commented Nov 16, 2023 at 14:18
  • Boneist, thanks so much for responding! I took your recommendations and added them to my query, worked out the errors I was getting, but I have 1 last error that I'm not sure what is wrong, this is the clip that is getting the error: ordered_data as (select <field names>row_number() over (partition by base_data.ENTEREDONDTM order by base_data.PERSONNUM) rn from base_data)
    – RBERTRAND
    Commented Nov 16, 2023 at 15:37
  • 1
    @RBERTRAND I pulled my answer because it's more important that you understand why this works the way that it does, and giving you the exact query will not likely help others as much as this answer will. The concept they and I are offering as the solution is the same. The ROW_NUMBER() function identifies the record number from 1-n, seperated by essentially which columns make that row unique, then ordered by the date column you are looking at to get the earliest record. That way rn 1 would be the earliest, rn 2 would be the record that occurred after, and so on.
    – Caleb Carl
    Commented Nov 16, 2023 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.