0

It might sound complex - I think it is.

I have an output like this:

| EmployeeID|      DateAndTime        | Type | AbsenceID |      DateEntered        |     LastModifiedDate    | minutes | DN |
|   ZB01    | 2014-09-22 08:01:01.000 |  1   |    0      | 2014-09-22 08:01:21.245 | 2014-09-22 08:01:21.245 |   443   | 30 |
|   ZB01    | 2014-09-22 16:24:01.000 |  2   |    0      | 2014-09-22 16:24:37.522 | 2014-09-22 16:24:37.522 |   443   | 30 |
|   MR89    | 2014-05-19 08:08:01.000 |  1   |    0      | 2014-05-19 08:08:34.680 | 2014-05-19 08:08:34.680 |   397   | 31 |
|   MR89    | 2014-05-19 14:11:01.000 |  5   |    11     | 2014-05-19 14:11:04.510 | 2014-05-19 14:11:04.510 |   397   | 31 |
|   MR89    | 2014-05-19 15:15:01.000 |  1   |    0      | 2014-05-23 13:30:55.837 | 2014-05-23 13:30:55.837 |   397   | 31 |
|   MR89    | 2014-05-19 15:15:02.000 |  2   |    0      | 2014-05-20 08:17:02.740 | 2014-05-23 13:30:55.883 |   397   | 31 |
|   TS44    | 2014-15-05 08:00:01.000 |  1   |    0      | 2014-15-05 08:00:02.822 | 2014-15-05 08:00:02.822 |   450   | 32 |
|   TS44    | 2014-15-05 16:30:01.000 |  2   |    0      | 2014-15-05 16:30:45.675 | 2014-15-05 16:30:45.675 |   450   | 32 |

Type 1 stands for an in-booking
Type 2 stands for an out-booking
Type 4 stands for an business absence in-booking.
Type 5 stands for an business absence out-booking.
Type 38 stands for absences (full-day).

When you make an type 4 or 5 booking you can select which absence is the reason. You see the reason at AbsenceId column. In the above example this stands for "dentist".

I want to get the total hours of absence from when the business absence begin to the next in- or out-booking is made. In the above example there's made an out-booking afterwards.

But what I wanted the output to be:

| EmployeeID|      DateAndTime        | Type | AbsenceID |      DateEntered        |     LastModifiedDate    | minutes | DN |absMinutes|
|   ZB01    | 2014-09-22 08:01:01.000 |  1   |    0      | 2014-09-22 08:01:21.245 | 2014-09-22 08:01:21.245 |   443   | 30 |   0      |
|   ZB01    | 2014-09-22 16:24:01.000 |  2   |    0      | 2014-09-22 16:24:37.522 | 2014-09-22 16:24:37.522 |   443   | 30 |   0      |
|   MR89    | 2014-05-19 08:08:01.000 |  1   |    0      | 2014-05-19 08:08:34.680 | 2014-05-19 08:08:34.680 |   397   | 31 |   0      |
|   MR89    | 2014-05-19 14:11:01.000 |  5   |    11     | 2014-05-19 14:11:04.510 | 2014-05-19 14:11:04.510 |   397   | 31 |   64     |
|   MR89    | 2014-05-19 15:15:01.000 |  2   |    0      | 2014-05-23 13:30:55.837 | 2014-05-23 13:30:55.837 |   397   | 31 |   0      |
|   MR89    | 2014-05-19 15:15:02.000 |  2   |    0      | 2014-05-20 08:17:02.740 | 2014-05-23 13:30:55.883 |   397   | 31 |   0      |
|   TS44    | 2014-15-05 08:00:01.000 |  1   |    0      | 2014-15-05 08:00:02.822 | 2014-15-05 08:00:02.822 |   450   | 32 |   0      |
|   TS44    | 2014-15-05 16:30:01.000 |  2   |    0      | 2014-15-05 16:30:45.675 | 2014-15-05 16:30:45.675 |   450   | 32 |   0      |

You see I have to kinds of minutes ind my example.
"Minutes" is the total hours of presence. The type 4 and 5 is calculated within the minutes.
"AbsMinutes" should be the time the employee have been absent. I thought of (pseudo code):

case when art = 5 and next-row = 1 or 2 then next-row's DateAndTime minus current row's DateAndTime.

My code:

select
    EmployeeID, DateAndTime, Type, AbsenceID, DateEntered, LastModified, Minutes, 
    dense_rank() over (partition by AUSWIDT order by convert(varchar(10),DATUMZEIT,20)) dn
from(--c
    select
        EmployeeID, DateAndTime, Type, AbsenceID, DateEntered, LastModified, Minutes, BookingKontrol,
        ROW_NUMBER() over (partition by AUSWIDT, DATUMZEIT order by LETZT_AEND desc, dagsKontrol) rn
    from(--b
        select
            EmployeeID, DateAndTime, Type, AbsenceID, DateEntered, LastModified, Minutes
            ,case when Type = 38 then 1 when Type = 4 then 1 when Type = 5 then 1 when Type = 1 then 1 when Type = 2 then 1 else 2 end BookingKontrol
        from(--a
            select 
                t.EmployeeId EmployeeID, t.DateAndTime DateAndTime, t.type Type ,t.AbsenceId AbsenceID, t.DateEntered DateEntered, t.LastModified LastModified, h.minutes Minutes
            from
                Transaktion t, HistoricTime h
            where
                h.EmployeeId = t.EmployeeId and
                convert(varchar(10),h.HistoricDate,20) = CONVERT(varchar(10),t.DateandTime,20)
        )a
    )b
    where BookingKontrol = 1
)c
where rn = 1

