Sql-server – How to get the previous or next row to compare with the current row for two columns

sql-server-2008

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.

Best Answer

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?

Related Question