CODEWITHSUNDEEP
sql-serverrdlc
Time Attendance System
Time Attendance System

Reputation: 319

How to group by date and get all in and out time in rdlc report?

Here is my sql query output like this :

DATEEVENT   emp_code    emp_name      entry           DT            time
2018-01-04     102       Ihsan Titi    IN   2018-01-04 15:57:04.000 15:57
2018-01-04     102       Ihsan Titi    IN   2018-01-04 15:57:05.000 15:57
2018-01-04     102       Ihsan Titi    OUT  2018-01-04 16:46:19.000 16:46
2018-01-04     102       Ihsan Titi    IN   2018-01-04 18:15:27.000 18:15
2018-01-04     102       Ihsan Titi    OUT  2018-01-04 18:20:47.000 18:20
2018-01-04     102       Ihsan Titi    IN   2018-01-04 20:02:05.000 20:02

but in my report i group row as reader id and date_event and column group by entry.It doesn't get all in_time and out_time.It get only one in and out time .My report output as given below in image.

enter image description here

Expected Output:

 DATEEVENT  emp_code          IN                       OUT
2018-01-04     102    2018-01-04 15:57:04.000           -
                      2018-01-04 15:57:04.000  2018-01-04 16:46:19.000 16:46
                      2018-01-04 18:15:27.000  2018-01-04 18:15:27.000 18:15
                      2018-01-04 20:02:05.000           -

Anyone could help .. Thanks in advance

Upvotes: 0

Views: 305

Answers (1)

uzi
uzi

Reputation: 4146

Check this query. If you want to show blank values for some fields it's better to do it in your report

declare @t table (
    emp_code int
    , emp_name varchar(100)
    , entry varchar(5)
    , DT datetime
)
insert into @t
values 
    (102, 'Ihsan Titi', 'IN', '20180104 15:57:04.000')
    , (102, 'Ihsan Titi', 'IN', '20180104 15:57:05.000')
    , (102, 'Ihsan Titi', 'OUT', '20180104 16:46:19.000')
    , (102, 'Ihsan Titi', 'IN', '20180104 18:15:27.000')
    , (102, 'Ihsan Titi', 'OUT', '20180104 18:20:47.000')
    , (102, 'Ihsan Titi', 'IN', '20180104 20:02:05.000')

select
    DATEEVENT = min(cast(DT as date)), emp_code
    , [IN] = isnull(max(iif(rn = 1, convert(varchar(19), DT, 120), NULL)), '-')
    , [OUT] = isnull(max(iif(rn = 2, convert(varchar(19), DT, 120), NULL)), '-')
from (
    select
        *, rn = row_number() over (partition by emp_code, grp order by DT)
    from (
        select 
            *, grp = sum(iif(entry = 'IN', 1, 0)) over (partition by emp_code order by DT)
        from 
            @t
    ) t
) t
group by emp_code, grp

Output:

DATEEVENT   emp_code    IN                     OUT
------------------------------------------------------------------
2018-01-04  102         2018-01-04 15:57:04    -
2018-01-04  102         2018-01-04 15:57:05    2018-01-04 16:46:19
2018-01-04  102         2018-01-04 18:15:27    2018-01-04 18:20:47
2018-01-04  102         2018-01-04 20:02:05    -

Actually row_number is not needed

select
    DATEEVENT = min(cast(DT as date)), emp_code
    , [IN] = min(convert(varchar(19), DT, 120))
    , [OUT] = iif(max(convert(varchar(19), DT, 120)) = min(convert(varchar(19), DT, 120)), '-', max(convert(varchar(19), DT, 120)))
from (
    select 
        *, grp = sum(iif(entry = 'IN', 1, 0)) over (partition by emp_code order by DT)
    from 
        @t
) t
group by emp_code, grp

Upvotes: 1

Related Questions