0

I need to show a list of employees and their absence days with hours.

I have the tables Employee
LogAbsence
LogTimeAttendance

From Employee I need:      EmployeeID(nvarchar(16),null), Name(nvarchar(40),null)
From LogAbsence:             FromDate(datetime,null), TillDate(datetime,null),
                                            AbsenceId(smallInt, null), KeyNo(int, null)
From LogTimeAttendance: EmployeeID(nvarchar(16),null), DateAndTime(datetime, null),
                                            lastChange(datetime,null), KeyNo(int,null)

I have the following output as example (with Dense_Rank and Rank):

| EmployeeID | DateAndTime              | lastChange               | AbsenseId |  KeyNo      | fromDate   | til lDate  | Dense | justRank |
|   R0994    | 2014-04-02 11:15:00.000  | 2014-04-04 10:30:41.433  |     5     | 1396600241  | 2014-04-22 | 2014-04-22 |   1   |     1    |
|   R0994    | 2014-04-22 00:00:10.000  | 2014-04-04 10:30:41.433  |     5     | 1396600241  | 2014-04-22 | 2014-04-22 |   2   |     2    |
|--
|   R0994    | 2014-04-25 00:00:10.000  | 2014-04-28 08:14:04.013  |     14    | 1398665644  | 2014-04-25 | 2014-04-25 |   3   |     3    |
|   R0994    | 2014-04-28 08:14:00.000  | 2014-04-28 08:14:04.013  |     7     | 1398665644  | 2014-04-25 | 2014-04-25 |   4   |     4    |
|--
|   R0994    | 2014-04-30 11:09:00.000  | 2014-04-30 12:31:15.243  |     7     | 1398853875  | 2014-05-26 | 2014-05-28 |   5   |     5    |
|   R0994    | 2014-04-30 11:09:00.000  | 2014-04-30 12:31:15.460  |     7     | 1398853875  | 2014-05-26 | 2014-05-28 |   5   |     6    |
|   R0994    | 2014-04-30 11:34:00.000  | 2014-04-30 12:31:15.667  |     7     | 1398853875  | 2014-05-26 | 2014-05-28 |   6   |     7    |
|   R0994    | 2014-05-26 00:00:10.000  | 2014-04-30 12:31:15.667  |     38    | 1398853875  | 2014-05-26 | 2014-05-28 |   7   |     8    |
|   R0994    | 2014-05-27 00:00:10.000  | 2014-04-30 12:31:15.667  |     38    | 1398853875  | 2014-05-26 | 2014-05-28 |   8   |     9    |
|   R0994    | 2014-05-28 00:00:10.000  | 2014-04-30 12:31:15.667  |     38    | 1398853875  | 2014-05-26 | 2014-05-28 |   9   |     10   |
|   R0994    | 2014-07-14 00:00:10.000  | 2014-04-30 12:31:15.460  |     38    | 1398853875  | 2014-05-26 | 2014-05-28 |   10  |     11   |
|   R0994    | 2014-07-14 00:00:10.000  | 2014-04-30 12:31:15.243  |     38    | 1398853875  | 2014-05-26 | 2014-05-28 |   10  |     12   |

As you can see on the example, EmployeeID R0994 have 3 different periods. The data for each row are different for Date and lastChange.

I want the output to look like this:

| EmployeeID | AbsenseId |  KeyNo      | fromDate   | tillDate   |
|   R0994    |    5      | 1396600241  |2014-04-22  | 2014-04-22 |
|   R0994    |    7      | 1398665644  |2014-04-25  | 2014-04-25 |
|   R0994    |    38     | 1398853875  |2014-05-26  | 2014-05-28 |

I need to get the Absence where Date or LastChange have the latest date.

How do I get the previous or next row to compare with the current row?

I make the sql command ind Microsoft SQL Server Management Studio. But I need to copy the code on the GUI's sql-part.
The sql part of the GUI is made special for those who know a little about SQL, where they can choose tables and columns so get an output.
So when you print out the result from the GUI, the GUI with automatically set the word 'SELECT' as the first think of the output.
Therefore I can't have the word 'WHILE' as the first think in the query.

My code:

select
    employeeID, 
    date, 
    lastChange,
    absenceId, 
    KeyNo, 
    fromDate, 
    tillDate
    ,DENSE_RANK() over (order by fromDate, tillDate) dense
    ,ROW_NUMBER() over (order by employeeId,fromDate, tillDate) justRank
from
(
    select 
        Employee.employeeID employeeID, 
        LogTimeAttendanceDateAndTime date, 
        LogTimeAttendance.LETZT_AEND lastChange, 
        LogAbsence.IDFEZE absenceId, 
        LogTimeAttendance.EINTRKEY KeyNo, 
        convert(varchar(10),LogAbsence.fromDate,20) fromDate, 
        convert(varchar(10),LogAbsence.tillDate,20) tillDate
    from 
        LogTimeAttendance, LogAbsence, Employee
    where 
        LogTimeAttendance.KeyNo = LogAbsence.KeyNo
        AND Employee.employeeID = LogTimeAttendance.employeeID
        --and Employee.employeeID = 'R0994'
)a
Order by EmployeeID, justRank

Please ask if you don't understand me.

  • Why do you want .460 in the result and not .243? – ypercubeᵀᴹ Mar 17 '15 at 14:23
  • And what are the types of fromdate and tilladte? Are they DATE, DATETIME or something else? Why the conversion to char? – ypercubeᵀᴹ Mar 17 '15 at 14:25
  • It's because it is the latest date and time showing registering the absent – marinadelademad Mar 17 '15 at 14:26
  • And the fromDate and tillDate are both datetimes. I edit'ed my question. I needed the fromDate and tillDate to be showned as 'yyy-mm-dd' – marinadelademad Mar 17 '15 at 14:34
  • Do fromdate and tilldate have time parts? Or are they always yyyy-mm-dd 00:00:00 ? – ypercubeᵀᴹ Mar 17 '15 at 14:40
2

If I understand correctly, you need:

select
    employeeID, 
    DateAndTime, 
    lastChange,
    absenceId, 
    KeyNo, 
    fromDate = convert(varchar(10), fd, 20), 
    tillDate = convert(varchar(10), td, 20)
from
(
    select 
        em.employeeID, 
        at.DateAndTime, 
        lastChange = at.LETZT_AEND, 
        absenceId = ab.IDFEZE, 
        KeyNo = at.EINTRKEY, 
        fd = convert(date, ab.fromDate), 
        td = convert(date, ab.tillDate),
        rn = row_number() over 
          (partition by em.employeeId, 
                        convert(date, ab.fromDate), 
                        convert(date, ab.tillDate)
           order by at.DateAndTime desc, 
                    at.LETZT_AEND desc)
    from 
        LogTimeAttendance as at
      join LogAbsence as ab
        on at.KeyNo = ab.KeyNo
      join Employee as em
        on em.employeeID = at.employeeID
    -- where 
    --     em.employeeID = 'R0994'
) as a
where 
    rn = 1
order by 
    employeeID, fd, td ;

Based on the sample you provided, the partition by could possibly be based on the (simpler) partition by em.employeeId, at.EINTRKEY.

If fromDate and tillDate have no time parts (always YYYY-MM-DD 00:00:00, then the convert to (date ...) are not needed. But then, why isn't the type of these columns DATE?

  • I did't create the tables so actually i don't know. It is a business partner that made that for us, I just need to retrieve data from the tables :) – marinadelademad Mar 17 '15 at 14:59
  • Then I suggest you keep the conversions to date, just to be sure. – ypercubeᵀᴹ Mar 17 '15 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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