order by t.EmployeeId, CONVERT(VARCHAR(10),t.DateandTime,20), t.LastModified LastModified

Update I use a system where a newbie programmer can select tables and fields from a list, link them together and make conditions. When executed, the system will automatically write SELECT in front of all the selected choices so it becomes a select-statement. That means I can't start with a WITH-query.

0
;WITH cteSample
AS
( SELECT 'ZB01' AS EmployeeID,'2014-09-22 08:01:01.000' AS DateAndTime, 1 AS [Type],0 AS AbsenceID
    ,'2014-09-22 08:01:21.245' AS DateEntered,'2014-09-22 08:01:21.245' AS LastModifiedDate , 443 AS [MINUTES] ,30 AS DN
UNION ALL SELECT 'ZB01','2014-09-22 16:24:01.000',2,0,'2014-09-22 16:24:37.522','2014-09-22 16:24:37.522',443,30
UNION ALL SELECT 'MR89','2014-05-19 08:08:01.000',1,0,'2014-05-19 08:08:34.680','2014-05-19 08:08:34.680',397,31
UNION ALL SELECT 'MR89','2014-05-19 14:11:01.000',5,11,'2014-05-19 14:11:04.510','2014-05-19 14:11:04.510',397,31
UNION ALL SELECT 'MR89','2014-05-19 15:15:01.000',1,0,'2014-05-23 13:30:55.837','2014-05-23 13:30:55.837',397,31
UNION ALL SELECT 'MR89','2014-05-19 15:15:02.000',2,0,'2014-05-20 08:17:02.740','2014-05-23 13:30:55.883',397,31
UNION ALL SELECT 'TS44','2014-15-05 08:00:01.000',1,0,'2014-15-05 08:00:02.822','2014-15-05 08:00:02.822',450,32
UNION ALL SELECT 'TS44','2014-15-05 16:30:01.000',2,0,'2014-15-05 16:30:45.675','2014-15-05 16:30:45.675',450,32)


SELECT
A.*
,CASE
WHEN AbsenceID <> 0 AND B.[Type] IN(1,2) THEN DATEDIFF(MINUTE,DateEntered,B.DateAndTime) 
ELSE 0 END AS absMinutes
FROM
cteSample AS A 
OUTER APPLY
(
    SELECT TOP(1)
        B.[Type]
        ,B.DateAndTime
    FROM cteSample AS B
    WHERE
        A.EmployeeID = B.EmployeeID
        AND A.DateAndTime < B.DateAndTime
        --AND B.[Type] >= 1 
        --AND B.[Type] <= 2
    ORDER BY 
        B.DateAndTime ASC
)B

and the output:

EmployeeID DateAndTime             Type        AbsenceID   DateEntered             LastModifiedDate        MINUTES     DN          absMinutes
 ZB01       2014-09-22 08:01:01.000 1           0           2014-09-22 08:01:21.245 2014-09-22 08:01:21.245 443         30          0
 ZB01       2014-09-22 16:24:01.000 2           0           2014-09-22 16:24:37.522 2014-09-22 16:24:37.522 443         30          0
 MR89       2014-05-19 08:08:01.000 1           0           2014-05-19 08:08:34.680 2014-05-19 08:08:34.680 397         31          0
 MR89       2014-05-19 14:11:01.000 5           11          2014-05-19 14:11:04.510 2014-05-19 14:11:04.510 397         31          64
 MR89       2014-05-19 15:15:01.000 1           0           2014-05-23 13:30:55.837 2014-05-23 13:30:55.837 397         31          0
 MR89       2014-05-19 15:15:02.000 2           0           2014-05-20 08:17:02.740 2014-05-23 13:30:55.883 397         31          0
 TS44       2014-15-05 08:00:01.000 1           0           2014-15-05 08:00:02.822 2014-15-05 08:00:02.822 450         32          0
 TS44       2014-15-05 16:30:01.000 2           0           2014-15-05 16:30:45.675 2014-15-05 16:30:45.675 450         32          0
  • That looks great, but.. I forgot to mention that I can't use WITH in my system where I create the queries.. I tried to use your example where i replaced the WITH-query with the already existiing select-query I showed, but it's not working – marinadelademad Mar 31 '15 at 7:51
  • WITH is just the source of my data. You can use temp table or variable table – Sabin Bio Apr 1 '15 at 4:45
  • I tried to test your code by changing some of the Types to 4 or 5 and AbsenceID to anything else than 0. I expected to get some absMinutes greater than zero but nothing happened. I'm kind of noob when it comes to SQL and I don't understand some of the code by 100%. How should I understand this line: WHEN AbsenceID <> 0 AND B.[Type] IN(1,2) THEN DATEDIFF(MINUTE,DateEntered,B.DateAndTime) ? it is the "B.TYPE IN(1,2)" I don't get – marinadelademad Apr 7 '15 at 7:23
  • next row should have type 1 or 2 – Sabin Bio Apr 7 '15 at 7:47

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.