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.
.460
in the result and not.243
? – ypercubeᵀᴹ Mar 17 '15 at 14:23fromdate
andtilladte
? Are theyDATE
,DATETIME
or something else? Why the conversion to char? – ypercubeᵀᴹ Mar 17 '15 at 14:25fromdate
andtilldate
have time parts? Or are they alwaysyyyy-mm-dd 00:00:00
? – ypercubeᵀᴹ Mar 17 '15 at 14:40